Как создать функцию в vba в excel
Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.
Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.
Способ 1. Создание макросов в редакторе Visual Basic
Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.
- В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
- В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)
:
К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:
Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:
-
Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос Zamena:
С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на VBA в частности, сложновато будет сообразить какие именно команды и как надо вводить, чтобы макрос автоматически выполнял все действия, которые, например, Вы делаете для создания еженедельного отчета для руководства компании. Поэтому мы переходим ко второму способу создания макросов, а именно.
Способ 2. Запись макросов макрорекордером
Макрорекордер - это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операци, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы:
- Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
- Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
- Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.
Чтобы включить запись необходимо:
- в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
- в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)
Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:
- Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
- Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
- Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .
Запуск и редактирование макросов
Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов
Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:
Кнопка на панели инструментов в Excel 2003 и старше
Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :
Перетащите ее к себе на панель инструментов и затем щелкните по ней правой кнопкой мыши. В контекстом меню можно назначить кнопке макрос, выбрать другой значок и имя:
Кнопка на панели быстрого доступа в Excel 2007 и новее
Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :
Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:
Кнопка на листе
Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :
Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.
Создание пользовательских функций на VBA
Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).
Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:
Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
Синтаксис оператора Function содержит такие части:
Part Описание Public Необязательный элемент. Указывает на то, что процедура Function доступна для всех других процедур во всех модулях. При использовании в модуле, который содержит Option Private, процедура недоступна вне проекта. Private Необязательный элемент. Указывает на то, что процедура Function доступна только для других процедур в том модуле, в котором была объявлена. Friend Необязательный элемент. Используется только в модуле класса. Указывает на то, что процедура Function видима повсюду в проекте, но невидима для контроллера экземпляра объекта. Static Необязательный элемент. Указывает на то, что локальные переменные процедуры Function сохраняются между вызовами. Атрибут Static не влияет на переменные, которые были объявлены вне Function, даже если они используются в процедуре. name Обязательно. Название Function; соответствует стандарту соглашений об именовании переменных. arglist Необязательный элемент. Список переменных, представляющих аргументы, которые передаются в процедуру Function при вызове. В качестве разделителя переменных используется запятая. тип Необязательный элемент. Тип данных значения, возвращаемого процедурой Function ; могут быть Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (в настоящее время не поддерживается), Date, String (за исключением фиксированной длины), Object, Variant или любого пользователя определенного типа. Операторы Необязательный элемент. Любая группа операторов, которая будет выполняться в процедуре Function. выражение Необязательный элемент. Возвращаемое значение Function. Аргумент arglist имеет следующий синтаксис и элементы:
[ Необязательный ] [ ByVal | ByRef ] [ ParamArray ] varname [( ) ] [ Как тип ] [ = defaultvalue ]
Part Описание Необязательное Необязательный элемент. Указывает, что аргумент не является обязательным. При использовании все последующие аргументы в arglist также должны быть необязательными и объявляться с помощью ключевого слова Необязательный . Optional не может использоваться для каких-либо аргументов, если используется ParamArray. ByVal Необязательный элемент. Указывает, что аргумент передается значением. ByRef Необязательный элемент. Указывает, что аргумент передается по ссылке. ByRef является значением по умолчанию в Visual Basic. ParamArray Необязательный элемент. Используется как последний аргумент в arglist, чтобы указать, что последний аргумент является массивом Optional элементов Variant. Ключевое слово ParamArray позволяет предоставлять произвольное число аргументов. Не может использоваться с аргументами ByVal, ByRef или Optional. varname Обязательно. Имя переменной, представляющее аргумент; соответствует стандарту соглашений об именовании переменных. тип Необязательный элемент. Тип данных аргумента, переданного процедуре; могут быть Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (не поддерживается) Дата, Строка (только переменная длина), Объект, Вариант или определенный тип объекта. Если параметр объявлен без ключевого слова Optional, можно также указать тип, определяемый пользователем. defaultvalue Необязательный элемент. Любая константа или константное выражение. Действительно только для параметров Optional. Если типом является Object, явным значением по умолчанию может быть только Nothing. Примечания
Если явно не указано с помощью общедоступных**,** частных или friend, процедуры Функции являются общедоступными по умолчанию.
Если не используется static, между вызовами не сохраняется значение локальных переменных.
Ключевое слово Friend может использоваться только в модулях классов. Однако доступ к процедурам Friend может осуществляться в любом модуле проекта. Процедура Friend не появляется в библиотеке типов родительского класса и не может быть поздней.
Процедуры Function могут быть рекурсивными, то есть они могут вызывать сами себя для выполнения поставленной задачи. Однако рекурсия может стать причиной переполнения стека. Ключевое слово Static обычно не используется с рекурсивными процедурами Function.
Весь выполняемый код должен быть в процедурах. Вы не можете определить процедуру Function внутри другой процедуры Function, Sub или Property .
Заявление Exit Function вызывает немедленный выход из процедуры Function . Выполнение программы продолжается с оператором, следующим за оператором, который вызвал процедуру Function. В любом месте процедуры Function может появится любое количество операторов Exit Function.
Как и Sub, Function является отдельной процедурой, которая может содержать аргументы, выполнять наборы операторов и изменять значения их аргументов. Однако в отличие от Sub, процедуру Function можно использовать справа от выражения, как и при использовании любой встроенной функции, например Sqr, Cos или Chr, если необходимо использовать значение, возвращенное функцией.
Вы вызываете процедуру Function , используя имя функции, а затем список аргументов в скобки, в выражении. Сведения о том, как вызывать процедуры Function, см. в заявлении Call.
Чтобы вернуть значение функции, присвойте значение названию функции. В любом месте процедуры может появится любое количество таких назначений. Если параметру name не присвоено значение, процедура вернет значение по умолчанию: числовая функция вернет 0, строковая функция вернет строку нулевой длины (""), а функция Variant вернет значение Empty. Функция, которая возвращает объектную ссылку, возвращает значение Nothing, если для параметра name не присвоено ни одной объектной ссылки (с помощью Set) в Function.
В следующем примере показано, как назначить возвращаемую ценность функции. В этом случае значение False присваивается названию, чтобы указать, что некоторые значения не были найдены.
Переменные, которые используются в процедурах Function, делятся на две категории: те, которые явно объявлены в процедуре, и те, которые нет.
Переменные, которые явно объявлены в процедуре (с помощью оператора Dim или его эквивалента), всегда являются локальными в процедуре. Переменные, которые используются, но не были явно объявлены в процедуре, также являются локальными, если они не были объявлены на более высоком уровне вне процедуры.
В процедуре может использоваться переменная, которая не была явно объявлена в процедуре, но если какой-либо элемент, который был определен на уровне модуля, имеет такое же название, может возникнуть конфликт. Если ваша процедура ссылается на необъявленную переменную, которая называется так же, как и другая процедура, константа или переменная, система посчитает, что ваша процедура ссылается на это название на уровне модуля. Следует явно объявлять переменные, чтобы избежать таких конфликтов. Используйте заявление Option Explicit для принудительного явного декларирования переменных.
Visual Basic может менять порядок арифметических выражений для повышения внутренней эффективности. Избегайте использования процедуры Function в арифметических выражениях, если функция меняет значение переменных в одном выражении. Дополнительные сведения об операторах арифметики см. в этой информации.
Пример
В этом примере оператор Function используется для объявления названия, аргументов и кода, которые составляют основной текст процедуры Function. В последнем примере используются фиксированные инициализированные аргументы Optional.
Использование ключевых слов ParamArray позволяет функции принимать переменное число аргументов. В следующем определении он передается по значению.
Необязательные аргументы могут иметь значения по умолчанию и типы, отличные от Variant.
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Пользовательская функция — это процедура VBA, которая производит заданные вычисления и возвращает полученный результат. Используется для вставки в ячейки рабочего листа Excel или для вызова из других процедур.Объявление пользовательской функции
Синтаксис функции
Компоненты функции
- Static — необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
- Имя — обязательный компонент, имя пользовательской функции.
- СписокАргументов — необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
- Операторы — необязательный компонент, блок операторов (инструкций).
- Имя = выражение — необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
- Exit Function — необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.
*Один из компонентов Имя = выражение следует считать обязательным, так как если не присвоить функции значения, смысл ее использования теряется.
Видимость функции
Видимость пользовательской функции определяется необязательными ключевыми словами Public и Private, которые могут быть указаны перед оператором Function (или Static, в случае его использования).
Ключевое слово Public указывает на то, что функция будет доступна для вызова из других процедур во всех модулях открытых книг Excel. Функция, объявленная как Public, отображается в диалоговом окне Мастера функций.
Ключевое слово Private указывает на то, что функция будет доступна для вызова из других процедур только в пределах программного модуля, в котором она находится. Функция, объявленная как Private, не отображается в диалоговом окне Мастера функций, но ее можно ввести в ячейку вручную.
Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.
Чтобы пользовательская функция всегда была доступна во всех открытых книгах Excel, сохраните ее в Личной книге макросов без объявления видимости или как Public. Но если вы планируете передать рабочую книгу с пользовательской функцией на другой компьютер, код функции должен быть в программном модуле передаваемой книги.
Пример пользовательской функции
Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем параграфе добавим описание. Называется функция «Деление», объявлена с типом данных Variant, так как ее возвращаемое значение может быть и числом, и текстом. Аргументы функции — Делимое и Делитель — тоже объявлены как Variant, так как в ячейках Excel могут быть числовые значения разных типов, и функция IsNumeric тоже проверяет разные типы данных и требует, чтобы ее аргументы были объявлены как Variant.
Эта статья предназначена для тех, кто желает научиться программировать в VBA Excel с нуля. Вы увидите, как это работает, и убедитесь, что не все так сложно, как кажется с первого взгляда. Свою первую программу вы напишите за 7 простых шагов.Знакомство с редактором VBA
- Создайте новую книгу Excel и сохраните ее как книгу с поддержкой макросов с расширением .xlsm. В старых версиях Excel по 2003 год – как обычную книгу с расширением .xls.
- Нажмите сочетание клавиш «Левая_клавиша_Alt+F11», которое откроет редактор VBA. С правой клавишей Alt такой фокус не пройдет. Также, в редактор VBA можно перейти по ссылке «Visual Basic» из панели инструментов «Разработчик» на ленте быстрого доступа. Если вкладки «Разработчик» на ленте нет, ее следует добавить в настройках параметров Excel.
В левой части редактора VBA расположен проводник проекта, в котором отображены все открытые книги Excel. Верхней строки, как на изображении, у вас скорее всего не будет, так как это – личная книга макросов. Справа расположен модуль, в который записываются процедуры (подпрограммы) с кодом VBA. На изображении открыт модуль листа, мы же далее создадим стандартный программный модуль.
После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.
Первая программа на VBA Excel
Добавляем на стандартный модуль шаблон процедуры – строки ее начала и завершения, между которыми мы и будем писать свою первую программу (процедуру, подпрограмму).
- Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.
В результате откроется окно добавления шаблона процедуры (Sub).
Имя процедуры может быть написано как на латинице, так и на кириллице, может содержать цифры и знак подчеркивания. Оно обязательно должно начинаться с буквы и не содержать пробелы, вместо которых следует использовать знак подчеркивания.
- Вставьте внутрь шаблона процедуры следующую строку: MsgBox "Привет" .
- Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.
Работа с переменными
Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «'» (апостроф).
Функция (Function) отличается от подпрограммы (Sub) тем, что она всегда возвращает какое-либо значение. Если функция размещается в ячейке рабочего листа, то в этой ячейке мы видим значение, которое функция возвратила.
В качестве примера мы создадим функцию, вычисляющую объем цилиндра по формуле: V=π*D²/4*H, где
- V – объем цилиндра;
- π – число Pi;
- D – диаметр основания цилиндра;
- H – высота цилиндра.
Первая функция на VBA Excel
Создайте или откройте файл Excel с расширением .xlsm (Книга Excel с поддержкой макросов). В версиях Excel по 2003 год – с расширением .xls.
Перейдите в редактор VBA, нажав сочетание клавиш «Левая_клавиша_Alt+F11».
Если вы не создавали ранее в этом проекте VBA стандартный программный модуль, нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку после значка Excel на панели инструментов.
Ссылка на модуль появится в проводнике слева. Если модуль создан ранее, дважды кликните по его ссылке в проводнике, и он откроется справа для редактирования.
В открывшемся окне добавления шаблона процедуры выберите «Function», вставьте в поле «Name» название функции «ObyemTsilindra» и нажмите «OK».
Шаблон функции добавится на лист модуля.
Вставьте в скобки через запятую аргументы функции (D, H), а внутрь шаблона следующую строку:
В этой строке, функции (слева) присваивается значение, вычисленное в выражении справа. Выражение повторяет нашу первоначальную формулу вычисления объема цилиндра, где «WorksheetFunction.Pi» – это функция рабочего листа, возвращающая число Pi.
Функция, вычисляющая объем цилиндра по диаметру основания и высоте готова.
Вставка функции в ячейку
Вставить пользовательскую функцию в ячейку можно как вручную: =ObyemTsilindra(Ячейка1;Ячейка2) , так и с помощью мастера функций.
Выберите ячейку, в которую вы хотите вставить формулу, и вызовите матер функций.
В открывшемся окне «Вставка функции» выберите категорию «Определенные пользователем», а в открывшемся списке – функцию «ObyemTsilindra» и нажмите «OK».
В окне «Аргументы функции» в полях аргументов выберите соответствующие ячейки, кликнув по ним на рабочем листе, и нажмите «OK».
Обращаться с пользовательской функцией на рабочем листе можно так же, как с любой другой формулой, например, копировать протягиванием.
Читайте также: