Инструментарий VBA⁠⁠

В данной статье я попробую объяснить, какими удобными фичами обладает редактор VBA для лёгкого и непринуждённого написания кода теми, кто никогда никакое программирование не изучал. Это будет не сложнее рисования совы.

Напомню, что в любой программе из пакета Microsoft Office редактор VBA можно открыть хоткеем Alt+F11.

Выпадающие списки

В каждой из программ Word, Excel, Access и далее есть свои огромные наборы объектов, с которыми приходится иметь дело не зависимо от наших познаний в обычном VB. С опытом их названия запоминаются, но не всегда очевидно их существование или название. Постоянно гуглить быстро надоедает. А можно воспользоваться встроенной в VBA возможностью. Нет, не справкой. Списком свойств и объектов.

Открывается он автоматически, после ввода названия объекта и добавления точки после него.

Но его можно открыть в любое время клавишами Ctrl+J. Так зачем он нужен? В нём перечислено всё, что доступно в работе с объектом, стоящим перед точкой. Среда VBA контролирует синтаксис и всегда выводит только то, что реально можно использовать в данном участке кода. Только в особых избранных случаях можно использовать и то, чего в списке нет. Но эти случаи настолько редкие и экзотические... Так мы всегда можем найти нужную нам функцию или свойство, о которых мы что-то отдалённо помнили или догадывались об их существовании, но вот убей - не вспомнишь, как оно точно звалось. Я постоянно действую именно так - набираю первое слово до точки или даже только первые буквы, а остальное нахожу в списке, ставлю точку, и начинаю листать список. Много случаев было, когда я нашёл интересные особенности объекта именно этим пролистыванием, не читая документации, не копаясь в справке и не обращаясь к гуглам и форумам. Так же полный список самих объектов и их возможностей всегда можно открыть клавишей F2

Здесь можно по части названия самого объекта, функции, свойства, константы найти любой элемент языка. Так мной было обнаружено множество возможностей VBA просто на заданную тему. Очень напоминает википедию, которую открываешь вопросом, какого цвета киви, а через два часа отлипаешь от статьи о пунической войне.

Всплывающая подсказка о синтаксисе

Набираем название объекта или функции, вставляем пробел или точку и видим всплывающую подсказку:

Она кратко описывает синтаксис написания кода для этого объекта. Жирным отмечается параметр, который мы должны описать прямо сейчас, согласно его порядковому положению. В данном случае на картинке это Prompt - текст сообщения. В квадратных скобках перечислены параметры, не обязательные для заполнения. Для некоторых из них даже указаны значения по-умолчанию. Например, стандартная кнопка у любого меседжбокса, даже, если не указать, какие кнопки должны быть - ОК. Такая константа там и указана - vbOKOnly. Что, если мы хотим выбрать другие кнопки? Если мы после текста сообщения поставим запятую, то выпадет список доступных констант для настройки кнопок. Мы их можем перечислить через знак плюса. Например сочетание констант

MsgBox "текст сообщения", vbAbortRetryIgnore + vbDefaultButton2 + vbExclamation, "заголовок"

даст нам такой вид (Title (заголовок сообщения) мы можем пропустить. Тогда будет подставлено название программы):

Имеем: три кнопки - прервать, повтор, пропустить. Кнопка "повтор" выбрана заранее и если пользователь сразу нажмёт клавишу Пробел или Enter, то сработает эта кнопка. Ну и константа vbExclamation вывела нам иконку восклицания.

К чему я это всё описываю вроде бы к самому банальному коду из разряда "хэлоу, ворлд"? А к тому, что если мы захотим изменить этот кусок кода, то могли бы заменить одну из констант, нажав те самые Ctrl+J. Но есть подвох - откроется список всех возможных констант и объектов языка, а не только те константы, которые можно употреблять в этом параметре меседжбокса. Как же быть? Просто. Удаляем запятую перед перечислением этих констант. Или ставим ещё один знак плюса после одной из имеющихся констант.

И список выпадает опять. И он опять показывает только те константы, которые мы можем подставить, чтоб они работали. Таким образом, мы, очень слабо зная бэйсик и английский со словарём, можем писать целые программы, ни разу не заглянув в справку. Код сам подсказывает нам, что мы должны писать.

Окно немедленного выполнения команд Immediate.

