Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Описание функций vba excel

VBA-Урок 9. Процедуры и функции

Public — Private

На данный момент, все процедуры, мы создавали, имеют тип Public , что означает, что они доступны из любого модуля.

Чтобы сделать процедуру доступной только в определенном модуле, используется ключевое слово Private:

Запуск процедуры с середины другой процедуры

Чтобы выполнить процедуру с середины другой процедуры, просто введите ее название.

Здесь есть очень простой пример:

Аргументы

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

К процедуре «warning» был добавлен аргумент, в данном случае это переменная «var_text» с типом «String» (строка):

Эта процедура требует аргумент, поэтому мы должны поставить значение после «warning», чтобы выполнить ее:

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

Необязательные аргументы

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

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

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

Аргументы должны быть введены в правильном порядке.

Чтобы протестировать, присутствует ли опциональный аргумент в процедуре, мы используем функцию IsMissing . Эта функция совместима только с некоторыми типами функций (типа Variant) и это является решающим, так как тип необязательно аргументов не был указан в объявлении (необъявленный тип = Variant).

Здесь есть пример, который использует два фрагмента кода, которые рассматривались выше:

См. рисунок ниже (пример 1):

ByRef — ByVal

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

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

Второй метод заключается в использовании ByVal .

В отличие от ByRef , который передает ссылки (ярлык), ByVal передает значение, которое означает, что значение передано как аргумент не было изменено.

Ниже вы можете увидеть как предыдущий код и ByVal работают:

Что вам нужно запомнить: используйте ByVal когда переменная не должна быть изменена .

Функции

Основным отличием между процедурой и функцией является то, что функция возвращает значение.

Вот простой пример:

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

Например, чтобы получить квадрат значения, которое введенное в ячейку A1:

VBA Excel. Пользовательская функция (синтаксис, компоненты)

Создание пользовательской функции в VBA Excel, ее синтаксис и компоненты. Описание пользовательской функции и ее аргументов. Метод Application.MacroOptions.

Объявление пользовательской функции

Синтаксис функции

Компоненты функции

  • Static — необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
  • Имя — обязательный компонент, имя пользовательской функции.
  • СписокАргументов — необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
  • Операторы — необязательный компонент, блок операторов (инструкций).
  • Имя = выражение — необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
  • Exit Function — необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.

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

Видимость функции

Видимость пользовательской функции определяется необязательными ключевыми словами Public и Private, которые могут быть указаны перед оператором Function (или Static, в случае его использования).

Ключевое слово Public указывает на то, что функция будет доступна для вызова из других процедур во всех модулях открытых книг Excel. Функция, объявленная как Public, отображается в диалоговом окне Мастера функций.

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

Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.

Чтобы пользовательская функция всегда была доступна во всех открытых книгах Excel, сохраните ее в Личной книге макросов без объявления видимости или как Public. Но если вы планируете передать рабочую книгу с пользовательской функцией на другой компьютер, код функции должен быть в программном модуле передаваемой книги.

Пример пользовательской функции

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

Процедуры «Function» и «Sub» в VBA

Встроенные функции VBA

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

Читать еще:  Ширина столбца в excel в см

Список этих функций можно посмотреть в редакторе VBA:

  • Откройте рабочую книгу Excel и запустите редактор VBA (нажмите для этого Alt+F11), и затем нажмите F2.
  • В выпадающем списке в верхней левой части экрана выберите библиотеку VBA.
  • Появится список встроенных классов и функций VBA. Кликните мышью по имени функции, чтобы внизу окна отобразилось её краткое описание. Нажатие F1 откроет страницу онлайн-справки по этой функции.

Кроме того, полный список встроенных функций VBA с примерами можно найти на сайте Visual Basic Developer Centre.

Пользовательские процедуры «Function» и «Sub» в VBA

В Excel Visual Basic набор команд, выполняющий определённую задачу, помещается в процедуру Function (Функция) или Sub (Подпрограмма). Главное отличие между процедурами Function и Sub состоит в том, что процедура Function возвращает результат, процедура Sub – нет.

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

Аргументы

При помощи аргументов процедурам VBA могут быть переданы различные данные. Список аргументов указывается при объявлении процедуры. К примеру, процедура Sub в VBA добавляет заданное целое число (Integer) в каждую ячейку в выделенном диапазоне. Передать процедуре это число можно при помощи аргумента, вот так:

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

Необязательные аргументы

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

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

В таком случае целочисленный аргумент i по умолчанию будет равен 0.

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

Передача аргументов по значению и по ссылке

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

  • ByVal – передача аргумента по значению. Это значит, что процедуре передаётся только значение (то есть, копия аргумента), и, следовательно, любые изменения, сделанные с аргументом внутри процедуры, будут потеряны при выходе из неё.
  • ByRef – передача аргумента по ссылке. То есть процедуре передаётся фактический адрес размещения аргумента в памяти. Любые изменения, сделанные с аргументом внутри процедуры, будут сохранены при выходе из процедуры.

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

