Содержание:
- Встроенные функции VBA
- Пользовательские процедуры «Функция» и «Подчиненная» в VBA.
- аргументы
- Процедура VBA «Функция»
- Процедура VBA «Sub»
- Область действия процедуры VBA
- Досрочный выход из процедур VBA «Функция» и «Подпрограмма»
Встроенные функции VBA
Прежде чем приступить к созданию собственных функций VBA, полезно знать, что Excel VBA имеет богатую коллекцию готовых встроенных функций, которые вы можете использовать при написании кода.
Список этих функций можно просмотреть в редакторе VBA:
- Откройте книгу Excel и запустите редактор VBA (нажмите, чтобы сделать это Alt + F11), а затем нажмите F2.
- Выберите библиотеку из раскрывающегося списка в левом верхнем углу экрана. VBA.
- Появится список встроенных классов и функций VBA. Нажмите на название функции, чтобы отобразить ее краткое описание внизу окна. нажатие F1 откроется страница онлайн-справки для этой функции.
Кроме того, полный список встроенных функций VBA с примерами можно найти в Центре разработчиков Visual Basic.
Пользовательские процедуры «Функция» и «Подчиненная» в VBA.
В Excel Visual Basic набор команд, выполняющих определенную задачу, помещается в процедуру. Функция (Функция) или ниже (Подпрограмма). Основное отличие процедур Функция и ниже это процедура Функция возвращает результат, процедуру ниже - нет.
Поэтому, если вам необходимо выполнить действия и получить какой-то результат (например, просуммировать несколько чисел), то обычно используется процедура Функция, а чтобы просто выполнить какие-то действия (например, изменить форматирование группы ячеек), нужно выбрать процедуру ниже.
аргументы
Различные данные можно передавать процедурам VBA с помощью аргументов. Список аргументов указывается при объявлении процедуры. Например, процедура ниже в VBA добавляет заданное целое число (Integer) в каждую ячейку выбранного диапазона. Вы можете передать это число процедуре, используя аргумент, например:
Sub AddToCells (i As Integer)... Конец Sub
Имейте в виду, что наличие аргументов для процедур Функция и ниже в VBA не является обязательным. Некоторые процедуры не требуют аргументов.
Необязательные аргументы
Процедуры VBA могут иметь необязательные аргументы. Это аргументы, которые пользователь может указать, если захочет, и если они опущены, процедура использует для них значения по умолчанию.
Возвращаясь к предыдущему примеру, чтобы сделать целочисленный аргумент функции необязательным, его следует объявить следующим образом:
Sub AddToCells (необязательно i как целое число = 0)
В этом случае целочисленный аргумент i по умолчанию будет 0.
В процедуре может быть несколько необязательных аргументов, все из которых перечислены в конце списка аргументов.
Передача аргументов по значению и по ссылке
Аргументы в VBA можно передать процедуре двумя способами:
- ByVal – передача аргумента по значению. Это означает, что процедуре передается только значение (то есть копия аргумента), и поэтому любые изменения, внесенные в аргумент внутри процедуры, будут потеряны при выходе из процедуры.
- По ссылке – передача аргумента по ссылке. То есть процедуре передается фактический адрес расположения аргумента в памяти. Любые изменения, внесенные в аргумент внутри процедуры, будут сохранены при выходе из процедуры.
Использование ключевых слов ByVal or По ссылке в объявлении процедуры вы можете указать, как аргумент передается процедуре. Это показано в примерах ниже:
Sub AddToCells (ByVal i As Integer)... Конец Sub | В этом случае целочисленный аргумент i передается по значению. После выхода из процедуры ниже все сделано с i изменения будут потеряны. |
Sub AddToCells (ByRef i As Integer)... End Sub | В этом случае целочисленный аргумент i передано по ссылке. После выхода из процедуры ниже все сделано с i изменения будут сохранены в переменной, переданной в процедуру ниже. |
Помните, что аргументы в VBA по умолчанию передаются по ссылке. Другими словами, если ключевые слова не используются ByVal or По ссылке, то аргумент будет передан по ссылке.
Прежде чем приступить к процедурам Функция и ниже более подробно будет полезно еще раз взглянуть на особенности и различия этих двух видов процедур. Ниже приведены краткие обсуждения процедур VBA. Функция и ниже и показаны простые примеры.
Процедура VBA «Функция»
Редактор VBA распознает процедуру Функциякогда он встречает группу команд, заключенную между следующими открывающими и закрывающими операторами:
Функция... Конечная функция
Как уже говорилось ранее, процедура Функция в VBA (в отличие от ниже) возвращает значение. К возвращаемым значениям применяются следующие правила:
- Тип данных возвращаемого значения должен быть объявлен в заголовке процедуры. Функция.
- Переменная, содержащая возвращаемое значение, должна иметь то же имя, что и процедура. Функция. Эту переменную не нужно объявлять отдельно, поскольку она всегда существует как неотъемлемая часть процедуры. Функция.
Это хорошо иллюстрируется на следующем примере.
Пример функции VBA: выполнение математической операции над тремя числами
Ниже приведен пример кода процедуры VBA. Функция, который принимает три аргумента типа двойной (числа двойной точности с плавающей запятой). В результате процедура возвращает еще одно число типов двойнойравно сумме первых двух аргументов минус третий аргумент:
Функция SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double SumMinus = dNum1 + dNum2 - dNum3 Конечная функция
Эта очень простая процедура VBA Функция иллюстрирует, как данные передаются в процедуру через аргументы. Вы можете видеть, что тип данных, возвращаемый процедурой, определяется как двойной (слова говорят Как двойной после списка аргументов). Этот пример также показывает, как результат процедуры Функция хранится в переменной с тем же именем, что и имя процедуры.
Вызов процедуры VBA «Функция»
Если описанная выше простая процедура Функция вставленный в модуль в редакторе Visual Basic, его можно вызвать из других процедур VBA или использовать на листе в книге Excel.
Вызов процедуры VBA «Функция» из другой процедуры.
Процедура Функция можно вызвать из другой процедуры VBA, просто присвоив эту процедуру переменной. В следующем примере показан вызов процедуры Сумминус, который был определен выше.
Sub main() Тусклый итог как двойной итог = SumMinus(5, 4, 3) End Sub
Вызов процедуры VBA «Функция» с рабочего листа
Процедура VBA Функция можно вызвать из листа Excel так же, как и любую другую встроенную функцию Excel. Поэтому процедура, созданная в предыдущем примере Функция – Сумминус можно вызвать, введя следующее выражение в ячейку листа:
=SumMinus(10, 5, 2)
Процедура VBA «Sub»
Редактор VBA понимает, что перед ним есть процедура нижекогда он встречает группу команд, заключенную между следующими открывающими и закрывающими операторами:
Суб... Конец сабвуфера
Процедура VBA «Sub»: Пример 1. Выравнивание по центру и изменение размера шрифта в выделенном диапазоне ячеек.
Рассмотрим пример простой процедуры VBA. ниже, задача которого — изменить форматирование выделенного диапазона ячеек. Ячейки центрируются (как по вертикали, так и по горизонтали), а размер шрифта меняется на заданный пользователем:
Sub Format_Centered_And_Sized (необязательно iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub
Эта процедура ниже выполняет действия, но не возвращает результат.
В этом примере также используется необязательный аргумент Размер шрифта. Если аргумент Размер шрифта не передан в процедуру ниже, то его значение по умолчанию — 10. Однако, если аргумент Размер шрифта передан в процедуру ниже, то для выбранного диапазона ячеек будет установлен размер шрифта, указанный пользователем.
Подпроцедура VBA: пример 2: выравнивание по центру и жирный шрифт в выбранном диапазоне ячеек
Следующая процедура аналогична только что рассмотренной, но на этот раз вместо изменения размера она применяет жирный шрифт к выбранному диапазону ячеек. Это пример процедуры ниже, который не принимает аргументов:
Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub
Вызов «под» процедуры в Excel VBA
Вызов процедуры VBA «Sub» из другой процедуры
Чтобы вызвать процедуру VBA ниже из другой процедуры VBA вам нужно написать ключевое слово Позвонить, имя процедуры ниже и далее в скобках — аргументы процедуры. Это показано в примере ниже:
Sub main() Вызов Format_Centered_And_Sized(20) End Sub
Если процедура Format_Centered_And_Sized имеет более одного аргумента, они должны быть разделены запятыми. Так:
Sub main() Вызов Format_Centered_And_Sized(arg1, arg2, ...) End Sub
Вызов процедуры VBA «Sub» с рабочего листа
Процедура ниже нельзя ввести непосредственно в ячейку листа Excel, как это можно сделать с помощью процедуры Функцияпотому что процедура ниже не возвращает значение. Однако процедуры ниже, которые не имеют аргументов и объявляются как Общая (как показано ниже) будет доступен пользователям рабочего листа. Таким образом, если простые процедуры, рассмотренные выше, ниже вставленная в модуль редактора Visual Basic, процедура Format_Centered_And_Bold будет доступен для использования на листе Excel, а процедура Format_Centered_And_Sized – не будет доступен, поскольку у него есть аргументы.
Вот простой способ запустить (или выполнить) процедуру ниже, доступный из рабочего листа:
- Нажмите Alt + F8 (нажмите клавишу другой и, удерживая ее, нажмите клавишу F8).
- В появившемся списке макросов выберите тот, который хотите запустить.
- Нажмите Run (бежать)
Чтобы выполнить процедуру ниже быстро и легко, вы можете назначить ему сочетание клавиш. Для этого:
- Нажмите Alt + F8.
- В появившемся списке макросов выберите тот, которому вы хотите назначить сочетание клавиш.
- Нажмите параметры (Параметры) и в появившемся диалоговом окне введите сочетание клавиш.
- Нажмите OK и закрыть диалог Макрос (Макро).
Внимание: Назначая макросу сочетание клавиш, убедитесь, что оно не используется стандартно в Excel (например, Ctrl + C). Если вы выберете уже существующее сочетание клавиш, оно будет переназначено макросу, в результате чего пользователь может случайно запустить макрос.
Область действия процедуры VBA
Во второй части этого руководства обсуждалась область действия переменных и констант, а также роль ключевых слов. Общая и Частный. Эти ключевые слова также можно использовать с процедурами VBA:
Public Sub AddToCells (i As Integer)... End Sub | Если объявлению процедуры предшествует ключевое слово Общая, то процедура будет доступна для всех модулей в этом проекте VBA. |
Частная подпрограмма AddToCells (i As Integer)... Конечная подпрограмма | Если объявлению процедуры предшествует ключевое слово Частный, то эта процедура будет доступна только для текущего модуля. Его нельзя вызвать из любого другого модуля или из книги Excel. |
Помните, что если перед объявлением процедуры VBA Функция or ниже ключевое слово не вставлено, для процедуры установлено свойство по умолчанию Общая (то есть он будет доступен везде в этом проекте VBA). В этом отличие от объявлений переменных, которые по умолчанию Частный.
Досрочный выход из процедур VBA «Функция» и «Подпрограмма»
Если вам нужно прекратить выполнение процедуры VBA Функция or ниже, не дожидаясь его естественного окончания, то для этого есть операторы Функция выхода и Exit Sub. Использование этих операторов показано ниже на примере простой процедуры. ФункцияA, который ожидает получить положительный аргумент для выполнения дальнейших операций. Если в процедуру передается неположительное значение, дальнейшие операции выполняться не могут, поэтому пользователю должно быть показано сообщение об ошибке, и процедура должна немедленно завершиться:
Функция НДС_Amount(sVAT_Rate As Single) Как одиночная НДС_Amount = 0 Если sVAT_Rate <= 0 Тогда MsgBox «Ожидал положительное значение sVAT_Rate, но получено» & sVAT_Rate Выход из функции End If... End Function
Обратите внимание, что перед завершением процедуры Функция - Сумма НДС, в код вставляется встроенная функция VBA MsgBox, который отображает всплывающее окно с предупреждением для пользователя.