В этом руководстве мы узнаем о функции Excel VBA 1) Что такое Visual Basic в Excel?

2) Как использовать VBA в Excel?

3) Как создать пользовательскую функцию?

4) Как писать макрос?

Как писать VBAcode Excel предоставляет пользователю большую коллекцию готовых функций, более чем достаточную для удовлетворения среднего пользователя. Можно добавить гораздо больше, установив различные доступные надстройки. Большинство вычислений может быть выполнено с использованием того, что предоставляется, но вскоре вы обнаружите, что желаете, чтобы была функция, которая выполняла определенную работу, и вы не можете найти ничего подходящего в списке. Вам нужен UDF. UDF (функция, определяемая пользователем) — это просто функция, которую вы сами создаете с помощью VBA. UDF часто называют «пользовательскими функциями». UDF может оставаться в модуле кода, прикрепленном к книге, и в этом случае она всегда будет доступна, когда эта книга открыта. В качестве альтернативы вы можете создать свою собственную надстройку, содержащую одну или несколько функций, которые вы можете установить в Excel, как коммерческую надстройку. Доступ к UDF также возможен для модулей кода. Часто UDF создаются разработчиками для работы исключительно в коде процедуры VBA, и пользователь никогда не знает об их существовании. Как и любая функция, UDF может быть настолько простым или сложным, насколько вы хотите. Начнем с простого …​

Функция для вычисления площади прямоугольника Да, я знаю, что вы можете сделать это в уме! Концепция очень проста, поэтому вы можете сосредоточиться на технике. Предположим, вам нужна функция для вычисления площади прямоугольника. Вы просматриваете набор функций Excel, но подходящего нет. Это расчет, который необходимо сделать:

AREA = LENGTH x WIDTH. Откройте новую книгу, а затем откройте редактор Visual Basic (Инструменты> Макрос> Редактор Visual Basic или ALT + F11).

Вам понадобится модуль, в котором вы будете писать свою функцию, поэтому выберите Insert> Module. В пустой модуль введите: Function Area и нажмите ENTER. Visual Basic Editor завершит строку за вас и добавит строку End Function, как если бы вы создавали подпрограмму. Пока это выглядит так …​

Function Area()

End Function

Поместите курсор между скобками после «Площадь». Если вы когда-нибудь задумывались, для чего нужны скобки, вы скоро узнаете! Мы собираемся указать «аргументы», которые будет принимать наша функция (argument — это часть информации, необходимая для выполнения вычислений). Введите Length как double, Width как double и щелкните пустую строку внизу. Обратите внимание, что по мере ввода всплывает поле прокрутки со списком всего, что соответствует тому, что вы печатаете.

img2

Эта функция называется Auto List Members. Если он не отображается, он либо выключен (включите его в меню «Инструменты»> «Параметры»> «Редактор»), либо ранее вы допустили опечатку. Это очень полезная проверка вашего синтаксиса. Найдите нужный элемент и дважды щелкните его, чтобы вставить в код. Вы можете проигнорировать это и просто ввести, если хотите. Ваш код теперь выглядит так …​

Function Area(Length As Double, Width As Double)

End Function

Объявление типа данных аргументов не обязательно, но имеет смысл. Вы могли бы ввести Length, Width и оставить все как есть, но предупреждение Excel о том, какой тип данных следует ожидать, помогает вашему коду работать быстрее и обнаруживать ошибки при вводе. Тип данных double относится к числу (которое может быть очень большим) и допускает дроби. Теперь о самом расчете. В пустой строке сначала нажмите клавишу TAB, чтобы сделать отступ в коде (чтобы его было легче читать), и введите Area = Length * Width. Вот завершенный код …​

Function Area(Length As Double, Width As Double)

Area = Length * Width

End Function

Вы заметите, что еще одна вспомогательная функция редактора Visual Basic всплывает, когда вы набираете текст, Auto Quick Info …​

img31

Здесь это не актуально. Его цель — помочь вам писать функции на VBA, сообщая, какие аргументы требуются. Вы можете сразу же протестировать свою функцию. Перейдите в окно Excel и введите значения длины и ширины в отдельные ячейки. В третьей ячейке введите свою функцию, как если бы она была одной из встроенных. В этом примере ячейка A1 содержит длину (17), а ячейка B1 — ширину (6.5). В C1 я набрал _ = area (A1, B1) _, и новая функция вычислила площадь (110,5) …​

img3

Иногда аргументы функции могут быть необязательными. В этом примере мы могли бы сделать аргумент Width необязательным. Предположим, что прямоугольник является квадратом, длина и ширина которого равны. Чтобы избавить пользователя от необходимости вводить два аргумента, мы можем позволить ему ввести только длину, а функция будет использовать это значение дважды (т.е. умножить длину на длину). Итак, функция знает, когда она может это сделать, мы должны включить оператор IF, чтобы помочь ей принять решение. Измените код так, чтобы он выглядел так …​

Function Area(Length As Double, Optional Width As Variant)

If IsMissing(Width) Then

Area = Length * Length

Else

Area = Length * Width

End If

End Function

Обратите внимание, что тип данных для Width был изменен на Variant, чтобы допускать нулевые значения. Теперь функция позволяет пользователю вводить только один аргумент, например. _ = area (A1) _. Оператор IF в функции проверяет, был ли указан аргумент Width, и вычисляет соответственно …​

img4

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

