Процедуры (Sub) и функции (Function) VBA, объявление процедур и функций, макросы - специальный тип процедур VBA, типы процедур

Процедуры -- это самые важные функциональные блоки языка VBA. В VBA вы можете выполнить только программный код, который содержится в какой-либо процедуре (обычной в стандартном модуле, событийной для элемента управления на форме и т.п.). Иногда начинающие пользователи пытаются записать команды прямо в область объявлений стандартного модуля и не могут понять, почему они не выполняются (сообщений о ошибке при этом не выдается -- просто этот код становится "невидим" для компилятора). Причина проста -- в разделе объявлений модуля (когда в верхних списках показываются значения (General) и (Declarations) могут быть только объявления переменных уровня модуля и некоторые специальные инструкции для компилятора. Весь остальной программный код должен находится внутри процедур.

В VBA предусмотрены следующие типы процедур:

Процедура типа Sub (подпрограмма) -- универсальная процедура для выполнения каких-либо действий:

Sub Farewell()

MsgBox “Goodbye”

End Sub

Макрос в VBA -- это просто процедура типа Sub, не имеющая параметров. Только макросы можно вызывать по имени из редактора VBA или приложения Office. Все другие процедуры нужно вызывать либо из других процедур, либо специальными способами, о которых будет рассказано ниже.

Процедура типа Function (функция) -- тоже набор команд, которые должны быть выполнены. Принципиальное отличие только одно: функция возвращает вызвавшей ее программе какое-то значение, которое там будет использовано. Пример процедуры:

Function Tomorrow()

Tomorrow = DateAdd("d", 1, Date())

End Function

и пример ее вызова:

Private Sub Test1()

Dim dDate

dDate = Tomorrow

MsgBox dDate

End Sub

В тексте функции необходимо предусмотреть оператор, который присваивает ей какое-либо значение. В нашем случае это строка Tomorrow = DateAdd(" d", 1, Date()).

В принципе, процедуры типа Sub тоже могут возвращать значения -- при помощи переменных (об этом -- ниже). Зачем же тогда нужны функции? Все очень просто: функцию можно вставлять практически в любое место программного кода. Например, наш последний пример может выглядеть намного проще:

Private Sub Test 1()

MsgBox Tomorrow()

End Sub

В VBA предусмотрены сотни встроенных функций (и гораздо большее число предусмотрено в объектных моделях приложений Office). Даже в нашем примере используются две встроенные функции: Date(), которая возвращает текущую дату по часам компьютера и DateAdd(), которая умеет прибавлять к текущей дате определенное количество дней, недель, месяцев, лет и т.п. Про встроенные функции будет рассказано ниже.

В VBA имеются также процедуры обработки событий (event procedure) -- процедуры типа Sub специального назначения, которые выполняется в случае возникновении определенного события. Пример был приведен выше (Private Sub UserForm_Click()). Про события подробнее будет рассказано в модуле про формы и события.

Есть еще процедуры типа Property (процедуры свойства). Они нужны для определения свойств создаваемого вами класса, а поскольку созданием своих классов мы заниматься не будем, то их можно не рассматривать.

8.2 Область видимости процедур

Области видимости процедур VBA, объявления Public, Private, Static, команда Option Private Module

По умолчанию все процедуры VBA (за исключением процедур обработки событий) определяются как открытые (Public). Это значит, что их можно вызвать из любой части программы -- из того же модуля, из другого модуля, из другого проекта. Объявить процедуру как Public можно так: Public Sub Farewell() или, поскольку процедура определяется как Public по умолчанию, то можно и так: Sub Farewell()

Можно объявить процедуру локальной: Private Sub Farewell()

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

Можно ограничить область видимости открытых процедур (тех, которые у вас определены как Public) в каком-то модуле рамками одного проекта. Для этого достаточно в разделе объявлений этого модуля вписать строку Option Private Module

Если при объявлении процедуры использовать ключевое слово Static, то все переменные в этой процедуре автоматически станут статическими и будут сохранять свои значения и после завершения работы процедуры (см. раздел про переменные). Пример: Private Static Sub Farewell()

8.3 Объявление процедур

Объявление процедур в VBA

Объявить процедуру можно вручную, например, впечатав в коде строку Private Sub Farewell () (редактор кода автоматически добавит строку End Sub и разделитель), а можно -- на графическом экране, воспользовавшись меню Insert -> Procedure. Разницы не будет никакой.

8.4 Передача параметров

Передача параметров процедурам и функциям в VBA, необязательные (optional) параметры, передача по ссылке (ByRef) и по значению (ByVal), применение ссылок при передаче параметров

Параметры -- значения, которые передаются от одной процедуры другой. В принципе, можно обойтись и без параметров, воспользовавшись только переменными уровня модуля, но при использовании параметров читаемость программы улучшается. Чтобы процедура имела возможность принимать параметры, ее вначале нужно объявить с параметрами. Например, вот пример простой функции, которая складывает два числа и выводит результат:

Function fSum (nItem1 As Integer, nItem2 As Integer)

fSum = nItem1 + nItem2

End Function

Вызов ее может выглядеть так:

MsgBox(fSum(3, 2))

В данном случае мы объявили оба параметра как обязательные, и поэтому попытка вызвать функцию без передачи ей какого-либо параметра (например, так: MsgBox (fSum(3))) приведет к ошибке "Argument not optional" -- "Параметр не является необязательным". Чтобы можно было пропускать какие-то параметры, эти параметры можно сделать необязательными. Для этой цели используется ключевое слово Optional:

Function fSum (nItem1 As Integer, Optional nItem2 As Integer)

В справке по встроенным функциям VBA необязательные параметры заключаются в квадратные скобки.

Для проверки того, был ли передан необязательный параметр, используется либо функция IsMissing (если для этого параметра был использован тип Variant), либо его значение сравнивается со значениями переменных по умолчанию (ноль для числовых данных, пустая строка для строковых и т.п.)

Вызов функции с передачей параметров может выглядеть так: nResult = fSum (3, 2)

Однако здесь есть несколько моментов, которые необходимо рассмотреть.

В нашем примере мы передаем параметры по позиции, то есть значение 3 присваивается первому параметру (nItem1), а значение 2 -- второму (nItem2). Однако параметры можно передавать и по имени:

nResult = fSum (nItem 1 := 3, nItem 2 := 2)

Обратите внимание, что несмотря на то, что здесь выполняется вполне привычная операция -- присвоение значений, оператор присвоения используется не совсем обычный -- двоеточие со знаком равенства, как в C++. При использовании знака равенства возникнет ошибка.

Конечно, вместо явной передачи значений (как у нас -- 3 и 2) можно использовать переменные. Однако что произойдет с переменными после того, как они "побывают" в функции, если функция изменяет их значение? Останется ли это значение за пределами функции прежним или изменится?

Все зависит от того, как именно передаются параметры -- по ссылке (по умолчанию, можно также использовать ключевое слово ByRef или по значению -- нужно использовать ключевое слово ByVal).

Если параметры передаются по ссылке, то фактически в вызываемую процедуру передается ссылка на эту переменную в оперативной памяти. Если эту переменную в вызываемой процедуре изменить, то значение изменится и в вызывающей функции. Это -- принятое в VBA поведение по умолчанию.

Если параметры передаются по значению, то фактически в оперативной памяти создается копия этой переменной и вызываемой процедуре передается эта копия. Конечно же, чтобы вы не сделали с этой копией, на исходную переменную это никак не повлияет и в вызывающей процедуре не отразится.

Продемонстрировать разницу можно на простом примере: Private Sub TestProc ()

'Объявляем переменную nPar1 и присваиваем ей значение

Dim nPar1 As Integer

nPar1 = 5

'Передаем ее как параметр nItem1 функции fSum

MsgBox (fSum(nItem1:=nPar1, nItem2:=2))

'А теперь проверяем, что стало в нашей переменной nPar1, 'после того, как она побывала в функции fSum:

MsgBox nPar1

End Sub

Function fSum(nItem1 As Integer, nItem2 As Integer)

'Используем значение переменной

fSum = nItem 1 + nItem 2

'А затем ее меняем!

nItem 1 = 10

End Function

Проверьте, что будет, если поменять строку объявления функции

Function fSum(nItem1 As Integer, nItem2 As Integer)

на следующую строку :

Function fSum(byVal nItem1 As Integer, nItem2 As Integer)

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

а для нашей функции -- fSum 3, 2

Такой код будет работать совершенно нормально. Однако, если нам потребуется все-таки узнать, что возвращает MsgBox, то придется передаваемые ему параметры заключать в круглые скобки:

nTest = MsgBox("Test")

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

8.5 Запуск и завершение работы процедур

Способы запуска и завершения работы процедур в VBA, конструкция End Sub, конструкция Exit Sub

С примерами вызова процедур и функций из кода мы уже сталкивались:

nResult = fSum (3, 2)

Достаточно записать имя процедуры/функции, передать ей необходимые параметры, а для функции еще и принять необходимые значения. Одни процедуры можно вызывать из других процедур. Но как дать пользователю возможность запустить самую первую процедуру?

В нашем распоряжении следующие возможности:

· создать макрос (то есть специальную процедуру, не принимающую параметров, в модуле NewMacros) и запустить его по имени, кнопке или комбинации клавиш (см. раздел 1.4). Макрос затем может вызвать другие процедуры;

· создать форму и воспользоваться набором событий этой формы и элементов управления на ней (об этом рассказано в разделе, посвященном работе с формами и элементами управления), или просто элементом управления на листе Excel или документе Word;

· назначить процедуре специальное имя (AutoExec(), AutoNew() и т.п.). Полный список таких специальных имен можно посмотреть в документации. Правда, поскольку раньше эти возможности активно использовались вирусами, в Office2003 по умолчанию эти макросы запускаться не будут. Для того, чтобы обеспечить им (и многим другим макросам) возможность запуска, необходимо изменить установленный режим безопасности в меню Сервис -> Макрос -> Безопасность, или обеспечить подписи для ваших макросов (об этом будет говориться ниже).

· вместо специального имени для макроса использовать событие: например, событие запуска приложения, событие открытия документа и т.п. Это -- рекомендованный Microsoft способ обеспечения автоматического запуска программного кода;

· можно запустить приложение из командной строки с параметром /m и именем макроса, например: winword.exe /mMyMacros

Очень удобен в использовании ярлык Windows, в котором прописан этот параметр запуска.

В VBA вполне допустима ситуация, когда функция запускает на выполнение саму себя. Однако подобных ситуаций лучше избегать (по возможности заменяя их на циклы). Причина -- проблемы с читаемостью и возможное (в случае бесконечного запуска функцией самой себя) исчерпание оперативной памяти (переполнение стека), чреватое серьезными системными ошибками.

Для завершения выполнения процедуры в VBA предусмотрены конструкции End и Exit. Синтаксис их очень прост: Exit Sub End Sub

Делают они одно и то же -- завершают работу текущей процедуры. Однако используются они в разных ситуациях:

· оператор End -- это завершение работы процедуры после того, как все сделано. После оператора End код процедуры заканчивается;

· оператор Exit -- это немедленное завершение работы функции в ходе ее работы. Обычно помещается в блок оператора условного перехода, чтобы произвести выход, как только выяснилось, что функции по каким-то причинам дальше выполняться нельзя (например, дальше идет код обработчика ошибок).

9. Встроенные функции языка Visual Basic For Applications

9.1 Встроенные функции языка Visual Basic For Applications

Встроенные функции VBA, справка по встроенным функциям

В языке программирования VBA предусмотрено несколько десятков встроенных функций. Они доступны в любой программе на языке VBA, при этом безразлично, в среде какого программного продукта мы находимся -- Excel, Word, Access или, к примеру, AutoCAD. Используются они очень активно, и во многих ситуациях без них не обойтись. Профессиональные программисты применяют их совершенно автоматически, а обычным пользователям хочется посоветовать потратить несколько часов на знакомство с ними -- потому что без знаниях этих функций эффективно работать в VBA не получится. Дополнительный аргумент в пользу их изучения: практически идентичный набор функций есть в обычном Visual Basic и VBScript, а многие из этих функций с теми же названиями и синтаксисом встречаются и в других языках программирования -- C++, Delphi, Java, JavaScript и т.п. В справке по VBA встроенные функции сгруппированы по буквам (см. рис. 2).

Рис. 2 Справка по встроенным функциям

Многие слушатели на курсах задавали вопрос: а нет ли справки по этим функциям на русском языке? К сожалению, такой справки мне найти не удалось, поэтому попытаюсь привести такую справку в этом курсе. Ниже будет рассказано про большинство активно используемых функций языка VBA (математические функции, которые в практической работе почти не используется, типа косинуса или тангенса, и финансовые функции мы рассматривать не будем). Полный синтаксис для экономии места приводиться не будет: главное -- понимание, что делает каждая функция и в каких ситуациях ее можно использовать.

Функции в следующих разделах сгруппированы по своей функциональности.

9.2 Функции преобразования и проверки типов данных

Встроенные функции преобразования и проверки типов данных VBA, функции CBool(), CByte(), CCur(), CDate(), CDbl(), CDec(), CInt(), CLng(), CSng(), CStr(), CVar(), CVDate(), CVErr(), Str(), Val(), IsNumeric(), IsDate(), IsEmpty(), IsError(), IsMissing(), IsNull(), IsObject(), IsArray(), Hex(), Oct()

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

· преобразование из строкового значение в числовое при приеме значения от пользователя через InputBox();

· преобразование значения даты/времени в строковое, когда нам нужно отобразить дату или время единообразно вне зависимости от региональных настроек на компьютерах пользователей;

· преобразование значения из строкового в дату/время для применения специальных функций даты/времени.

Чаще всего для конвертации типов данных используются функции, имя которых выглядит как C (от слова Convert) + имя типа данных. Вот перечень этих функций: CBool(), CByte(), CCur(), CDate(), CDbl(), CDec(), CInt(), CLng(), CSng(), CStr(), CVar(), CVDate(), CVErr(). Просмотреть, что в итоге получилось, можно при помощи функции TypeName(), например:

nVar1 = CInt(InputBox("Введите значение"))

MsgBox TypeName(nVar1)

Кроме того, еще несколько полезных для конвертации функций:

· Str() -- позволяет перевести числовое значение в строковое. Делает почти то же самое, что и CStr(), но при этом вставляет пробел впереди для положительных чисел.

· Val() -- "вытаскивает" из смеси цифр и букв только числовое значение. При этом эта функция читает данные слева направо и останавливается на первом нечисловом значении (допускается единственное нечисловое значение -- точка, которая будет отделять целую часть от дробной). Очень удобно, когда у нас вперемежку с числовыми данными прописываются единицы измерения или валюта.

Чтобы не возникло ошибок при конвертации, можно вначале проверять значения на возможность конвертации при помощи функций IsNumeric() и IsDate(). Для проверки на соответствие специальным значениям можно использовать функции IsArray(), IsEmpty(), IsError(), IsMissing(), IsNull() и IsObject(). Все эти функции возвращают True или False в зависимости от результатов проверки переданного им значения.

Для того, чтобы преобразовать десятичные данные в строковое представление шестнадцатеричных и восьмеричных значений, используются функции Hex() и Oct(). Для обратного преобразования специальных функций не предусмотрено, но вы можете указать компилятору VBA, что эти числа записаны в шестнадцатеричном или восьмеричном формате, записав их, например, как &O12 и &HA.