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

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

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

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

Пользовательские функции (как и макросы) записываются на языке программирования Visual Basic для приложений (VBA). Они отличаются от макросов двумя вещами. Во-первых, в них используются процедуры Function, а не Sub. Это значит, что они начинаются с оператора Function, а не Sub, и заканчиваются оператором End Function, а не End Sub. Во-вторых, они выполняют различные вычисления, а не действия. Некоторые операторы (например, предназначенные для выбора и форматирования диапазонов) исключаются из пользовательских функций. Из этой статьи вы узнаете, как создавать и применять пользовательские функции. Для создания функций и макросов используется редактор Visual Basic (VBE), который открывается в отдельном окне.

Предположим, что ваша компания предоставляет скидку в размере 10 % клиентам, заказавшим более 100 единиц товара. Ниже мы объясним, как создать функцию для расчета такой скидки.

В примере ниже показана форма заказа, в которой перечислены товары, их количество и цена, скидка (если она предоставляется) и итоговая стоимость.

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

Чтобы создать пользовательскую функцию DISCOUNT в этой книге, сделайте следующее:

Нажмите клавиши ALT+F11 (или FN+ALT+F11 на Mac), чтобы открыть редактор Visual Basic, а затем щелкните Insert (Вставка) > Module (Модуль). В правой части редактора Visual Basic появится окно нового модуля.

Скопируйте указанный ниже код и вставьте его в новый модуль.

Примечание: Чтобы код было более удобно читать, можно добавлять отступы строк с помощью клавиши TAB. Отступы необязательны и не влияют на выполнение кода. Если добавить отступ, редактор Visual Basic автоматически вставит его и для следующей строки. Чтобы сдвинуть строку на один знак табуляции влево, нажмите SHIFT+TAB.

Теперь вы готовы использовать новую функцию DISCOUNT. Закройте редактор Visual Basic, выделите ячейку G7 и введите следующий код:

=DISCOUNT(D7;E7)

Excel вычислит 10%-ю скидку для 200 единиц по цене 47,50 ₽ и вернет 950,00 ₽.

В первой строке кода VBA функция DISCOUNT(quantity, price) указывает, что функции DISCOUNT требуется два аргумента: quantity (количество) и price (цена). При вызове функции в ячейке листа необходимо указать эти два аргумента. В формуле =DISCOUNT(D7;E7) аргумент quantity имеет значение D7, а аргумент price — значение E7. Если скопировать формулу в ячейки G8:G13, вы получите указанные ниже результаты.

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

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

Оператор If в следующем блоке кода проверяет аргумент quantity и сравнивает количество проданных товаров со значением 100:

Если количество проданных товаров не меньше 100, VBA выполняет следующую инструкцию, которая перемножает значения quantity и price, а затем умножает результат на 0,1:

Discount = quantity * price * 0.1

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

Если значение quantity меньше 100, VBA выполняет следующий оператор:

Наконец, следующий оператор округляет значение, назначенное переменной Discount, до двух дробных разрядов:

Discount = Application.Round(Discount, 2)

Читайте так же:
Способы создания снимков экрана на смартфонах ZTE

В VBA нет функции округления, но она есть в Excel. Чтобы использовать округление в этом операторе, необходимо указать VBA, что метод (функцию) Round следует искать в объекте Application (Excel). Для этого добавьте слово Application перед словом Round. Используйте этот синтаксис каждый раз, когда нужно получить доступ к функции Excel из модуля VBA.

Пользовательские функции должны начинаться с оператора Function и заканчиваться оператором End Function. Помимо названия функции, оператор Function обычно включает один или несколько аргументов. Однако вы можете создать функцию без аргументов. В Excel доступно несколько встроенных функций (например, СЛЧИС и ТДАТА), в которых нет аргументов.

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

