Использование функций рабочего листа в макросе - примеры кода VBA

Есть много способов использовать функции в VBA. VBA имеет множество встроенных функций. Вы даже можете создавать свои собственные функции (UDF). Однако вы также можете использовать многие функции Excel в VBA с помощью Application.WorksheetFunction.

Как использовать функции рабочего листа в VBA

Чтобы получить доступ к функции Excel в VBA, добавьте Application.WorksheetFunction перед функцией, которую вы хотите вызвать. В приведенном ниже примере мы вызовем функцию Excel Max:

12 Dim maxvalue до тех пор, покаmaxvalue = Application.WorksheetFunction.Max (Диапазон ("a1"). Значение, Диапазон ("a2"). Значение)

Синтаксис функций такой же, однако вы будете вводить аргументы функции так же, как и любую другую функцию VBA.

Обратите внимание, что синтаксис функции Max появляется при вводе (аналогично функциям VBA):

Рабочий лист

WorksheetFunction - это метод объекта Application. Он позволяет получить доступ ко многим (не всем) стандартным функциям рабочего листа Excel. Как правило, вы не получите доступа к функциям рабочего листа, имеющим соответствующую версию VBA.

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

Application.WorksheetFunction vs. Application

На самом деле есть два способа получить доступ к этим функциям:

Application.WorksheetFunction (как показано выше):

1 maxvalue = Application.WorksheetFunction.Max (Диапазон ("a1"). Значение, Диапазон ("a2"). Значение)

или вы можете опустить WorksheetFunction

1 maxvalue = Application.Max (Диапазон ("a1"). Значение, Диапазон ("a2"). Значение)

К сожалению, исключение WorksheetFunction устранит Intellisense, отображающий синтаксис (см. Изображение выше). Однако у него есть одно большое потенциальное преимущество: Обработка ошибок.

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

Давайте посмотрим на пример, чтобы увидеть разницу:

Рабочий лист Vlookup Обработка ошибок функций

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

Сначала мы будем использовать метод WorksheetFunction. Обратите внимание, как VBA выдает ошибку:

Затем мы опускаем WorksheetFunction. Обратите внимание, как

Далее мы опустим функцию WorksheetFunction. Обратите внимание, что ошибка не выдается, а вместо этого функция «value» содержит значение ошибки из Vlookup.

Список функций рабочего листа VBA

Ниже вы найдете список наиболее распространенных функций рабочего листа VBA.

