Определяемые пользователем функции VBA и подпрограммы

В среде Visual Basic, входящей в состав Excel, набор команд для выполнения специфических задач называется процедурой, которая делится на два типа: функция (Function) и подпрограмма (Sub).

Главное отличие между этими понятиями заключается в том, что первая возвращает результат, в то время как вторая не делает этого.

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

Аргументы

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

Sub AddToCells(i As Integer)

      .

      .

      .

End Sub

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

Опциональные аргументы

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

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

Sub AddToCells(Optional i As Integer = 0)

Тогда аргумент i стандартно будет равняться нулю. Функция может содержать несколько необязательных аргументов. При перечислении их необходимо вставлять в конец списка.

Два способа передачи аргументов

Аргументы могут передаваться функциям несколькими способами:

1. По значению. Такой тип называется ByVal. В этом случае значение (то есть, копия аргумента) передается процедуре и, следовательно, любые изменения, которые вносятся внутрь аргумента процедуры, будут потеряны, когда процедура заканчивается.
2. По ссылке. ByRef. В этом случае адрес аргумента в памяти передается процедуре. Тогда любые изменения сохраняются после ее завершения.

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

Пример:

Sub AddToCells(ByVal i As Integer)         .         .         . End Sub	Здесь целочисленная переменная i передается по значению. Любые изменения, которые вносятся в нее, теряются, как только подпрограмма завершается.
Sub AddToCells(ByRef i As Integer)         .         .         . End Sub	Здесь тот же целочисленный аргумент передается по адресу в памяти. Когда подпрограмма завершается, любые изменения, которые совершены, запоминаются.

По умолчанию аргументы передаются по адресу. Следовательно, если не использовать ключевого слова, аргументы будут передаваться исходя из адреса в памяти и будут сохранены после окончания работы подпрограммы.

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

Функции VBA

Чтобы определить функцию в VBA, необходимо прописать следующие операторы:

Function

.

.

.

End Function

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

1. Тип данных возвращаемого значения должен определяться в заголовке функции.
2. Значение, которое возвращается, должно назначаться переменной, имеющей такое же имя, как и функция. Эта переменная не обязательно должна быть определена, поскольку она уже является частью функции.

Все это можно показать на примере, который вы увидите ниже.

Процедура «Function»: выполнение операции с тремя числовыми значениями

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

Function SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double

SumMinus = dNum1 + dNum2 — dNum3

End Function

Это элементарная процедура типа «Function», которая показывает способ, которым информация может передаваться процедуре. Также видно, что функция возвращает тип, который определяется как «Double» (такая команда дается интерпретатору с помощью ключевых слов As Double после приведения всех аргументов). Также этот пример демонстрирует, как сохраняется результат, возвращенный процедурой этого вида в переменной, которая имеет такое же имя, как и сама процедура.

Вызов функции

Если приведенный выше пример функции интегрирован в модуль в редакторе, его можно выполнить с других функций или непосредственно с листа в документе.

Вызов функции из иной функции

Функция может быть выполнена и из иной процедуры с помощью присваивания переменной к ней. В данном примере демонстрируется обращение к определенной ранее функции SumMinus.

Sub main()

   Dim total as Double

   total = SumMinus(5, 4, 3)

End Sub

Вызов с листа

Также можно вызывать процедуру непосредственно с листа способом, аналогичным встроенным. Например, так:

=SumMinus(10, 5, 2)

Подпрограммы VBA

Редактор макросов распознает подпрограмму по операторам, располагаемым в начале и в конце процедуры.

Sub

.

.

.

End Sub

Пример подпрограммы 1: Выставление выравнивания и модификация шрифта в заданном диапазоне

Давайте проанализируем легкий пример подпрограммы Excel, которая будет изменять форматирование определенного диапазона: выставляет в нем выравнивание по центру и определенный пользователем размер букв и цифр. 

Sub Format_Centered_And_Sized(Optional iFontSize As Integer = 10)

   Selection.HorizontalAlignment = xlCenter

   Selection.VerticalAlignment = xlCenter

   Selection.Font.Size = iFontSize

End Sub

Поскольку это подпрограмма, она не возвращает результата. Ее задача – выполнение определенной последовательности действий.

Здесь также используется опциональный аргумент iFontSize. Если он не передается подпрограмме, он автоматически становится равным 10. Но если этот аргумент определен, то в соответствующем диапазоне шрифт станет такого же размера, как было определено пользователем.