Часто нам бывает нужно узнать результат вычисления какого-нибудь выражения или выполнить какую-то команду, не создавая специально для этого модуль. В экселе, например, это может быть команда, снимающая защиту листа:

ActiveSheet.Unprotect Password:="пароль"

Куда писать эту команду? Открываем редактор VBA и нажимаем клавишу Ctrl+G. Откроется окошко Immediate. Если оно уже было открыто, курсор встанет в него и мы можем сразу набирать, что захотим. Если надо, к примеру, узнать ответ на извечный вопрос, то так и пишем:

? 2+2

Нажимаем Enter и получаем ответ. Вопросительный знак перед командой требует вывести ответ в это же окошко. Если мы напишем:

? MsgBox ("текст сообщения", vbAbortRetryIgnore + vbDefaultButton2 + vbExclamation, "заголовок")

то увидим описанный нами меседжбокс. Но когда мы нажмём одну из его кнопок, то в окошке Immediate появится код этой кнопки. Т.е. результат работы этой "программы". Кстати, здесь перечислять параметры меседжбокса надо в скобках, т.к. этот синтаксис используется в норме в таком виде:

If MsgBox("текст сообщения", vbAbortRetryIgnore + vbDefaultButton2 + vbExclamation, "заголовок") = vbIgnore Then
MsgBox "меня все игнорируют"
End If

А ещё мы можем сделать наоборот. Писать программу в модуле, а результаты работы получать во временное место, невидимое пользователю - в это же окошко Immediate. Например в процедуре у нас есть цикл. Он что-то там делает и мы хотим узнать, что насчиталось в переменной a и какое значение имеет счётчик цикла после выхода из него:

Sub НашаПроцедура()
For i = 0 To 3
a = a + i
Debug.Print "итерация", i, a
Next
Debug.Print "итог", i, a
End Sub

результат:
итерация  0  0
итерация  1  1
итерация  2  3
итерация  3  6
итог  4  6

Результат вывелся с помощью Debug.Print. Через запятые можно перечислять все переменные, которые нас интересуют - они выведутся с отступом через табулятор. Теперь мы знаем, что в VBA счётчик цикла после выхода из цикла имеет значение на единицу больше, чем последняя итерация и он не обнуляется. Иногда бывают ошибки, когда переменная i в программе используется дальше и забыли её обнулить.

Итак, мы можем проверять значения различных переменных после отработки процедуры.

Режим отладки

Кстати, в общих модулях запустить любую процедуру можно прямо из кода. Т.е. для этого не надо писать где-то ещё "НашаПроцедура". Мы просто ставим курсор в любом месте кода процедуры и нажимаем клавишу F5.

Запустим вот такой глупый код:

Sub НашаПроцедура()
For i = 3 To 0 Step -1
a = a + i
a = a / i
Debug.Print a
Next
End Sub

Наша программа немного поработала, а потом выдала сообщение об ошибке.

Не будем его сразу закрывать и бежать на форумы с воплем "ваш пример не работает выдаёт какую-то ашипку". Посмотрим, что нам предлагает сделать сам VBA. Run-time error - это код ошибки. Его можно использовать в таком обработчике ошибок:

Sub НашаПроцедура()
On Error GoTo ErrorHandler
For i = 3 To 0 Step -1
a = a + i
a = a / i
Debug.Print a
Next

Exit Sub
ErrorHandler:
Select Case Err.Number
Case 11: MsgBox "На ноль делить нельзя", vbCritical
Case Else: MsgBox Err.Description, vbCritical, "Код ошибки: " & Err.Number
End Select
End Sub

On Error GoTo ErrorHandler - инструкция о том, что в случае ошибки программа должна перейти к метке ErrorHandler. Сама метка в коде обозначается её названием и двоеточием. Стоит она после строки Exit Sub - это ещё не конец процедуры, но без меток программа туда не попадёт. Затем начинается перебор всех кодов ошибок, которые в ходе работы процедуры могут возникнуть - Select Case Err.Number. Пока нам известен только код 11 - мы его увидели в сообщении. Для него мы и сделали своё грозное предупреждение, что так делать нельзя.