ФункцияОписание
Логический
А ТАКЖЕПроверяет выполнение всех условий. ИСТИНА / ЛОЖЬ
ЕСЛИЕсли условие выполнено, сделайте что-нибудь, если нет, сделайте что-нибудь еще.
ЕСЛИ ОШИБКАЕсли результатом является ошибка, сделайте что-нибудь еще.
ИЛИПроверяет выполнение каких-либо условий. ИСТИНА / ЛОЖЬ
Поиск и справка
ВЫБИРАТЬВыбирает значение из списка на основе номера позиции.
HLOOKUPНайдите значение в первой строке и верните значение.
ПОКАЗАТЕЛЬВозвращает значение на основе номеров столбцов и строк.
ПОГЛЯДИИщет значения по горизонтали или вертикали.
СООТВЕТСТВИЕИщет значение в списке и возвращает его позицию.
ТРАНСПОРТИРОВКАИзменяет ориентацию диапазона ячеек.
ВПРНайдите значение в первом столбце и верните значение.
Дата и время
ДАТАВозвращает дату из года, месяца и дня.
ДАТА ЗНАЧЕНИЕПреобразует дату, сохраненную в виде текста, в действительную дату
ДЕНЬВозвращает день в виде числа (1-31).
ДНЕЙ360Возвращает дни между двумя датами в году из 360 дней.
EDATEВозвращает дату через n месяцев от начальной даты.
EOMONTHВозвращает последний день месяца через n месяцев.
ЧАСВозвращает час в виде числа (0–23).
МИНУТАВозвращает минуты в виде числа (0-59).
МЕСЯЦВозвращает месяц в виде числа (1–12).
NETWORKDAYSКоличество рабочих дней между 2 датами.
NETWORKDAYS.INTLРабочие дни между 2 датами, индивидуальные выходные.
ТЕПЕРЬВозвращает текущую дату и время.
ВТОРОЙВозвращает вторую в виде числа (0-59).
ВРЕМЯВозвращает время в формате часа, минуты и секунды.
ВРЕМЕННАЯ ЦЕНАПреобразует время, сохраненное в виде текста, в действительное время.
WEEKDAYВозвращает день недели в виде числа (1-7).
НЕДЕЛЯВозвращает номер недели в году (1-52).
РАБОЧИЙ ДЕНЬДата n рабочих дней от даты.
ГОДВозвращает год.
ГОДВозвращает долю года между двумя датами.
Инженерное дело
ПЕРЕРАБАТЫВАТЬПреобразование числа из одной единицы в другую.
Финансовый
FVРассчитывает будущую стоимость.
PVРассчитывает текущую стоимость.
КПЕРРассчитывает общее количество периодов выплат.
ГУПРассчитывает сумму платежа.
ТЕМПРассчитывает процентную ставку.
ЧПСРассчитывает чистую приведенную стоимость.
IRRВнутренняя норма доходности для набора периодических CF.
XIRRВнутренняя норма доходности для набора непериодических CF.
ЦЕНАРассчитывает цену облигации.
ВНУТРЕННИЙПроцентная ставка полностью инвестированной ценной бумаги.
Информация
ISERRПроверить, является ли значение ячейки ошибкой, игнорирует # N / A. ИСТИНА / ЛОЖЬ
ОШИБКАПроверьте, является ли значение ячейки ошибкой. ИСТИНА / ЛОЖЬ
ДАЖЕПроверьте, четное ли значение ячейки. ИСТИНА / ЛОЖЬ
ИСЛОГИЧЕСКИЙПроверить, является ли ячейка логической (ИСТИНА или ЛОЖЬ). ИСТИНА / ЛОЖЬ
ISNAПроверьте, является ли значение ячейки # N / A. ИСТИНА / ЛОЖЬ
ISNONTEXTПроверьте, не является ли ячейка текстом (пустые ячейки не являются текстом). ИСТИНА / ЛОЖЬ
ISNUMBERПроверьте, является ли ячейка числом. ИСТИНА / ЛОЖЬ
ISODDПроверьте, является ли значение ячейки нечетным. ИСТИНА / ЛОЖЬ
ISTEXTПроверьте, является ли ячейка текстом. ИСТИНА / ЛОЖЬ
ТИПВозвращает тип значения в ячейке.
Математика
АБСВычисляет абсолютное значение числа.
АГРЕГАТОпределите и выполните вычисления для базы данных или списка.
ПОТОЛОКОкругляет число до ближайшего указанного кратного числа.
COSВозвращает косинус угла.
ГРАДУСЫПреобразует радианы в градусы.
DSUMСуммирует записи базы данных, соответствующие определенным критериям.
ДАЖЕОкругляет до ближайшего четного целого числа.
EXPВычисляет экспоненциальное значение для заданного числа.
ФАКТВозвращает факториал.
ПОЛОкругляет число до ближайшего указанного кратного числа.
НОДВозвращает наибольший общий делитель.
INTОкругляет число до ближайшего целого.
LCMВозвращает наименьшее общее кратное.
LNВозвращает натуральный логарифм числа.
БРЕВНОВозвращает логарифм числа по указанному основанию.
LOG10Возвращает десятичный логарифм числа.
ОКРУЖЕНИЕОкругляет число до указанного кратного.
СТРАННЫЙОкругляет до ближайшего нечетного целого числа.
ПИЗначение ПИ.
ВЛАСТЬВычисляет число в степени.
ПРОДУКТУмножает массив чисел.
QUOTIENTВозвращает целочисленный результат деления.
РАДИАНЫПреобразует угол в радианы.
СЛУЧАЙНОВычисляет случайное число между двумя числами.
КРУГЛЫЙОкругляет число до указанного количества цифр.
ОКРУГЛИТЬОкругляет число в меньшую сторону (до нуля).
ОКРУГЛЯТЬОкругляет число в большую сторону (от нуля).
ГРЕХВозвращает синус угла.
ПРОМЕЖУТОЧНЫЙ ИТОГВозвращает сводную статистику для серии данных.
СУММСкладывает числа.
СУММЕСЛИСуммирует числа, соответствующие критериям.
СУММЕСЛИМНСуммирует числа, соответствующие нескольким критериям.
СУММПРОИЗВУмножает массивы чисел и суммирует результирующий массив.
TANВозвращает тангенс угла.
Статистика
В СРЕДНЕМСредние числа.
СРЕДНИЙСредние числа, соответствующие критериям.
СРЕДНЕНОМНСредние числа, соответствующие нескольким критериям.
КОРРЕЛВычисляет корреляцию двух серий.
СЧИТАТЬПодсчитывает ячейки, содержащие число.
COUNTAПодсчитайте непустые ячейки.
СЧИТАТЬПУСТОТЫПодсчитывает пустые ячейки.
СЧЁТЕСЛИПодсчитывает ячейки, соответствующие критерию.
СЧЁТЕСЛИМНПодсчитывает ячейки, соответствующие нескольким критериям.
ПРОГНОЗПредскажите будущие значения y по линейной линии тренда.
ЧАСТОТАПодсчитывает значения, попадающие в указанные диапазоны.
РОСТВычисляет значения Y на основе экспоненциального роста.
ПЕРЕСЕЧЕНИЕВычисляет точку пересечения оси Y для оптимальной линии.
БОЛЬШОЙВозвращает k-е наибольшее значение.
ЛИНЕЙНВозвращает статистику по линии тренда.
МАКСИМУМВозвращает наибольшее число.
МЕДИАНАВозвращает среднее число.
MINВозвращает наименьшее число.
РЕЖИМВозвращает наиболее часто встречающееся число.
ПРОЦЕНТИЛЬВозвращает k-й процентиль.
PERCENTILE.INCВозвращает k-й процентиль. Где k включительно.
PERCENTILE.EXCВозвращает k-й процентиль. Где k является исключительным.
КВАРТИЛЬВозвращает указанное значение квартиля.
QUARTILE.INCВозвращает указанное значение квартиля. Включительно.
QUARTILE.EXCВозвращает указанное значение квартиля. Эксклюзив.
КЛАССИФИЦИРОВАТЬРанг числа в серии.
RANK.AVGРанг числа в серии. Средние.
RANK.EQРанг числа в серии. Высший ранг.
СКЛОНВычисляет наклон по линейной регрессии.
НЕБОЛЬШОЙВозвращает k-е наименьшее значение.
СТАНДОТКЛОНВычисляет стандартное отклонение.
СТАНДОТКЛОН.PВычисляет SD для всей популяции.
СТАНДОТКЛОН.SВычисляет SD образца.
STDEVPВычисляет SD для всей популяции
ТРЕНДВычисляет значения Y на основе линии тренда.
Текст
ЧИСТЫЙУдаляет все непечатаемые символы.
ДОЛЛАРПреобразует число в текст в денежном формате.
НАЙТИОпределяет положение текста в ячейке. С учетом регистра.
ЛЕВЫЙОбрезает текст на несколько символов слева.
LENПодсчитывает количество символов в тексте.
MIDИзвлекает текст из середины ячейки.
ПРАВИЛЬНЫЙПреобразует текст в правильный регистр.
ЗАМЕНЯТЬЗаменяет текст в зависимости от его местоположения.
ПОВТОРПовторяет текст несколько раз.
ПРАВИЛЬНООбрезает текст на несколько символов справа.
ПОИСКОпределяет положение текста в ячейке. Без учета регистра.
ЗАМЕНЯТЬНаходит и заменяет текст. Деликатный случай.
ТЕКСТПреобразует значение в текст с определенным числовым форматом.
ОТДЕЛКАУдаляет все лишние пробелы из текста.
wave wave wave wave wave