Vba excel вернуть значение функции

Обновлено: 07.07.2024

Создание функций с бесконечным количеством аргументов

В предыдущих примерах мы создавали функции, в которых в качестве аргументов выступало одно значение. Но есть функции, у которых в качестве переменной выступает массив значений. Примерами таких функций встроенных в Excel служат функции СУММ, ВПР, МАКС, СРЗНАЧ и так далее. Давайте теперь создадим свою похожую функцию.

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

В первой строке данной функции можно увидеть, что переменной Диапазон мы присвоили тип Range (Диапазон As Range). Таким образом, в качестве аргумента функции мы можем использовать диапазон ячеек. Применим эту функцию на рабочем листе Excel.

Как видно из рисунка, мы не перечисляли каждое значение, а использовали диапазон ячеек A2:A11.

Теперь немного разберем код. Мы использовали цикл For Each - Next. Его задача пройтись по каждой ячейке нашего диапазона. Предварительно мы объявили еще одну переменную r типа Range. Она будет хранить значение каждой ячейки.

Далее идет проверка значения ячейки и если оно кратно 2, то мы суммируем результат во временную переменную S.

В конце, после цикла, мы присваиваем функции СуммаЧетных значение просуммированных элементов S.

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

Предыдущая функция имеет недостаток - если вы попытаетесь использовать в качестве переменной несмежный диапазон ячеек, то получите ошибку. Иногда требуется более универсальная функция, так как на старте мы можем не знать количество аргументов, которые будут использоваться. Например, всем известная функция СУММ может принимать неограниченное количество аргументов =СУММ( A1 ; B1:B5 ;1;2;3), функция все рано вернет верный результат.

Вы можете создать свою аналогичную ​​функцию в VBA, указав к последнему (или единственному) аргументу ключевое слово ParamArray.

ParamArray - данный модификатор применяется только к последнему аргументу. Аргумент с данным модификатором всегда должен иметь тип данных Variant и всегда является необязательным, ключевое слов Optional не указывается дополнительно

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

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

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

У нас добавился еще один цикл For Each - Next. Т.е. сначала мы проходим по всем аргументам (диапазонам ячеек), а после проходим по всем ячейкам этих диапазонов.

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

Создание функции, возвращающих массив значений

Надеюсь вы знаете, как использовать формулы массивов в Excel. Эти функции вводятся на рабочий лист Excel с помощью сочетания клавиш Ctrl + Shift + Enter. Сейчас мы создадим функцию, которая возвратит массив значений. Например, выведет нам список всех месяцев.

Если мы введем функцию в одну ячейку, то увидим, что результатом будет только Январь. Чтобы понять как работает функция необходимо выделить 12 ячеек по горизонтали, ввести =СписокМесяцев() и нажать сочетание клавиш Ctrl + Shift + Enter.

Если необходимо вывести список месяцев по вертикали, то можно дополнительно использовать функцию =ТРАНСП(СписокМесяцев()).

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

Viual Baic для приложений (VBA), как и другие версии Viual Baic и большинство языков программирования, предлагает возможность разбивать код на небольшие независимые части. Это делает программу менее т

Содержание:

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


Хорошей практикой является разделение кода на (Stockbyte / Stockbyte / Getty Images)

Функции против подпрограмм

Когда мне нужно вернуть значение?

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

Пример

Возвращаемые значения в Excel VBA

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

На данный момент, все процедуры, мы создавали, имеют тип 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 располагает обширной коллекцией готовых встроенных функций, которые можно использовать при написании кода.

Список этих функций можно посмотреть в редакторе 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 в объявлении процедуры можно задать, каким именно способом аргумент передаётся процедуре. Ниже это показано на примерах:

Помните, что аргументы в 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, которая была определена выше.

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

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

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 процедурам:

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

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

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


IsEmpty - это функция, которая используется для проверки, является ли ячейка, на которую ссылаются, пустой или нет. Это очень похоже на функцию ISBLANK в Excel. Функция IsEmpty в Excel VBA также называется информационной функцией в Excel, так как она дает информацию о том, является ли данная ячейка пустой или нет.

Синтаксис IsEmpty в Excel VBA

Синтаксис для функции Isempty следующий:


Мы можем использовать эту функцию, чтобы проверить, является ли одна ячейка пустой или весь диапазон данных пуст или нет. Эта функция возвращает два значения. Одно верно, а другое ложно. Если указанная ячейка пуста, функция возвращает значение как true, а если указанная ячейка не пуста, она возвращает значение как false.

Как использовать функцию VBA IsEmpty в Excel?

Мы узнаем, как использовать функцию VBA IsEmpty с несколькими примерами в Excel.

Вы можете скачать этот шаблон VBA IsEmpty Excel здесь - Шаблон VBA IsEmpty Excel

VBA IsEmpty - Пример № 1

Во-первых, давайте посмотрим значение, возвращаемое этой функцией, как она работает.

Чтобы использовать функцию VBA IsEmpty в Excel, выполните следующие действия:

Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic.


Шаг 2: Напишите следующий код в проекте.

Код:


Во-первых, давайте поймем код, написанный выше, шаг за шагом:

  • Проверка - это имя определенной подфункции.
  • Mycheck - это переменная, которую мы определили как строку, потому что функция Isempty возвращает логическое значение.
  • Mycheck сохраняет значение Isempty, возвращаемое при проверке ячейки A1.
  • Значение, хранящееся в Mycheck Variable, отображается функцией MsgBox.

Шаг 3: Запустите код, нажав кнопку запуска.

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

VBA IsEmpty - Пример № 2

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

Чтобы использовать функцию VBA IsEmpty в Excel, выполните следующие действия:

Шаг 1: На вкладке разработчика нажмите Visual Basic под разделом кода.


Шаг 2: Напишите следующий код в окне кода,

Код:


Давайте еще раз поймем код, написанный выше, еще раз.

Шаг 3: Запустите приведенный выше код, нажав на кнопку «Выполнить».

Мы видим, что результат отображается как ячейка A1 пуста.

Шаг 4: Теперь поместите случайное значение в ячейку A, например, я поместил значение A в ячейку A1.


Шаг 5: Теперь снова запустите код, и мы получим следующий результат.

VBA IsEmpty - Пример № 3

Теперь давайте используем эту функцию, чтобы выяснить, является ли данный диапазон ячеек пустым или нет. Ранее мы использовали эту функцию в одной ячейке. В этом примере наш диапазон данных будет от B1: D7.

Чтобы использовать функцию VBA IsEmpty в Excel, выполните следующие действия:

Шаг 1: На вкладке разработчика нажмите Visual Basic под разделом кода.


Шаг 2: Напишите следующий код в окне кода,

Код:


Давайте разберемся в написанном выше коде шаг за шагом.

Шаг 3: Запустите приведенный выше код, нажав на кнопку «Выполнить».

Мы видим следующий результат, поскольку ячейка A1 пуста.

То, что нужно запомнить

Есть несколько вещей, которые мы должны помнить об Isempty Function в Excel VBA:

  • Isempty похож на функцию Isblank в Excel.
  • IsEmpty - это информационная функция.
  • Функция IsEmpty возвращает логическое значение, т.е. true или false.
  • Функция IsEmpty может использоваться для одной ячейки или для диапазона ячеек.

Рекомендуемые статьи

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

Читайте также: