Большинство функций рабочего листа Microsoft Excel можно использовать в коде Visual Basic (список этих функций: List of Worksheet Functions Available to Visual Basic)
Часть функций Excel в Visual Basic просто не нужны. Например, вместо функции Concatenate (СЦЕПИТЬ) в коде Visual Basic для объединения текстов используется оператор &.
Вызов функции рабочего листа из Visual Basic
В Visual Basic функции рабочего листа (worksheet ) Microsoft Excel доступны через объект WorksheetFunction.
Например, здесь используется функция Min для определения наименьшего значения в диапазоне ячеек:
Здесь сначала объявляется переменная myRange как объект типа Range, и затем ей назначается диапазон ячеек A1:C10 на листе Sheet1. Переменной answer присваивается результат применения функции Min к myRange . Полученное значение answer отображается в окне сообщения.
Диапазон ячеек необходимо объявить как объект Range!
Например, в формуле в ячейке таблицы Excel
=MATCH(9,A1:A10,0)
а в коде Visual Basic —
Функции Visual Basic (без префикса WorksheetFunction) могут иметь такое же имя, как и функция Excel, но работать иначе. Например, Application.WorksheetFunction.Log и Log возвращают разные значения.
Вставка функции рабочего листа в ячейку
Чтобы вставить функцию в ячейку, нужно присвоить ее свойству Formula соответствующего объекта Range.
результат функции RAND (она возвращает случайное число) присваивается свойству Formula диапазона ячеек A1:B3 на листе Sheet1в активной книге Excel.
источник (Using Microsoft Excel Worksheet Functions in Visual Basic [Excel 2003 VBA Language Reference] Office 2003)
Создание пользовательской функции в VBA Excel, ее синтаксис и компоненты. Описание пользовательской функции и ее аргументов. Метод Application.MacroOptions.
Пользовательская функция — это процедура VBA, которая производит заданные вычисления и возвращает полученный результат. Используется для вставки в ячейки рабочего листа Excel или для вызова из других процедур.
Объявление пользовательской функции
Синтаксис функции
Компоненты функции
- Static — необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
- Имя — обязательный компонент, имя пользовательской функции.
- СписокАргументов — необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
- Операторы — необязательный компонент, блок операторов (инструкций).
- Имя = выражение — необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
- Exit Function — необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.
*Один из компонентов Имя = выражение следует считать обязательным, так как если не присвоить функции значения, смысл ее использования теряется.
Видимость функции
Видимость пользовательской функции определяется необязательными ключевыми словами Public и Private, которые могут быть указаны перед оператором Function (или Static, в случае его использования).
Ключевое слово Public указывает на то, что функция будет доступна для вызова из других процедур во всех модулях открытых книг Excel. Функция, объявленная как Public, отображается в диалоговом окне Мастера функций.
Ключевое слово Private указывает на то, что функция будет доступна для вызова из других процедур только в пределах программного модуля, в котором она находится. Функция, объявленная как Private, не отображается в диалоговом окне Мастера функций, но ее можно ввести в ячейку вручную.
Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.
Чтобы пользовательская функция всегда была доступна во всех открытых книгах Excel, сохраните ее в Личной книге макросов без объявления видимости или как Public. Но если вы планируете передать рабочую книгу с пользовательской функцией на другой компьютер, код функции должен быть в программном модуле передаваемой книги.
Пример пользовательской функции
Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем параграфе добавим описание. Называется функция «Деление», объявлена с типом данных Variant, так как ее возвращаемое значение может быть и числом, и текстом. Аргументы функции — Делимое и Делитель — тоже объявлены как Variant, так как в ячейках Excel могут быть числовые значения разных типов, и функция IsNumeric тоже проверяет разные типы данных и требует, чтобы ее аргументы были объявлены как Variant.
Эта функция выполняет деление значений двух ячеек рабочего листа Excel. Перед делением проверяются два блока условий:
- Если делимое или делитель не являются числом, функция возвращает значение: «Ошибка: Делимое и Делитель должны быть числами!», и производится принудительный выход из функции оператором Exit Function.
- Если делитель равен нулю, функция возвращает значение: «Ошибка: деление на ноль!», и производится принудительный выход из функции оператором Exit Function.
Если проверяемые условия не выполняются (возвращают значение False) производится деление чисел и функция возвращает частное (результат деления).
Вы можете скопировать к себе в стандартный модуль эту функцию и она станет доступна в разделе «Определенные пользователем» Мастера функций. Попробуйте вставить функцию «Деление» в ячейку рабочего листа с помощью Мастера и поэкспериментируйте с ней.
Практического смысла функция «Деление» не имеет, но она хорошо демонстрирует как объявляются, создаются и работают пользовательские функции в VBA Excel. А еще она поможет продемонстрировать, как добавлять к функциям и аргументам описания. С полноценной пользовательской функцией вы можете ознакомиться здесь.
Добавление описания функции
В списке функций, выводимом Мастером, невозможно добавить или отредактировать их описание. Список макросов позволяет добавлять процедурам описание, но в нем нет функций. Проблема решается следующим образом:
- Запустите Мастер функций, посмотрите, как отображается имя нужной функции и закройте его.
- Откройте список макросов и в поле «Имя макроса» впишите имя пользовательской функции.
- Нажмите кнопку «Параметры» и в открывшемся окне добавьте или отредактируйте описание.
- Нажмите кнопку «OK», затем в окне списка макросов — «Отмена». Описание готово!
Добавление описания на примере функции «Деление»:
Добавление описания пользовательской функции
Описание функции «Деление» в диалоговом окне Мастера функций «Аргументы функции»:
Описание пользовательской функции в окне «Аргументы функции»
С помощью окна «Список макросов» можно добавить описание самой функции, а ее аргументам нельзя. Но это можно сделать, используя метод Application.MacroOptions.
Метод Application.MacroOptions
Метод Application.MacroOptions позволяет добавить пользовательской функции описание, назначить сочетание клавиш, указать категорию, добавить описания аргументов и добавить или изменить другие параметры. Давайте рассмотрим возможности этого метода, используемые чаще всего.
Пример кода с методом Application.MacroOptions:
- ИмяПодпрограммы — любое уникальное имя, подходящее для наименования процедур.
- ИмяФункции — имя функции, параметры которой добавляются или изменяются.
- Описание функции — описание функции, которое добавляется или изменяется.
- Название категории — название категории в которую будет помещена функция. Если параметр Category отсутствует, пользовательская функция будет записана в раздел по умолчанию — «Определенные пользователем». Если указанное Название категории соответствует одному из названий стандартного списка, функция будет записана в него. Если такого Названия категории нет в списке, будет создан новый раздел с этим названием и функция будет помещена в него.
- "Описание 1", "Описание 2", "Описание 3", . — описания аргументов в том порядке, как они расположены в объявлении пользовательской функции.
Эта подпрограмма запускается один раз, после чего ее можно удалить или использовать как шаблон для корректировки параметров других пользовательских функций.
Сейчас с помощью метода Application.MacroOptions попробуем изменить описание пользовательской функции «Деление» и добавить описания аргументов.
После однократного запуска этой подпрограммы получаем следующий результат:
Новое описание пользовательской функции и ее второго аргумента
Также возможно использовать Excel функции в коде VBA, что мы увидим в этом уроке.
После введения WorksheetFunction , появится список функций:
Выберите функцию, которая вас интересует, например COUNTBLANK :
В следующем макросе, количество пустых ячеек в диапазоне " A1: D8 " хранятся в переменной, а затем отображаются в диалоговом окне: