Создаем свою первую функцию в Excel
Что такое функция в VBA?
Хотя Excel уже содержит более 450 встроенных функций, но их тоже периодически не хватает. Иногда встроенные функции не могут выполнить то, что вы хотите сделать. Иногда для достижения результата необходимо создать огромную и сложную формулу, которая не понятна окружающим. В этом случае вы можете создать пользовательскую функцию, которую легко читать и использовать.
Обратите внимание, что пользовательские функции, созданные с помощью VBA, как правило значительно медленнее, чем встроенные функции. Следовательно, они лучше всего подходят для ситуаций, когда вы не можете получить результат, используя встроенные функции или вычислений не много и снижение производительности не критично.
В чем отличие процедуры (Sub) от функции (Function)?
Основное отличие в том, что процедура (sub) используется для выполнения набора команд, и не призвана в отличие от функции (function) возвращать значение (или массив значений).
Для демонстрации приведем пример. Например есть ряд чисел от 1 до 100 и необходимо отделить четные от нечетных.
С помощью процедуры (sub) вы можете, к примеру, пройтись по ячейкам и выделить нечетные с помощью заливки. А функцию можно использовать в соседнем столбце и она вернет ИСТИНА или ЛОЖЬ в зависимости от того четное значение или нет. Т.е. вы не сможете изменить цвет заливки с помощью функции на листе.
Создание простой пользовательской функции в VBA
Давайте создадим простую пользовательскую функцию на VBA и посмотрим как там все работает.
Ниже представлен код функции, которая из текста оставляет только цифры, отбрасывая буквенные значения.
Чтобы у вас все заработало, необходимо вставить данный код в модуль книги. Если вы не знаете как это сделать, то начните со статьи Как записать макрос в Excel.
Теперь посмотрим как функция работает, попробуем использовать ее на листе:
Прежде чем разбирать саму функцию отметим 2 приятных момента, которые появились после создания:
Разбираем функцию пошагово
Теперь давайте глубоко погрузимся и посмотрим, как эта функция создавалась. Начинается функция со строки
Слово Function говорит о начале функции, далее идет ее название, в нашем случае Цифры.
Последняя часть первой строки As Long задает тип данных, которая возвращает функция. В нашем случае функция будет возвращать целые значения. Это также не обязательно.
Вторая и третья строка функции объявляет дополнительные внутренние переменные, которые мы будем использовать.
Переменную i мы буем использовать для перебора символов. А переменную result для хранения промежуточного результата функции.
Функция Mid берет из аргумента Текст i-ый символ (значение 1, указывает что функция Mid берет только 1 символ)/
Этой строкой мы преобразовываем текстовую переменную result, которая содержит все цифры аргумента Текст, в числовое значение. И говорим какой результат должна вывести наша функция Цифры.
В приведенном выше коде описаны различные части типичной пользовательской функции, созданной в VBA. В следующих статьях мы более подробно разберем эти элементы, а также рассмотрим различные способы выполнения функции VBA в Excel.
Руководство: создание пользовательских функций в Excel
В этом руководстве описан порядок выполнения перечисленных ниже задач.
Предварительные требования
Последняя версия Yeoman и генератора Yeoman для надстроек Office. Выполните в командной строке указанную ниже команду, чтобы установить эти инструменты глобально.
Даже если вы уже установили генератор Yeoman, рекомендуем обновить пакет до последней версии из npm.
Создание проекта пользовательских функций
Чтобы начать, создайте проект кода для разработки надстройки пользовательской функции. Генератор Yeoman для надстроек Office настроит в вашем проекте некоторые готовые пользовательские функции, которые можно попробовать. Если вы уже с помощью краткого руководства по пользовательским функциям создали проект, продолжайте работать с ним и пропустите этот шаг.
Выполните следующую команду, чтобы создать проект надстройки с помощью генератора Yeoman.
При выполнении команды yo office может появиться запрос о политиках сбора данных генератора Yeoman и средств CLI надстройки Office. Используйте предоставленные сведения, чтобы ответить на запросы подходящим образом.
При появлении запроса предоставьте следующую информацию для создания проекта надстройки.
Генератор Yeoman создаст файлы проекта и установит вспомогательные компоненты Node.
Вы можете игнорировать инструкции по дальнейшим действиям, предоставляемые генератором Yeoman после создания проекта надстройки. Пошаговые инструкции этой статьи содержат все сведения, необходимые для завершения этого учебного курса.
Перейдите к корневой папке проекта.
Выполните построение проекта.
Запустите локальный веб-сервер, работающий на Node.js. Вы можете попробовать использовать надстройку пользовательской функции в Excel в Интернете или для Windows.
Чтобы проверить надстройку в Excel для Windows или Mac, выполните следующую команду. Когда вы выполните эту команду, запустится локальный веб-сервер и откроется приложение Excel, в котором будет загружена ваша надстройка.
Чтобы проверить надстройку в Excel в браузере, выполните следующую команду. После выполнения этой команды запустится локальный веб-сервер.
Чтобы использовать надстройку пользовательских функций, откройте новую книгу в Excel в Интернете. В этой книге выполните шаги, описанные ниже, чтобы загрузить неопубликованную надстройку.
В Excel на вкладке Вставка выберите пункт Надстройки.
Выберите пункт Управление моими надстройками, а затем выберите Отправить мою надстройку.
Выберите Обзор. и откройте корневой каталог проекта, созданный генератором Yeoman.
Выберите файл manifest.xml и нажмите Открыть, затем нажмите кнопку Отправить.
Проверка работы готовой пользовательской функции
Выполните запуск функции CONTOSO.ADD с числами 10 и 200 в качестве входных параметров, введя значение =CONTOSO.ADD(10,200) в ячейке и нажав клавишу ВВОД.
Пользовательская функция ADD вычисляет сумму двух чисел, которые вы указываете и возвращает результат 210.
Создание пользовательской функции, которая запрашивает данные из сети Интернет
В проекте starcount найдите файл ./src/functions/functions.js и откройте его в редакторе кода.
В function.js добавьте следующий код.
Выполните указанную ниже команду, чтобы повторно собрать проект.
Чтобы повторно зарегистрировать надстройку в Excel, выполните указанные ниже действия (для Excel в Интернете, для Windows или для Mac). Выполните описанные ниже действия, чтобы новая функция стала доступной.
Закройте Excel, а затем откройте Excel повторно.
В Excel выберите вкладку Вставка, а затем нажмите стрелку вниз, которая находится справа от пункта Мои надстройки. 
В списке доступных надстроек найдите раздел Надстройки разработчика и выберите вашу надстройку starcount, чтобы ее зарегистрировать. 
В Excel выберите вкладку Вставка, а затем выберите Надстройки.
Выберите пункт Управление моими надстройками, а затем выберите Отправить мою надстройку.
Выберите Обзор. и откройте корневой каталог проекта, созданный генератором Yeoman.
Выберите файл manifest.xml и нажмите Открыть, затем нажмите кнопку Отправить.
Теперь давайте оценим, как работает новая функция. В ячейке B1 введите текст =CONTOSO.GETSTARCOUNT(«OfficeDev», «Excel-Custom-Functions») и нажмите клавишу ВВОД. Результат в ячейке B1 — это текущее количество звезд, отданных репозиторию Excel-Custom-Functions Github.
Создание потоковой асинхронной пользовательской функции
Проект starcount уже содержит две следующие функции в файле ./src/functions/functions.js.
Чтобы опробовать функции, введите текст =CONTOSO.CLOCK() в ячейку C1 и нажмите ВВОД. Должна отобразиться текущая дата, которая потоком обновляется каждую секунду. Хотя эти часы являются просто таймером в цикле, однако можно использовать аналогичную идею настройки таймера для более сложных функций, которые выполняют веб-запросы в режиме реального времени.
Дальнейшие действия
Поздравляем! Вы создали новый проект пользовательских функций, попробовали, как работает готовая функция, создали пользовательскую функцию, которая запрашивает данные из Интернета, а также создали пользовательскую функцию, которая осуществляет потоковую передачу данных. Затем вы можете изменить свой проект, чтобы использовать общую среду выполнения, упрощая взаимодействие с панелью задач. Выполните инструкции из следующей статьи.
Создание пользовательских функций в 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 и введите следующий код:
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)
В 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. Так, код будет легче понять, если потребуется внести в него изменения.
Апостроф указывает приложению Excel на то, что следует игнорировать всю строку справа от него, поэтому вы можете добавлять комментарии в отдельных строках или в правой части строк, содержащих код VBA. Советуем начинать длинный блок кода с комментария, в котором объясняется его назначение, а затем использовать встроенные комментарии для документирования отдельных операторов.
Кроме того, рекомендуется присваивать макросам и пользовательским функциям описательные имена. Например, присвойте макросу название MonthLabels вместо Labels, чтобы более точно указать его назначение. Описательные имена макросов и пользовательских функций особенно полезны, если существует множество процедур с похожим назначением.
То, как документировать макрос и пользовательские функции, имеет личный выбор. Важно принятия определенного способа документации и его согласованного использования.
Для использования настраиваемой функции должна быть открыта книга, содержащая модуль, в котором она была создана. Если книга не открыта, вы получите #NAME? при попытке использования функции. Если вы ссылались на функцию в другой книге, ее имя должно предшествовать названию книги, в которой она находится. Например, при создании функции DISCOUNT в книге Personal.xlsb и вызове ее из другой книги необходимо ввести =personal.xlsb!discount(),а не просто =discount().
Чтобы вставить пользовательскую функцию быстрее (и избежать ошибок), ее можно выбрать в диалоговом окне «Вставка функции». Пользовательские функции доступны в категории «Определенные пользователем»:
Чтобы пользовательские функции всегда были доступны, можно хранить их в отдельной книге, а затем сохранять в качестве надстройки. Затем надстройку можно сделать доступной при запуске Excel. Вот как это сделать:
Создав нужные функции, выберите Файл > Сохранить как.
В 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 не всегда могут помочь. Если существующих функций недостаточно, Excel позволяет добавить новые настраиваемые пользовательские функции (UDF). Они делают вашу работу легче.
Мы расскажем, как можно их создать, какие они бывают и как использовать их, чтобы ваша работа стала проще. Узнайте, как записать и использовать пользовательские функции, которые многие называют макросами..
Что такое пользовательская функция в Excel?
На момент написания этой статьи Excel предлагает вам более 450 различных функций. С их помощью вы можете выполнять множество различных операций. Но разработчики Microsoft Excel не могли предвидеть все задачи, которые нам нужно решать. Думаю, что многие из вас встречались с этими проблемами:
Как можно решить эти проблемы?
Хотя первые два варианта кажутся вам знакомыми, третий может вызвать некоторую путаницу. Итак, давайте подробнее рассмотрим настраиваемые функции в Excel и решим, стоит ли их использовать.
Пользовательская функция – это настраиваемый код, который принимает исходные данные, производит вычисление и возвращает желаемый результат.
Исходными данными могут быть числа, текст, даты, логические значения, массивы. Результатом вычислений может быть значение любого типа, с которым работает Excel, или массив таких значений.
Другими словами, пользовательская функция – это своего рода модернизация стандартных функций Excel. Вы можете использовать ее, когда возможностей обычных функций недостаточно. Основное ее назначение – дополнить и расширить возможности Excel, выполнить действия, которые невозможны со стандартными возможностями.
Существует несколько способов создания собственных функций:
Даже если вы сохранили эту огромную формулу в своем архиве, вам нужно ее найти, скопировать и вставить, а затем аккуратно поправить все ссылки на ячейки. Согласитесь, это потребует затрат времени, да и ошибки не исключены.
А на ввод функции вы потратите всего несколько секунд.
Для чего можно использовать?
Вы можете использовать настраиваемую функцию одним из следующих способов:
Для чего нельзя использовать пользовательские функции:
Как создать пользовательскую функцию в VBA?
Прежде всего, необходимо открыть редактор Visual Basic (сокращенно — VBE). Обратите внимание, что он открывается в новом окне. Окно Excel при этом не закрывается.
После открытия VBE вам нужно добавить новый модуль. В него вы будете записывать ваш код. Щелкните правой кнопкой мыши на панели проекта VBA слева и выберите «Insert», затем появившемся справа окне — “Module”.
Справа появится пустое окно модуля, в котором вы и будете создавать свою функцию.
Прежде чем начать, напомним правила, по которым создается функция.
Теперь давайте попробуем создать вашу первую собственную формулу. Для начала мы создаем код, который будет подсчитывать количество слов в диапазоне ячеек.
Для этого в окно модуля вставим этот код:
Я думаю, здесь могут потребоваться некоторые пояснения.
Код функции всегда начинается с пользовательской процедуры Function. В процедуре Function мы делаем описание новой функции.
В начале мы должны записать ее имя: CountWords.
Затем в скобках указываем, какие исходные данные она будет использовать. NumRange As Range означает, что аргументом будет диапазон значений. Сюда нужно передать только один аргумент — диапазон ячеек, в котором будет происходить подсчёт.
As Long указывает, что результат выполнения функции CountWords будет целым числом.
Во второй строке кода мы объявляем переменные.
Оператор Dim объявляет переменные:
rCell — переменная диапазона ячеек, в котором мы будем подсчитывать слова.
lCount — переменная целое число, в которой будет записано число слов.
Цикл For Each… Next предназначен для выполнения вычислений по отношению к каждому элементу из группы элементов (нашего диапазона ячеек). Этот оператор цикла применяется, когда неизвестно количество элементов в группе. Начинаем с первого элемента, затем берем следующий и так повторяем до самого последнего значения. Цикл повторяется столько раз, сколько ячеек имеется во входном диапазоне.
Внутри этого цикла с значением каждой ячейки выполняется операция, которая вычисляет количество слов:
Как видите, это обычная формула Excel, которая использует стандартные средства работы с текстом: LEN, TRIM и REPLACE. Это английские названия знакомых нам русскоязычных ДЛСТР, СЖПРОБЕЛЫ и ЗАМЕНИТЬ. Вместо адреса ячейки рабочего листа используем переменную диапазона rCell. То есть, для каждой ячейки диапазона мы последовательно считаем количество слов в ней.
Подсчитанные числа суммируются и сохраняются в переменной lCount:
Когда цикл будет завершен, значение переменной присваивается функции.
Функция возвращает в ячейку рабочего листа значение этой переменной, то есть общее количество слов.
Именно эта строка кода гарантирует, что функция вернет значение lCount обратно в ячейку, из которой она была вызвана.
Закрываем наш код с помощью «End Function».
Как видите, не очень сложно.
Сохраните вашу работу. Для этого просто нажмите кнопку “Save” на ленте VB редактора.
Вы можете сравнить работу с пользовательской функцией CountWords и подсчет количества слов в диапазоне при помощи формул.
Как использовать пользовательскую функцию в формуле?
Когда вы создали пользовательскую, она становится доступной так же, как и другие стандартные функции Excel. Сейчас мы узнаем, как создавать с ее помощью собственные формулы.
Чтобы использовать ее, у вас есть две возможности.
Первый способ. Нажмите кнопку fx в строке формул. Среди появившихся категорий вы увидите новую группу — Определённые пользователем. И внутри этой категории вы можете увидеть нашу новую пользовательскую функцию CountWords.
Можно посчитать этой же функцией и количество слов в диапазоне. Запишите в ячейку С3:
Мы только что указали функцию и установили диапазон, и вот результат подсчета: 14 слов.
Для сравнения в C1 я записал формулу массива, при помощи которой мы также можем подсчитать количество слов в диапазоне.
Как видите, результаты одинаковы. Только использовать CountWords() гораздо проще и быстрее.
Различные типы пользовательских функций с использованием VBA.
Теперь мы познакомимся с разными типами пользовательских функций в зависимости от используемых ими аргументов и результатов, которые они возвращают.
Без аргументов.
Вы можете создать такую функцию и в VBA.
Ниже приведен код, который запишет в ячейку имя вашего рабочего листа.
Или же можно использовать такой код:
Обратите внимание, что в скобках после имени нет ни одного аргумента. Здесь не требуется никаких аргументов, так как результат, который нужно вернуть, не зависит от каких-либо значений в вашем рабочем файле.
Приведенный выше код определяет результат функции как тип данных String (поскольку желаемый результат — это имя файла, которое является текстом). Если вы не укажете тип данных, то Excel будет определять его самостоятельно.
С одним аргументом.
Создадим простую функцию, которая работает с одним аргументом, то есть с одной ячейкой. Наша задача – извлечь из текстовой строки последнее слово.
Аргумент The_Text — это значение выбранной ячейки. Указываем, что это должно быть текстовое значение (As String).
Оператор StrReverse возвращает текст с обратным порядком следования знаков. Далее InStr определяет позицию первого пробела. При помощи Left получаем все знаки заканчивая первым пробелом. Затем удаляем пробелы при помощи Trim. Вновь меняем порядок следования символов при помощи StrReverse. Получаем последнее слово из текста.
Поскольку эта функция принимает значение ячейки, нам не нужно использовать здесь Application.Volatile. Как только аргумент изменится, функция автоматически обновится.
Использование массива в качестве аргумента.
Многие функции Excel используют массивы значений как аргументы. Вспомните функции СУММ, СУММЕСЛИ, СУММПРОИЗВ.
Мы уже рассмотрели эту ситуацию выше, когда учились создавать пользовательскую функцию для подсчета количества слов в диапазоне ячеек.
Приведённый ниже код создает функцию, которая суммирует все чётные числа в указанном диапазоне ячеек.
Аргумент NumRange указан как Range. Это означает, что функция будет использовать массив исходных данных. Необходимо отметить, что можно использовать также тип переменной Variant. Это выглядит как
Тип Variant обеспечивает «безразмерный» контейнер для хранения данных. Такая переменная может хранить данные любого из допустимых в VBA типов, включая числовые значения, текст, даты и массивы. Более того, одна и та же такая переменная в одной и той же программе в разные моменты может хранить данные различных типов. Excel самостоятельно будет определять, какие данные передаются в функцию.
В коде есть цикл For Each … Next, который берет каждую ячейку и проверяет, есть ли в ней число. Если это не так, то ничего не происходит, и он переходит к следующей ячейке. Если найдено число, он проверяет, четное оно или нет (с помощью функции MOD).
Все чётные числа суммируются в переменной Result.
Когда цикл будет закончен, значение Result присваивается переменной SumEven и передаётся функции.
С несколькими аргументами.
Большинство функций Excel имеет несколько аргументов. Не являются исключением и пользовательские функции. Поэтому так важно уметь создавать собственные функции с несколькими аргументами.
В приведенном ниже коде создается функция, которая выбирает максимальное число в заданном интервале.
Она имеет 3 аргумента: диапазон значений, нижняя граница числового интервала, верхняя граница интервала.
Здесь мы используем три аргумента. Первый из них — rngCells As Range. Это диапазон ячеек, в которых нужно искать максимальное значение. Второй и третий аргумент (MinNum, MaxNum) указаны без объявления типа. Это означает, что по умолчанию к ним будет применён тип данных Variant. В VBA используется 6 различных числовых типов данных. Указывать только один из них — это значит ограничить применение функции. Поэтому более целесообразно, если Excel сам определит тип числовых данных.
Цикл For Each … Next последовательно просматривает все значения в выбранном диапазоне. Числа, которые находятся в интервале от максимального до минимального значения, записываются в специальный массив arrNums. При помощи стандартного оператора MAX в этом массиве находим наибольшее число.
С обязательными и необязательными аргументами.
Чтобы понять, что такое необязательный аргумент, вспомните функцию ВПР (VLOOKUP). Её четвертый аргумент [range_lookup] является необязательным. Если вы не укажете один из обязательных аргументов, получите ошибку. Но если вы пропустите необязательный аргумент, всё будет работать.
Но необязательные аргументы не бесполезны. Они позволяют вам выбирать вариант расчётов.
Например, в функции ВПР, если вы не укажете четвертый аргумент, будет выполнен приблизительный поиск. Если вы укажете его как ЛОЖЬ (или 0), то будет найдено точное совпадение.
Если в вашей пользовательской функции есть хотя бы один обязательный аргумент, то он должен быть записан в начале. Только после него могут идти необязательные.
Чтобы сделать аргумент необязательным, вам просто нужно добавить «Optional» перед ним.
Теперь давайте посмотрим, как создать функцию в VBA с необязательными аргументами.
Этот код извлекает текст из ячейки. Optional CaseText = False означает, что аргумент CaseText необязательный. По умолчанию его значение установлено FALSE.
Если необязательный аргумент CaseText имеет значение TRUE, то возвращается результат в верхнем регистре. Если необязательный аргумент FALSE или опущен, результат остается как есть, без изменения регистра символов.
Думаю, что у вас возник вопрос: «Могут ли в пользовательской функции быть только необязательные аргументы?». Ответ смотрите ниже.
Только с необязательным аргументом.
Насколько мне известно, нет встроенной функции Excel, которая имеет только необязательные аргументы. Здесь я могу ошибаться, но я не могу припомнить ни одной такой.
Но при создании пользовательской такое возможно.
Перед вами функция, которая записывает в ячейку имя пользователя.
Function UserName(Optional Uppercase As Variant)
If IsMissing(Uppercase) Then Uppercase = False
If Uppercase Then UserName = UCase(UserName)
Как видите, здесь есть только один аргумент Uppercase, и он не обязательный.
Если аргумент равен FALSE или опущен, то имя пользователя возвращается без каких-либо изменений. Если же аргумент TRUE, то имя возвращается в символах верхнего регистра (с помощью VBA-оператора Ucase). Обратите внимание на вторую строку кода. Она содержит VBA-функцию IsMissing, которая определяет наличие аргумента. Если аргумент отсутствует, оператор присваивает переменной Uppercase значение FALSE.
Можно предложить и другой вариант этой функции.
В этом случае необязательный аргумент имеет значение по умолчанию FALSE. Если функция будет введена без аргументов, то значение FALSE будет использовано по умолчанию и имя пользователя будет получено без изменения регистра символов. Если будет введено любое значение кроме нуля, то все символы будут преобразованы в верхний регистр.
Возвращаемое значение — массив.
В VBA имеется весьма полезная функция — Array. Она возвращает значение с типом данных Variant, которое представляет собой массив (т.е. несколько значений).
Пользовательские функции, которые возвращают массив, весьма полезны при хранении массивов значений. Например, Months() вернёт массив названий месяцев:
Обратите внимание, что функция выводит данные в строке, по горизонтали.
В Office365 и выше можно вводить как обычную формулу, в более ранних версиях – как формулу массива.
А если необходим вертикальный массив значений?
Мы уже говорили ранее, что созданные нами функции можно использовать в формулах Excel вместе со стандартными.
Используем Months() как аргумент функции ТРАНСП:
Как можно использовать пользовательские функции с массивом данных? Можно применять их для ввода данных в таблицу, как показано на рисунке выше. К примеру, в отчёте о продажах не нужно вручную писать названия месяцев.
Можно получить название месяца по его номеру. Например, в ячейке A1 записан номер месяца. Тогда название месяца можно получить при помощи формулы
Альтернативный вариант этой формулы:
Согласитесь, написанная нами функция делает формулу Excel значительно проще.
Эта статья откроет серию материалов о пользовательских функциях. Если мне удалось убедить вас, что это стоит использовать или вы хотели бы попробовать что-то новое в Excel, следите за обновлениями;)