Есть много способов использовать функции в 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 | Извлекает текст из середины ячейки. |
ПРАВИЛЬНЫЙ | Преобразует текст в правильный регистр. |
ЗАМЕНЯТЬ | Заменяет текст в зависимости от его местоположения. |
ПОВТОР | Повторяет текст несколько раз. |
ПРАВИЛЬНО | Обрезает текст на несколько символов справа. |
ПОИСК | Определяет положение текста в ячейке. Без учета регистра. |
ЗАМЕНЯТЬ | Находит и заменяет текст. Деликатный случай. |
ТЕКСТ | Преобразует значение в текст с определенным числовым форматом. |
ОТДЕЛКА | Удаляет все лишние пробелы из текста. |