Если вы точно уверены, что программа даже с ошибкой будет работать правильно и ошибочное выражение можно смело пропустить, то вместо "On Error GoTo ErrorHandler" можно написать "On Error Resume Next". А если во время отладки хотите проверить конкретный участок кода, то прямо перед ним добавить "On Error GoTo 0". Тогда в случае ошибки появится обычное отладочное сообщение, как на картинке выше, а не переход к обработчику ошибок. Оно проигнорирует все предыдущие инструкции на счёт реакции на ошибку. Это бывает удобно, если отладчик уже написан, но закралась ещё одна ошибка, о которой мы ещё ничего не знаем.

Sub НашаПроцедура()

On Error GoTo ErrorHandler

For i = 3 To 0 Step -1

a = a + i

On Error GoTo 0

a = a / i

If i = 0 Then

a = 0

Else

a = a / i

End If

Debug.Print a

Next

Exit Sub

ErrorHandler:

Select Case Err.Number

Case 11: MsgBox "На ноль делить нельзя", vbCritical

Case Else: MsgBox Err.Description, vbCritical, "Код ошибки: " & Err.Number

End Select

End Sub

Продолжим изучать отладочное сообщение об ошибке. Возьмём для этого другой пример:

Sub Бесконечность()
Dim i As Integer
While i = 0
Debug.Print i
Wend
End Sub

После запуска этой процедуры никакую ошибку мы не получим. Только эксель у нас немного зависнет. Потому, что переменная i у нас с самого начала была равна нулю и в ходе выполнения цикла с ней ничего не происходит, только выводится её значение. Но цикл никак не может завершиться. Что же делать, если мы запустили такую программу? Перезагружать компьютер, открывать диспетчер задач? Не обязательно. В любой момент нажимаем клавиши Ctrl+Break. И вот в этом случае у нас и выходит отладочное сообщение, на котором кнопка Continue в этот раз доступна.

И нажав её, мы можем опять созерцать работу бесконечности. Ещё мы можем совсем остановить работу процедуры кнопкой End. Или кнопкой Debug поставить программу на паузу - редактор VBA покажет строчку, в которой программа остановилась. Подробности этого режима я опишу ниже.

Но остановить бесконечность нам удалось только потому, что компьютер у нас никогда из неё по своей воле не выйдет. А если программа будет чуть покороче бесконечности или компьютер достаточно быстрый, то мы не успеем уловить этот момент и прервать выполнение программы. А ведь иногда надо это сделать. Для этого у нас есть парочка способов.

Команда Stop

Эту команду мы можем вставить в промежуток между любыми строчками нашего кода. Когда программа в ходе своей работы наткнётся на неё, сообщение мы не увидим, но программа остановится. Немного модифицируем и запустим нашу бесконечность:

Когда переменная a достигает значения 100, программа останавливается на команде Stop. Сама она выделяется жёлтым маркером, а в левом поле редактора напротив неё стоит жёлтая стрелка - маркер перехода. Это та самая стрелка, которую мы можем увидеть после нажатия кнопки Debug в сообщении об ошибке. Мы можем схватить стрелку мышкой и переставить в любую другую строчку кода, затем опять нажать клавишу F5 и программа продолжит свою работу с этого места. Как это можно использовать? Можно нажать другую клавишу - F8. Она продолжает только одну команду в коде. Так мы можем вести программу по шагам. Выполнять очередной шаг и смотреть на результаты. Пока выполнение процедуры стоит на паузе, мы можем посмотреть значение переменной a, наведя на неё мышку. Можно сразу всю программу запустить в пошаговом режиме. Вместо F5 с самого начала нажать F8 и следить за каждым её шагом.

Точка останова

У команды Stop есть одновременно и достоинство и недостаток. Достоинство в том, что с её помощью мы можем оставить этакую закладку. Мы можем сохранить программу в таком виде, запустить её через некоторое время, и программа опять остановится в этом же месте, как бы напоминая нам, что в этом месте надо было сделать ещё что-то важное. Главное, не забыть о ней, отдавая программу пользователю. В этом и кроется её недостаток - о ней надо помнить, чтоб потом убрать из кода. А чтоб отладить программу и не оставлять следов этого, можно использовать точки останова. Они не сохраняются, если программу закрыть, что тоже удобно. Поставить её можно, щёлкнув в левое поле редактора напротив нужной строчки или нажать клавишу F9

Окно Watches

Останавливать программу мы научились. Пропустить команды можем. Можем посмотреть значения некоторых переменных. Но делается это как-то сложно. Надо много писать, смотреть в какие-то окна, не всегда понимая, значения каких именно переменных нам туда написало. А ведь у нас в распоряжении есть ещё одно удобное окошко - Watches. Никаких специальных клавиш для него не заведено. Потому, что его обычно размещают в удобном месте экрана и больше не трогают. Открыть его можно через меню View - Watch Window:

Изначально это окошко всегда пустое, а через него мы можем отслеживать состояние любого объекта среды программирования. Есть несколько способов, как это сделать. Если у нас в коде программы уже написаны какие-то объекты, то мы их можем:

1. перетащить мышкой в окно Watches

2. выделив название и нажав правой кнопкой мыши, выбрать в меню команду "Add Watch"

3. в самом окне открыть такое же меню и в диалоге задать параметры слежки

Этим же способом можно настроить слежку за теми объектами, которые мы добавляли из кода.

Мы можем этим способом остановить нашу бесконечность, когда переменная a будет равна сотне. Для этого добавляем в Watches не саму переменную, а выражение "a = 100", а в настройках слежки выбираем пункт "Break When Value Is True". Т.е. остановить программу, когда выражение "a = 100" станет истиной. Когда программа стоит на паузе, мы можем руками исправлять значение переменных в окне Locals (или Watches, если вынесем в него саму переменную, а не только выражение). Щёлкаем по имеющемуся значению в столбце Value и пишем туда нужное нам значение. Например, сделаем a равной 99.

Мы так же можем во время паузы выполнения программы размещать в Watches другие переменные и даже, листы экселя и формы со всеми их кнопками и полями.

Допустим, у нас есть такая таблица и мы хотим во втором столбце вывести порядковые номера напротив месяцев. Не знаю, зачем нам это и почему я решил это сделать не формулой экселя, а в VBA, но пусть будет

Когда мы запустим такой код, она остановится на обработке ячейки с Маем:

А в Watches мы можем посмотреть на свойства переменной r (её тип Range - ссылка на ячейку экселя). Плюсик напротив "r.Cells(i, 1)" развернёт нам всссеееее свойства ячейки с адресом A5. Просто полистав его, мы можем обнаружить много интересных свойств. И то, что свойство Formula имеет реальное значение 43221, соответствующее привычной нам дате 1.05.2018. И что формат ячейки в свойстве NumperFormat имеет значение "[$-419]ММММ;@", т.е. выводит только название месяца, что мы и видим в свойстве Text. Многие из этих параметров мы можем так же изменять в нашей процедуре, но теперь-то мы понимаем, какие из свойств нам надо использовать, чтоб, например, изменить в этой ячейке месяц. Подсказка: заменить май на июнь мы можем так:

r.Cells(i, 1) = DateValue("1.06.2018")

Формула у ячейки станет равной 43252. Если мы сделаем так:

r.Cells(i, 1) ="1.06.2018"

то формат "Месяц" в этой ячейке мы потеряем, а увидим мы сам текст "1.06.2018" вместо "Июнь". Вот такие нюансы бывают. Теперь-то можно понять, почему так происходит.

Если в процедуре мы используем много своих переменных, то, чтоб каждую из них не вытаскивать в Watches, мы можем увидеть их в окне Locals.

Но его список ограничен переменными текущей работающей процедуры. Какие-нибудь произвольные выражения или глобальные переменные мы туда вынести не можем.

Шаги обхода

Клавиши F5 и F8 я уже описал. Но у нас есть ещё несколько клавиш.

В таком примере запустим процедуру "Месяц":

Она заполнит первый столбец всеми календарными днями декабря, а во втором столбце с помощью функции "Выходной" укажет: рабочий это день или выходной. Допустим, в работе функции "Выходной" мы уверены и при проходе процедуры "Месяц" мы не хотим каждый раз перебирать все строчки функции. Для этого при пошаговом запуске, дойдя до

If Выходной(dt) = True Then

мы можем нажимать не F8, а Shift+F8 - перешагнуть. Т.е. просим VBA выполнить функцию, но не показывать, как она это делает. Но, если мы, всё таки, зашли в функцию и хотим из неё быстро выйти потому, что она нам не интересна, то мы нажимаем Ctrl+Shift+F8 - выйти.

Хоткей Ctrl+F8 - запустить до курсора. Мы можем поставить курсор в коде на несколько строчек позже текущего шага и перешагнуть через все строки, которые окажутся между ними. Это всё равно, что поставить на месте курсора точку останова и нажать F5.