Пример 2: Выравнивание и применение начертания

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

Вызов подпрограммы VBA

Вызов подпрограммы через VBA

Вызов процедуры Sub возможен через VBA с помощью ключевого слова «Call», после которого сразу пишется название подпрограммы. Вот пример, который демонстрирует, как это осуществляется на практике:

Sub main()

Call Format_Centered_And_Sized( 20 )

End Sub

Если приведенная в примере подпрограмма имеет больше одного аргумента, они могут быть разделены запятыми. Например.

Sub main()

Call Format_Centered_And_Sized( arg1, arg2, … )

End Sub

Вызов подпрограммы с документа

Подпрограммы не могут выполняться непосредственно с документа Excel аналогично функциям, поскольку процедура «Sub» не выдает результата. Несмотря на это, если она публичная, пользователи документа могут ее применять. При этом важно, чтобы она не имела аргументов. Это значит, что, если вставить описанные выше примеры в среду Visual Basic, подпрограмма Format_Centered_And_Bold сможет применяться пользователем.

Подпрограммы, которые могут запускаться в документе, вызываются так:

1. Необходимо открыть диалоговое окно «Макросы» с помощью нажатия клавиш Alt+F8. Для этого необходимо сначала нажать первую кнопку, после чего задержать ее и нажать вторую. Все другие комбинации работают так же.
2. Там приводится перечень созданных пользователем макросов. Необходимо выбрать подходящий для текущей задачи.
3. Нажать кнопку «Выполнить».

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

1. Путем нажатия комбинации Alt+F8 нужно открыть диалоговое окно с макросами и выбрать тот макрос, который необходимо связать с горячими клавишами.
2. Нажать «Параметры» и ввести то сочетание, которое хочется применять для запуска процедуры. 
3. Нажать «ОК» и закрыть окно.

Важно отметить, что при назначении комбинации горячих клавиш для подпрограммы необходимо удостовериться, что выбранная комбинация не зарезервирована под стандартные функции Excel. Например, нельзя использовать комбинацию Ctrl+V, поскольку она предназначена, чтобы вставлять информацию из буфера обмена.

Задание прав процедурам

Права функции задаются с помощью слов «Public» и «Private». Чтобы стало понятнее, приводим таблицу с примерами:

Public Sub AddToCells(i As Integer)         .         .         . End Sub	Если процедура объявляется с помощью ключевого слова «Public», это открывает доступ к ней для всех остальных частей проекта.
Private Sub AddToCells(i As Integer)         .         .         . End Sub	Если объявление осуществляется с помощью ключевого слова «Private», то только текущий модуль может получить доступ к ней. С других же документов или модулей пользователь не сможет вызвать эту процедуру.

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

Ранний выход

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

Function VAT_Amount(sVAT_Rate As Single) As Single

   VAT_Amount = 0

   If sVAT_Rate <= 0 Then

      MsgBox «Expected a Positive value of sVAT_Rate but Received » & sVAT_Rate

      Exit Function

   End If

…

End Function

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

Она может принимать следующие аргументы:

1. Prompt. Текст, который отображается в диалоговом окне. Обязательный аргумент. Все остальные – опциональные.
2. Buttons. Аргумент, который определяет количество кнопок, которые будут отображаться диалоговым окном. Может принимать несколько значений. Например, если он содержит значение vbOKCancel, диалоговое окно будет иметь только две кнопки: ОК и “Отменить”. Но в нашем случае лучше всего подходит значение vbYesNo.
3. Title. Определяет заголовок диалогового окна.
4. HelpFile. Позволяет привязать к окну справочный файл.
5. Context. ID элемента справки. Обязательный аргумент, если используется HelpFile.

После того, как пользователь нажал определенную кнопку, функция возвращает определенное значение. Следовательно, приведенный выше код может модифицироваться в зависимости от задач. Например, спросить пользователя, не хотел бы он изменить значение переменной sVAT_Rate, и если пользователь нажимает «Да», то сделать его положительным. Предварительно нужно прописать переменную.

Здесь можно проявить фантазию. Но вообще, использование диалогового окна в макросах – это совсем другая тема, которая в данном случае требует еще и умения использовать условные операторы для выполнения действий. Главное, что вы умеете определять функции и знаете их типы и основные отличия между ними. Все остальное придет с практикой и желанием непрерывно обучаться. Удачи.