Количество ключевых слов VBA, которые можно использовать в пользовательских функциях, меньше числа, используемого в макросах. Настраиваемые функции не могут выполнять другие задачи, кроме возврата значения в формулу на этом или в выражение, используемом в другом макросе или функции VBA. Например, пользовательские функции не могут изменять размер окна, редактировать формулу в ячейке, а также изменять шрифт, цвет или узор текста в ячейке. Если в процедуру функции включить такой код действия, функция возвращает #VALUE! ошибку «#ВЫЧИС!».

Единственное действие, которое может выполнять процедура функции (кроме вычислений), — это отображение диалогового окна. Чтобы получить значение от пользователя, выполняющего функцию, можно использовать в ней оператор InputBox. Кроме того, с помощью оператора MsgBox можно выводить сведения для пользователей. Вы также можете использовать настраиваемые диалоговые окна (UserForms), но эта тема выходит за рамки данной статьи.

Даже простые макросы и пользовательские функции может быть сложно понять. Чтобы сделать эту задачу проще, добавьте комментарии с пояснениями. Для этого нужно ввести перед текстом апостроф. Например, ниже показана функция DISCOUNT с комментариями. Благодаря подобным комментариями и вам, и другим будет впоследствии проще работать с кодом VBA. Так, код будет легче понять, если потребуется внести в него изменения.

Пример функции VBA с примечаниями

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

Кроме того, рекомендуется присваивать макросам и пользовательским функциям описательные имена. Например, присвойте макросу название MonthLabels вместо Labels, чтобы более точно указать его назначение. Описательные имена макросов и пользовательских функций особенно полезны, если существует множество процедур с похожим назначением.

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

Для использования настраиваемой функции должна быть открыта книга, содержащая модуль, в котором она была создана. Если книга не открыта, вы получите #NAME? при попытке использования функции. Если вы ссылались на функцию в другой книге, ее имя должно предшествовать названию книги, в которой она находится. Например, при создании функции DISCOUNT в книге Personal.xlsb и вызове ее из другой книги необходимо ввести =personal.xlsb!discount(),а не просто =discount().

Чтобы вставить пользовательскую функцию быстрее (и избежать ошибок), ее можно выбрать в диалоговом окне «Вставка функции». Пользовательские функции доступны в категории «Определенные пользователем»:

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

Читайте так же:
Способы удаленного управления Android-устройством: 5 бесплатных программ

Создав нужные функции, выберите Файл > Сохранить как.

В Excel 2007 нажмите кнопку Microsoft Office, а затем щелкните Сохранить как.

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

Сохранив книгу, выберите Файл > Параметры Excel.

В Excel 2007 нажмите кнопку Microsoft Office и щелкните Параметры Excel.

В диалоговом окне Параметры Excel выберите категорию Надстройки.

В раскрывающемся списке Управление выберите Надстройки Excel. Затем нажмите кнопку Перейти.

В диалоговом окне Надстройки установите флажок рядом с именем книги, как показано ниже.

Создав нужные функции, выберите Файл > Сохранить как.

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

Сохранив книгу, выберите Сервис > Надстройки Excel.

В диалоговом окне Надстройки нажмите кнопку «Обзор», найдите свою надстройку, нажмите кнопку Открыть, а затем установите флажок рядом с надстройкой в поле Доступные надстройки.

После этого пользовательские функции будут доступны при каждом запуске Excel. Если вы хотите добавить его в библиотеку функций, вернимся в Visual Basic редактора. Если вы заглянуть в Visual Basic редактора Project проводника под заголовком VBAProject, вы увидите модуль с именем файла надстройки. У надстройки будет расширение XLAM.

Дважды щелкните модуль в Project Explorer, чтобы вывести код функций. Чтобы добавить новую функцию, установите точку вставки после оператора End Function, который завершает последнюю функцию в окне кода, и начните ввод. Вы можете создать любое количество функций, и они будут всегда доступны в категории «Определенные пользователем» диалогового окна Вставка функции.