Здесь, в Великобритании, топливо продается в литрах. Милометр автомобиля (хорошо, значит, это одометр) записывает расстояние в милях. И поскольку я слишком стар и глуп, чтобы что-то менять, я понимаю только MPG (мили на галлон). Теперь, если вы думаете, что все это немного грустно, как насчет этого? Когда я прихожу домой, я открываю Excel и ввожу данные в таблицу, которая рассчитывает для меня MPG и отображает характеристики автомобиля. Расчет представляет собой количество миль, которое автомобиль проехал с момента последней заправки, деленное на количество использованных галлонов топлива …​

МИЛЬ НА ГАЛЛОН = (МИЛИ ЭТО ЗАПОЛНЕНО — МИЛЬ ПОСЛЕДНИХ ЗАПОЛНЕННЫХ МИЛИ) / ГАЛЛОНОВ ТОПЛИВА, но поскольку топливо поступает в литрах, а в галлоне 4,546 литров .. | МИЛЬ НА ГАЛЛОН = (МИЛЬ ЭТО ЗАПОЛНЕНО — МИЛИ ПОСЛЕДНЕЕ ЗАПОЛНЕНО) / ЛИТРОВ ТОПЛИВА x 4,546 Вот как я написал функцию …​

и вот как это выглядит на листе …​

Function MPG(StartMiles As Integer, FinishMiles As Integer, Litres As Single)

MPG = (FinishMiles - StartMiles) / Litres * 4.546

End Function

Не все функции выполняют математические вычисления. Вот тот, который предоставляет информацию …​

img5

Функция, задающая название дня. Меня часто спрашивают, есть ли функция даты, которая дает день недели в виде текста (например, понедельник). Ответ отрицательный, но создать его довольно просто. (Приложение: Я сказал «нет»? Посмотрите примечание ниже, чтобы увидеть функцию, которую я забыл!). В Excel есть функция WEEKDAY, которая возвращает день недели в виде числа от 1 до 7. Вы можете выбрать, какой день равен 1, если вам не нравится значение по умолчанию (воскресенье). В приведенном ниже примере функция возвращает «5», что, как мне известно, означает «четверг».

Но я не хочу видеть число, я хочу увидеть «четверг». Я мог бы изменить расчет, добавив функцию ВПР, которая ссылалась на таблицу, содержащую список чисел и соответствующий список названий дней. Или я мог бы сделать все это самодостаточным с несколькими вложенными операторами IF. Слишком сложно! Ответ — настраиваемая функция …​

img6

Я назвал свою функцию «DayName», и она принимает единственный аргумент, который я называю «InputDate», который (конечно) должен быть датой. Вот как это работает …​

Function DayName(InputDate As Date)

Dim DayNumber As Integer

DayNumber = Weekday(InputDate, vbSunday)

Select Case DayNumber

Case 1

DayName = "Sunday"

Case 2

DayName = "Monday"

Case 3

DayName = "Tuesday"

Case 4

DayName = "Wednesday"

Case 5

DayName = "Thursday"

Case 6

DayName = "Friday"

Case 7

DayName = "Saturday"

End Select

End Function

  • Первая строка функции объявляет переменную, которую я назвал «DayNumber», которая будет целым числом (то есть целым числом).

  • Следующая строка функции присваивает значение этой переменной с помощью функции ДЕНЬ НЕДЕЛИ в Excel. Значение будет числом от 1 до 7.

Хотя по умолчанию 1 = воскресенье, я все равно включил его для ясности.

Наконец, оператор Case * проверяет значение переменной и возвращает соответствующий фрагмент текста.

Вот как это выглядит на листе …​

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

img7

Вы также можете найти функции, перечисленные в мастере функций (иногда называемом инструментом «Вставить функцию»). Используйте мастер, чтобы вставить функцию обычным способом (Вставить> Функция).

Прокрутите список категорий функций, найдите User Defined и выберите его, чтобы увидеть список доступных UDF …​

Вы можете видеть, что пользовательские функции не имеют никакого описания, кроме бесполезного сообщения «Справка недоступна», но вы можете добавить краткое описание …​

img8

img9

Убедитесь, что вы находитесь в книге, содержащей функции. Перейдите в Инструменты> Макрос> Макросы. Вы не увидите здесь свои функции, но Excel знает о них! В поле «Имя макроса» в верхней части диалогового окна введите имя функции, затем нажмите кнопку «Параметры» диалогового окна. Если кнопка неактивна, либо вы неправильно написали имя функции, либо находитесь не в той книге, либо ее не существует! Это открывает другое диалоговое окно, в котором вы можете ввести краткое описание функции. Нажмите OK, чтобы сохранить описание, и (вот что сбивает с толку) нажмите Отмена, чтобы закрыть диалоговое окно Macro. Не забудьте сохранить книгу, содержащую функцию. В следующий раз, когда вы перейдете к мастеру функций, ваш UDF будет иметь описание …​

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

img10

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

img11

Если вы хотите написать определяемые пользователем функции для использования более чем в одной книге, лучший способ — создать надстройку Excel. Узнайте, как это сделать, в учебнике Создание надстройки Excel.

Дополнение Я действительно должен знать лучше! Никогда, никогда, никогда не говори никогда! Сказав вам, что не существует функции, которая дает название дня, я теперь вспомнил о той, которая может. Взгляните на этот пример …​

Функция ТЕКСТ возвращает значение ячейки в виде текста в определенном числовом формате. Итак, в примере я мог бы выбрать = ТЕКСТ (A1, «ddd»), чтобы вернуть «Чт», = ТЕКСТ (A1, «мммм»), чтобы вернуть «сентябрь» и т. Д. В справке Excel есть еще несколько примеров способов использования эта функция.

img12

_ _ Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook .

Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected] __