В этом случае целочисленный аргумент i передан по значению. После выхода из процедуры Sub все сделанные с i изменения будут утрачены.В этом случае целочисленный аргумент i передан по ссылке. После выхода из процедуры Sub все сделанные с i изменения будут сохранены в переменной, которая была передана процедуре Sub.

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

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

VBA процедура «Function»

Редактор VBA распознаёт процедуру Function, когда встречает группу команд, заключённую между вот такими открывающим и закрывающим операторами:

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

  • Тип данных возвращаемого значения должен быть объявлен в заголовке процедуры Function.
  • Переменная, которая содержит возвращаемое значение, должна быть названа так же, как и процедура Function. Эту переменную не нужно объявлять отдельно, так как она всегда существует как неотъемлемая часть процедуры Function.

Это отлично проиллюстрировано в следующем примере.

Пример VBA процедуры «Function»: Выполняем математическую операцию с 3 числами

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

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

Вызов VBA процедуры «Function»

Если рассмотренная выше простая процедура Function вставлена в модуль в редакторе Visual Basic, то она может быть вызвана из других процедур VBA или использована на рабочем листе в книге Excel.

Вызов VBA процедуры «Function» из другой процедуры

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

Читать еще:  Excel web app

Вызов VBA процедуры «Function» из рабочего листа

VBA процедуру Function можно вызвать из рабочего листа Excel таким же образом, как любую другую встроенную функцию Excel. Следовательно, созданную в предыдущем примере процедуру FunctionSumMinus можно вызвать, введя в ячейку рабочего листа вот такое выражение:

VBA процедура «Sub»

Редактор VBA понимает, что перед ним процедура Sub, когда встречает группу команд, заключённую между вот такими открывающим и закрывающим операторами:

VBA процедура «Sub»: Пример 1. Выравнивание по центру и изменение размера шрифта в выделенном диапазоне ячеек

Рассмотрим пример простой VBA процедуры Sub, задача которой – изменить форматирование выделенного диапазона ячеек. В ячейках устанавливается выравнивание по центру (и по вертикали, и по горизонтали) и размер шрифта изменяется на заданный пользователем:

Данная процедура Sub выполняет действия, но не возвращает результат.

В этом примере также использован необязательный (Optional) аргумент iFontSize. Если аргумент iFontSize не передан процедуре Sub, то его значение по умолчанию принимается равным 10. Однако же, если аргумент iFontSize передается процедуре Sub, то в выделенном диапазоне ячеек будет установлен размер шрифта, заданный пользователем.

VBA процедура «Sub»: Пример 2. Выравнивание по центру и применение полужирного начертания к шрифту в выделенном диапазоне ячеек

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

Вызов процедуры «Sub» в Excel VBA

Вызов VBA процедуры «Sub» из другой процедуры

Чтобы вызвать VBA процедуру Sub из другой VBA процедуры, нужно записать ключевое слово Call, имя процедуры Sub и далее в скобках аргументы процедуры. Это показано в примере ниже:

Если процедура Format_Centered_And_Sized имеет более одного аргумента, то они должны быть разделены запятыми. Вот так:

Вызов VBA процедуры «Sub» из рабочего листа

Процедура Sub не может быть введена непосредственно в ячейку листа Excel, как это может быть сделано с процедурой Function, потому что процедура Sub не возвращает значение. Однако, процедуры Sub, не имеющие аргументов и объявленные как Public (как будет показано далее), будут доступны для пользователей рабочего листа. Таким образом, если рассмотренные выше простые процедуры Sub вставлены в модуль в редакторе Visual Basic, то процедура Format_Centered_And_Bold будет доступна для использования на рабочем листе книги Excel, а процедура Format_Centered_And_Sized – не будет доступна, так как она имеет аргументы.

Вот простой способ запустить (или выполнить) процедуру Sub, доступную из рабочего листа:

  • Нажмите Alt+F8 (нажмите клавишу Alt и, удерживая её нажатой, нажмите клавишу F8).
  • В появившемся списке макросов выберите тот, который хотите запустить.
  • Нажмите Выполнить (Run)

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

  • Нажмите Alt+F8.
  • В появившемся списке макросов выберите тот, которому хотите назначить сочетание клавиш.
  • Нажмите Параметры (Options) и в появившемся диалоговом окне введите сочетание клавиш.
  • Нажмите ОК и закройте диалоговое окно Макрос (Macro).

Внимание: Назначая сочетание клавиш для макроса, убедитесь, что оно не используется, как стандартное в Excel (например, Ctrl+C). Если выбрать уже существующее сочетание клавиш, то оно будет переназначено макросу, и в результате пользователь может запустить выполнение макроса случайно.

Область действия процедуры VBA

В части 2 данного самоучителя обсуждалась тема области действия переменных и констант и роль ключевых слов Public и Private. Эти ключевые слова так же можно использовать применительно к VBA процедурам:

Если перед объявлением процедуры стоит ключевое слово Public, то данная процедура будет доступна для всех модулей в данном проекте VBA.Если перед объявлением процедуры стоит ключевое слово Private, то данная процедура будет доступна только для текущего модуля. Её нельзя будет вызвать, находясь в любом другом модуле или из рабочей книги Excel.

Помните о том, что если перед объявлением VBA процедуры Function или Sub ключевое слово не вставлено, то по умолчанию для процедуры устанавливается свойство Public (то есть она будет доступна везде в данном проекте VBA). В этом состоит отличие от объявления переменных, которые по умолчанию бывают Private.

Ранний выход из VBA процедур «Function» и «Sub»

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

Обратите внимание, что перед тем, как завершить выполнение процедуры FunctionVAT_Amount, в код вставлена встроенная VBA функция MsgBox, которая показывает пользователю всплывающее окно с предупреждением.

Создаем свою первую функцию в Excel

Что такое функция в VBA?

Функция написанная на VBA — это код, который выполняет вычисления и возвращает значение (или массив значений). Создав функцию вы можете использовать ее тремя способами:

  1. В качестве формулы на листе, где она может принимать аргументы и возвращать значения.
  2. Как часть вашей подпрограммы VBA. В процедуре Sub или внутри других функций.
  3. В правилах условного форматирования.
Читать еще:  Как в excel рисовать стрелки

Хотя Excel уже содержит более 450 встроенных функций, но их тоже периодически не хватает. Иногда встроенные функции не могут выполнить то, что вы хотите сделать. Иногда для достижения результата необходимо создать огромную и сложную формулу, которая не понятна окружающим. В этом случае вы можете создать пользовательскую функцию, которую легко читать и использовать.

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

В чем отличие процедуры (Sub) от функции (Function)?

Основное отличие в том, что процедура (sub) используется для выполнения набора команд, и не призвана в отличие от функции (function) возвращать значение (или массив значений).

Для демонстрации приведем пример. Например есть ряд чисел от 1 до 100 и необходимо отделить четные от нечетных.

С помощью процедуры (sub) вы можете, к примеру, пройтись по ячейкам и выделить нечетные с помощью заливки. А функцию можно использовать в соседнем столбце и она вернет ИСТИНА или ЛОЖЬ в зависимости от того четное значение или нет. Т.е. вы не сможете изменить цвет заливки с помощью функции на листе.

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

Давайте создадим простую пользовательскую функцию на VBA и посмотрим как там все работает.

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

Чтобы у вас все заработало, необходимо вставить данный код в модуль книги. Если вы не знаете как это сделать, то начните со статьи Как записать макрос в Excel.

Теперь посмотрим как функция работает, попробуем использовать ее на листе:

Прежде чем разбирать саму функцию отметим 2 приятных момента, которые появились после создания:

  • Она стала доступна, как и любая другая встроенная функция (как создать скрытую функцию, расскажем далее).
  • Когда вы ввели знак «=» и начинаете вводить имя функции, то Excel выводит все совпадения и показывает не только встроенные функции, но и пользовательские.

Разбираем функцию пошагово

Теперь давайте глубоко погрузимся и посмотрим, как эта функция создавалась. Начинается функция со строки

Слово Function говорит о начале функции, далее идет ее название, в нашем случае Цифры.

  • Имя функции не может содержать пробелов. Кроме того, вы не можете назвать функцию, если она сталкивается с именем ссылки на ячейку. Например, вы не можете назвать функцию ABC123, так как это имя также относится к ячейке в листе Excel.
  • Вы не должны указывать на свою функцию то же имя, что и у существующей функции. Если вы это сделаете, Excel будет отдавать предпочтение встроенной функции.
  • Вы можете использовать символ подчеркивания, если хотите разделить слова. Например, Сумма_Прописью является допустимым именем.

После названия в круглых скобках описываются аргументы функции. По аналогии со встроенными функциями Excel. В нашем случае используется единственный аргумент Текст. После названия аргумента мы указали As String, это означает, что наш аргумент — текстовое значение или ссылка на ячейку, содержащее текстовое значение. Если вы не укажете тип данных, VBA рассмотрит его как Variant (что означает, что вы можете использовать любой тип данных, VBA его определит самостоятельно).

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

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

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

Задача функции — пройти по всем символам переменной Текст и сохранить только те, что являются цифрами. Поэтому начнем цикл с 1 и до последнего символа.

Len — функция, которая определяет количество символов.

Основная строка функции — это проверка является ли очередной символ текста цифрой и если да — то сохранение его в переменной result

Для этого нам потребуется функция IsNumeric — она возвращает True если текст — число и False в противном случае.

Функция Mid берет из аргумента Текст i-ый символ (значение 1, указывает что функция Mid берет только 1 символ)/

Функция Next — закрывает цикл For тут все понятно.

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

Последняя строка кода — End Function. Это обязательная строка кода, которая сообщает VBA, что код функции заканчивается здесь.

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

Ссылка на основную публикацию
Adblock
detector