Эта статья основана на главе книги Microsoft Office Excel 2007 Inside Out, написанной Марком Доджем (Mark Dodge) и Крейгом Стинсоном (Craig Stinson). В нее были добавлены сведения, относящиеся к более поздним версиям Excel.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Пользовательские функции в Excel на VBA (+ видео)

Все, кто работает с Excel, сталкивались со встроенными функциями, например ВПР, ЕСЛИ и т.д. Из этих функций в Excel строятся различные формулы позволяющие, посчитать, обработать или принять решение. Эти функции находятся в мастере функций и разделены по группам. Мастер же позволяет упростить ввод аргументов функции. Набор функций в Excel достаточно обширный и для большинства задач можно найти нужную функцию или составить формулу из нескольких вложенных функций. Но что, если для решения задачи требуются особые вычисления!? В этом нам поможет встроенный язык VB, который позволяет написать собственные процедуры и функции обработки данных, при этом функции могут быть добавлены в мастер функций и использоваться как обычные встроенные функции (пользовательские функции).

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

  • Как часть в процедуре VBA;
  • В формулах, создаваемых на листах Excel.

Область видимости функций аналогична области действия переменных т.е. Public, Private, Static. Описание функции начинается с ключевого слова Function и заканчивается End Function.Требования к именам функций такие же, как и к именам переменных в VBA.
Рассмотрим простейший пример функции:

Function Test (a as integer, b as integer) as integer
Test = a*b
end function

Читайте так же:
Как удалить страницу в Одноклассниках навсегда в 2021 году: все способы

Мы создали функцию Test, которая имеет два аргумента a и b целого типа. Функция Test возвращает только целый результат. Наша функция находит произведение a и b. Обратите внимание, что результат мы присваиваем в Test т.е. это завершающая стадия работы функции. Если этого не сделать, то функция не вернет никакого результата. end function – указывает, что вычисления функции закончились.
Эту функцию мы можем использовать в других процедурах и функциях vba как вспомогательную или в формулах листа Excel. Хочу обратить ваше внимание, если Вы пишите функции для использования в формулах, то описание функции должно располагаться в модуле (Module) и начинаться только со слова Function. Описание типа Private сделает функцию невидимой в мастере. Поэтому, если использование функции не планируется в формулах, то Вы можете ограничить ее область видимости: Private Function Test (a as integer, b as integer) as integer .

Остановимся теперь более подробно на создании пользовательских функций, которые будут использоваться в формулах для вычислений.
Мы уже создали функцию Test. Добавьте ее в созданный Module.

Переходим теперь на лист и вводим в ячейки A1 и B1 целые числа, выделяем ячейку C1 и жмем вставка функции. В мастере функций необходимо выбрать категорию «Определенные пользователем» и в списке функций найдите «Test«:

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

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

Определение категории функции

По умолчанию функции находятся в категории «Определенные пользователем«. Каким образом можно пользовательской функции переназначить категорию!?

С помощью команды: Application.MacroOptions Macro:=»Test», Category:=10
где Macro:=»Test» это название нашего макроса, Category:=10 номер категории, куда разместить функцию. Всего категорий в Excel — 15. Ниже приведен список:

коды категорий пользовательских функций в Excel

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

Sub InstallFunc()
Application.MacroOptions Macro:=»Test», Category:=10
End Sub

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

Как Вы заметили, наша функция не имеет никакого описания, для пользователей это будет неудобно, а те, кто впервые увидят эту функцию, вообще не поймут для чего она. Поэтому добавим некоторое описание нашей функции. По аналогии с определением категории, нам необходимо один раз выполнить команду Application.MacroOptions. Расширим наш InstallFunc:

Sub InstallFunc()
Application.MacroOptions Macro:=»Test», Category:=10, _
Description:=»Находит произведение аргументов A и B»
End Sub

Собственно описание находится в Description. Выполните процедуру InstallFunc. Готово. Смотрим результат:

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

На этом пока все. Видео по созданию пользовательской функции и готовый пример Вы можете скачать ниже.

Примеры как создать пользовательскую функцию в Excel

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

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

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

  1. Открыть редактор языка VBA с помощью комбинации клавиш ALT+F11.
  2. В открывшемся окне выбрать пункт Insert и подпункт Module, как показано на рисунке: VBA.
  3. Новый модуль будет создан автоматически, при этом в основной части окна редактора появится окно для ввода кода: Новый модуль.
  4. При необходимости можно изменить название модуля.
  5. В отличие от макросов, код которых должен находиться между операторами Sub и End Sub, пользовательские функции обозначают операторами Function и End Function соответственно. В состав пользовательской функции входят название (произвольное имя, отражающее ее суть), список параметров (аргументов) с объявлением их типов, если они требуются (некоторые могут не принимать аргументов), тип возвращаемого значения, тело функции (код, отражающий логику ее работы), а также оператор End Function. Пример простой пользовательской функции, возвращающей названия дня недели в зависимости от указанного номера, представлен на рисунке ниже: Function и End Function.
  6. После ввода представленного выше кода необходимо нажать комбинацию клавиш Ctrl+S или специальный значок в левом верхнем углу редактора кода для сохранения.
  7. Чтобы воспользоваться созданной функцией, необходимо вернуться к табличному редактору Excel, установить курсор в любую ячейку и ввести название пользовательской функции после символа «=»:
Читайте так же:
Как добавить друзей в Одноклассниках

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

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

  1. Создайте новый макрос (нажмите комбинацию клавиш Alt+F8), в появившемся окне введите произвольное название нового макроса, нажмите кнопку Создать: Создайте новый макрос.
  2. В результате будет создан новый модуль с заготовкой, ограниченной операторами Sub и End Sub. Sub и End Sub.
  3. Введите код, как показано на рисунке ниже, указав требуемое количество переменных (в зависимости от числа аргументов пользовательской функции): Введите код макроса.
  4. В качестве «Macro» должна быть передана текстовая строка с названием пользовательской функции, в качестве «Description» — переменная типа String с текстом описания возвращаемого значения, в качестве «ArgumentDescriptions» — массив переменных типа String с текстами описаний аргументов пользовательской функции.
  5. Для создания описания пользовательской функции достаточно один раз выполнить созданный выше модуль. Теперь при вызове пользовательской функции (или SHIFT+F3) отображается описание возвращаемого результата и переменной: Description.

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

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

Пример 1. Рассчитать сумму отпускных для каждого работника, проработавшего на предприятии не менее 12 месяцев, на основе суммы общей заработной платы и числа выходных дней в году.

Вид исходной таблицы данных:

Пример 1.

Каждому работнику полагается 24 выходных дня с выплатой S=N*24/(365-n), где:

  • N – суммарная зарплата за год;
  • n – число праздничных дней в году.

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

Создадим пользовательскую функцию.

Сохраним функцию и выполним расчет с ее использованием:

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

Otpusknye.

Калькулятор расчета калорий в Excel

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

Вид исходной таблицы данных:

Пример 2.

Для расчета используем формулу Миффлина — Сан Жеора, которую запишем в коде пользовательской функции с учетом пола участника. Код примера:

Проверки корректности введенных данных упущены для упрощения кода. Если пол не определен, функция вернет результат 0 (нуль).

Пример расчета для первого участника:

В результате использования автозаполнения получим следующие результаты:

CaloriesPerDay.

Пользовательская функция для решения квадратных уравнений в Excel

Пример 3. Создать функцию, которая возвращает результаты решения квадратных уравнений для указанных в ячейках коэффициентах a, b и c уравнения типа ax2+bx+c=0.

Вид исходной таблицы:

Пример 3.

Для решения создадим следующую пользовательскую функцию:

создадим следующую пользовательскую функцию.

Найдем корни первого уравнения:

Выполним расчеты для остальных уравнений. Полученные результаты:

SquareEquation.

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

VBA пользовательская функция не работает в Mac Excel 2016

Я написал пользовательскую функцию в Excel 2016 для Mac. Сама функция работает; я вызвал ее в VBA в новой подпрограмме, и она работала нормально. Но когда я вызываю его с листа, он ничего не делает.

Читайте так же:
Отключить уведомления Windows 10

2 ответа

  • Excel VBA не работает на Mac

Я написал некоторый код в Excel 2007 и попытался запустить его на Mac с Excel 2008. Эта процедура связана с кнопкой управления на листе, и здесь происходило аналогичное обсуждение: Excel vba для mac Однако в моем случае это, похоже, не совсем та же проблема. Когда я открываю файл на Mac, Excel.

Я перемещаю excel VBA с PC на Mac, и одна строка имеет функцию оценки, например: Public Function test() test = Evaluate(=2+2) End Function Однако, если я попытаюсь запустить макрос на mac, функция вернет #name. есть ли эквивалентная функция на стороне Mac для оценки?

Оказывается, я помещал код в раздел «Объекты Microsoft Excel», а размещение кода в ThisWorkbook или Sheet1 не позволяет функции работать. Вместо этого вы должны щелкнуть правой кнопкой мыши на Microsoft Excel Objects- > Insert -> Module -> поместить код в новую пустую область.

Это также зависит от местоположения вставленного модуля.

Если модуль, содержащий функцию, вставлен в книгу, в которой вы запускаете формулу, можно вызвать функцию непосредственно из электронной таблицы. Однако, если модуль вставлен в книгу personal makro, то функция должна быть вызвана из электронной таблицы следующим образом =PERSONAL.XLSB!function()

Похожие вопросы:

У меня есть макрос на Excel 2016, который отлично работает для создания PDF. Но этот макрос работает только для Excel 2016 для Windows. Я пробую его на Excel 2016 (последняя версия) для Mac, и он не.

Я видел сообщения о том, как комментировать / раскомментировать блоки кода VBA в Excel, но все они, похоже, связаны с Excel для Windows. Я не могу найти подобный метод комментирования больших блоков.

Я использую mac OS X Sierra с Mac для Ecel 2016 и python 3.6. Я пытаюсь запустить скрипт python из excel, потому что: У меня уже есть excel с большим количеством функциональных возможностей, и я.

Я написал некоторый код в Excel 2007 и попытался запустить его на Mac с Excel 2008. Эта процедура связана с кнопкой управления на листе, и здесь происходило аналогичное обсуждение: Excel vba для mac.

Я перемещаю excel VBA с PC на Mac, и одна строка имеет функцию оценки, например: Public Function test() test = Evaluate(=2+2) End Function Однако, если я попытаюсь запустить макрос на mac, функция.

Я разработал небольшой макрос VBA в Excel, который должен добавлять значения ячеек в строке 15 к значениям ячеек в строке 6 во время изменения рабочей книги (в моем случае ввод числа в строке 15 и.

Мы вставляем pptx-файлы в Excel, затем используем код Excel VBA (как показано ниже) для открытия, затем SaveAs pptx-файл на диск пользователя, а затем программно изменяем содержимое pptx на основе.

Я запускаю макрос VBA в Excel 2016 для Mac. Макрос работает на платформах Windows с Excel 2016 года, а также на платформах Mac с более ранней версией, чем версия 2016 года. Эта проблема появляется.

когда я запускаю VBA на windows excel, это работает. Когда я запускаю тот же самый vba на excel для Mac, он не работает. Затем я нажимаю debug, он выделяет следующую часть макроса Selection.Replace.

Я столкнулся с проблемой, когда мой макрос VBA работает в моем приложении MAC Excel, но не в приложении Windows Excel. Там ошибка, которую я получаю, есть Ошибка времени выполнения объекта ‘ 438’ не.

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector