Алексей Гладкий - Excel. Трюки и эффекты

Как известно, для перехода в режим изменения формата ячейки необходимо или выполнить команду контекстного меню Формат ячеек, или на вкладке Главная в группе Ячейки нажать кнопку Формат и выбрать пункт Формат ячеек, или нажать сочетание клавиш Ctrl+1. Однако для изменения числового формата ячейки можно также воспользоваться специально созданной пользовательской панелью инструментов. Рассмотрим этот процесс подробнее.

Для реализации примера нам потребуется написать в редакторе VBA два кода: в модуле рабочего листа и в стандартном модуле. Код, помещаемый в модуль рабочего листа, выглядит следующим образом (листинг 2.40).

Sub Worksheet_Change(ByVal Target As Excel.Range)

Call UpdateToolbar

End Sub

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Call UpdateToolbar

End Sub

В стандартном модуле редактора VBA необходимо написать код, который приведен в листинге 2.41.

Sub FastChangeNumberFormat()

Dim bar As CommandBar

Dim button As CommandBarButton

' Удаление существующей панели инструментов (если она есть)

On Error Resume Next

CommandBars(«Числовой формат»).Delete

On Error GoTo 0

' Формирование новой панели

Set bar = CommandBars.Add

With bar

.Name = «Числовой формат»

.Visible = True

End With

' Создание кнопки

Set button = CommandBars(«Числовой формат»).Controls.Add _

(Type:=msoControlButton)

With button

.Caption = ""

.OnAction = «ChangeNumFormat»

.TooltipText = «Щелкните для изменения числового формата»

.Style = msoButtonCaption

End With

' Обновление созданной панели инструментов

Call UpdateToolbar

End Sub

Sub UpdateToolbar()

' Обновление панели инструментов (если она создана)

On Error Resume Next

' Изменение заголовка кнопки (на название формата выделенной ячейки)

CommandBars(«Числовой формат»).Controls(1).Caption = _

ActiveCell.NumberFormat

End Sub

Sub ChangeNumFormat()

' Отображение диалогового окна изменения формата ячейки

Application.Dialogs(xlDialogFormatNumber).Show

Call UpdateToolbar

End Sub

Теперь нужно запустить на выполнение макрос FastChangeNumberFormat (после написания кода он будет доступен в окне выбора макросов) – в результате на вкладке Надстройки появится одна кнопка. Название данной кнопки зависит от формата активной ячейки. При подведении к кнопке указателя мыши отобразится всплывающая подсказка Числовой формат: Щелкните для изменения числового формата. При нажатии данной кнопки откроется вкладка Число окна Формат ячеек. Формат активной ячейки изменяется в данном режиме по обычным правилам.

Следует отметить, что перейти в режим редактирования числового формата ячейки можно, запустив на выполнение макрос ChangeNumFormat – после написания приведенного выше кода он также будет доступен в окне выбора макросов.

В процессе работы с электронными таблицами часто приходится переносить значения из массива в диапазон или из диапазона в массив. При этом скорость получения данных из диапазона обычно выше скорости записи. С помощью трюка, который мы сейчас рассмотрим, можно тестировать скорость записи данных массива в диапазон и переноса этих данных обратно в массив.

В стандартном модуле редактора VBA нужно написать код, содержимое которого представлено в листинге 2.42.

Sub TableSpeedTest()

Dim alngData() As Long ' Массив с числами

Dim lngCount As Long ' Количество элементов в массиве

Dim dtStart As Date ' Хранит время (и даже дату)

начала _ тестирования

Dim strArrayToTable As String ' Время записи в таблицу

Dim strTableToArray As String ' Время чтения из таблицы

Dim strMessage As String

Dim i As Long

' Подготовка диапазона ячеек

Range(«A:A»).ClearContents

' Ввод размера массива, формирование массива заданного размера

lngCount = InputBox(«Введите количество элементов»)

ReDim alngData(1 To lngCount)

' Заполнение массива данными

For i = 1 To lngCount

alngData(i) = i

Next i

' Перенос массива в таблицу

Application.ScreenUpdating = False

dtStart = Timer

For i = 1 To lngCount

Cells(i, 1) = i

Next i

strArrayToTable = Format(Timer – dtStart, «00:00»)

' Чтение данных из таблицы обратно в массив

dtStart = Timer

For i = 1 To lngCount

alngData(i) = Cells(i, 1)

Next i

strTableToArray = Format(Timer – dtStart, «00:00»)

Application.ScreenUpdating = True

' Вывод на экран результатов тестирования

strMessage = "Запись: " & strArrayToTable & vbCrLf & _

"Чтение: " & strTableToArray

MsgBox strMessage, , lngCount & « элементов»

End Sub

В результате написания данного кода в окне выбора макросов появится макрос TableSpeedTest. После его запуска откроется окно, в котором в поле Введите количество элементов следует с клавиатуры ввести количество элементов и нажать кнопку ОК. По окончании работы макроса на экране отобразится окно, в котором будет показано время (в секундах), необходимое для записи данных массива в диапазон и для считывания их обратно в массив. Например, у одного из авторов этой книги на запись массива из 30 000 элементов было затрачено 17 секунд, а на считывание данных из диапазона обратно в массив – только 1 секунда; массив из 65 000 элементов записывался 37 секунд, а перенос этих данных обратно в массив занял лишь 2 секунды.