Как использовать Worksheet функции, как ВПР в VBA Excel?
Такие функции, как ВПР, СЧЁТЕСЛИ, СУММЕСЛИ, называются функциями рабочего листа. Как правило, функции, которые предопределены в Excel и готовы к использованию на листе, являются функциями рабочего листа. Вы не можете изменить или увидеть код этих функций в VBA.
С другой стороны, определяемые пользователем функции и функции, специфичные для VBA, такие как MsgBox или InputBox, являются функциями VBA. Все мы знаем, как использовать функции VBA в VBA. Но что, если мы хотим использовать VLOOKUP в VBA. Как мы это делаем? В этой статье мы исследуем именно это.
Использование функций рабочего листа в VBA
Для доступа к функции рабочего листа мы используем класс Application. Почти все функции рабочего листа перечислены в классе Application.WorksheetFunction. А используя оператор точки, вы можете получить к ним доступ.
В любом подпункте напишите Application.WorksheetFunction. И начните писать название функции. Intellisense VBA покажет названия доступных для использования функций. После того, как вы выбрали имя функции, она запросит переменные, как и любая функция в Excel. Но вам придется передавать переменные в понятном VBA формате. Например, если вы хотите передать диапазон A1: A10, вам нужно будет передать его как объект диапазона, например Range («A1: A10»).
Итак, давайте воспользуемся некоторыми функциями рабочего листа, чтобы лучше понять это.
Как использовать функцию VLOOKUP в VBA
Чтобы продемонстрировать, как можно использовать функцию VLOOKUP в VBA, у меня есть образцы данных. Мне нужно показать имя и город данного идентификатора входа в окне сообщения с помощью VBA. Данные разбросаны в диапазоне A1: K26.
Нажмите ALT + F11, чтобы открыть VBE и вставить модуль. См. Код ниже.
Sub WsFuncitons()
Dim loginID As String
Dim name, city As String
loginID = "AHKJ_1-3357042451"
'Using VLOOKUP function to get name of given id in table
name = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)
'Using VLOOKUP function to get city of given id in table
city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)
MsgBox ("Name: " & name & vbLf & "City: " & city)
End Sub
Когда вы запустите этот код, вы получите такой результат.
Вы можете увидеть, как быстро VBA печатает результат в окне сообщения. Теперь рассмотрим код.
Как это работает?
1.
Dim loginID As String
Тусклое имя, город как строка
Сначала мы объявили две переменные строкового типа для хранения результата, возвращаемого функцией VLOOKUP. Я использовал переменные строкового типа, потому что уверен, что результат, возвращаемый функцией VLOOKUP, будет строковым значением. Если ожидается, что функция вашего рабочего листа будет возвращать число, дату, диапазон и т. Д. Типа значения, используйте этот тип переменной для сохранения результата. Если вы не уверены, какое значение будет возвращено функцией рабочего листа, используйте переменные вариантного типа.
2.
loginID = «AHKJ_1-3357042451»
Затем мы использовали переменную loginID для хранения значения поиска. Здесь мы использовали жестко запрограммированное значение. Вы также можете использовать ссылки. Например.
Вы можете использовать Range («A2»). Value для динамического обновления значения поиска из диапазона A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range («A1: K26»), 2, 0) Здесь мы используем функцию VLOOKUP для получения. Теперь, когда вы исправляете функцию и открываете круглые скобки, она покажет вам необходимые аргументы, но не так информативно, как в Excel. Посмотреть на себя.
Вам нужно помнить, как и какую переменную нужно использовать. Вы всегда можете вернуться к рабочему листу, чтобы увидеть подробности описательной переменной.
Здесь значение поиска — Arg1. Для Arg1 мы используем loginID. Таблица поиска — Arg2. Для Arg2 мы использовали Range («A1: K26»). Обратите внимание, что мы не использовали напрямую A2: K26, как в Excel. Индекс столбца — Arg3.
Для Arg3 мы использовали 2, так как имя находится во втором столбце. Тип поиска — Arg4. Мы использовали 0 как Arg4.
city = Application.WorksheetFunction.VLookup (loginID, Range («A1: K26»), 4, 0)
Аналогично получаем название города.
4.
MsgBox («Name:» & name & vbLf & «City:» & city)
Наконец, мы печатаем имя и город с помощью Messagebox.
Зачем использовать функцию рабочего листа в VBA? Функции рабочего листа обладают мощью огромных вычислений, и было бы неразумно игнорировать мощь функций рабочего листа. Например, если нам нужно стандартное отклонение набора данных, и вы хотите написать для этого весь код, это может занять у вас часы. Но если вы знаете, как использовать функцию рабочего листа STDEV.P в VBA, чтобы получить расчет за один раз.
Sub GetStdDev()
std = Application.WorksheetFunction.StDev_P(Range("A1:K26"))
End Sub
Использование нескольких функций рабочего листа VBA
Допустим, нам нужно использовать сопоставление индекса для получения некоторых значений. Теперь, как бы вы написали формулу на VBA. Я думаю, вы напишете вот что:
Sub IndMtch() Val = Application.WorksheetFunction.Index(result_range, _ Application.WorksheetFunction.Match(lookup_value, _ lookup_range, match_type)) End Sub
Это не неправильно, но это долго. Правильный способ использовать несколько функций — использовать блок With. См. Пример ниже:
Sub IndMtch() With Application.WorksheetFunction Val = .Index(result_range, .Match(lookup_value, lookup_range, match_type)) val2 = .VLookup(arg1, arg2, arg3) val4 = .StDev_P(numbers) End With End Sub
Как видите, я использовал блок With, чтобы сообщить VBA, что я буду использовать свойства и функции Application.WorksheetFunction. Так что мне не нужно определять это везде. Я просто использовал оператор точки для доступа к функциям INDEX, MATCH, VLOOKUP и STDEV.P. Как только мы используем оператор End With, мы не можем получить доступ к функциям без использования полностью определенных имен функций.
Поэтому, если вам нужно использовать несколько функций рабочего листа в VBA, используйте with block.
Не все функции рабочего листа доступны через Application.WorksheetFunction Некоторые функции рабочего листа напрямую доступны для использования в VBA. Вам не нужно использовать объект Application.WorksheetFunction.
Например, такие функции, как Len (), которые используются для получения количества символов в строке, слева, справа, в середине, обрезке, смещении и т. Д. Эти функции можно напрямую использовать в VBA. Вот пример.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) End Sub
Смотрите, здесь мы использовали функцию LEN без использования объекта Application.WorksheetFunction.
Точно так же вы можете использовать другие функции, такие как left, right, mid, char и т. Д.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) Debug.Print (left(strng,2)) Debug.Print (right(strng,1)) Debug.Print (Mid(strng, 3, 2)) End Sub
Когда вы запустите указанную выше подпрограмму, она вернет:
5 He o ll
Так что да, ребята, вот как вы можете использовать функцию Excel в VBA. Надеюсь, я достаточно объяснил, и эта статья вам помогла. Если у вас есть какие-либо вопросы относительно этой статьи или чего-либо еще, связанного с VBA, задавайте их в разделе комментариев ниже. А пока вы можете прочитать о других связанных темах ниже.
Статьи по теме:
link: / excel-macros-and-vba-what-is-csng-function-in-excel-vba [Что такое функция CSng в Excel VBA] | Функция SCng — это функция VBA, которая преобразует любой тип данных в число с плавающей запятой одинарной точности («при условии, что это число»). Я в основном использую функцию CSng для преобразования чисел в текстовом формате в реальные числа.
link: / vba-how-to-get-text-number-in-reverse-through-vba-in-microsoft-excel [Как получить текст и число в обратном порядке через VBA в Microsoft Excel] | * Чтобы перевернуть число и текст мы используем циклы и среднюю функцию в VBA. 1234 будет преобразовано в 4321, «you» будет преобразовано в «uoy». Вот отрывок.
link: / formating-in-vba-format-table-в зависимости от-числовых-форматов-using-vba-in-microsoft-excel [Форматирование данных с помощью пользовательских числовых форматов с помощью VBA в Microsoft Excel] | Чтобы изменить числовой формат определенных столбцов в Excel, используйте этот фрагмент VBA. Он покрывает числовой формат указанного в указанный формат одним щелчком мыши.
link: / tips-using-workheet-change-event-to-run-macro-when-any-change-is-made [Использование события изменения рабочего листа для запуска макроса при любом изменении] | Итак, чтобы запускать ваш макрос всякий раз, когда лист обновляется, мы используем события рабочего листа VBA.
link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Запустить макрос, если какие-либо изменения сделаны на листе в указанном диапазоне] | Чтобы запустить код макроса при изменении значения в указанном диапазоне, используйте этот код VBA. Он обнаруживает любые изменения, сделанные в указанном диапазоне, и запускает событие.
link: / events-in-vba-simplest-vba-code-to-highlight-current-row-and-column-using [Простейший код VBA для выделения текущей строки и столбца с использованием] | Используйте этот небольшой фрагмент VBA, чтобы выделить текущую строку и столбец листа.
Популярные статьи:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-повышения-продуктивность [50 сочетаний клавиш Excel для повышения производительности] | Выполняйте свою задачу быстрее. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.
link: / формулы-и-функции-введение-функции-vlookup [Функция ВПР в Excel] | Это одна из наиболее используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листов.
link: / tips-countif-in-microsoft-excel [COUNTIF в Excel 2016] | Подсчитайте значения с условиями, используя эту удивительную функцию. Вам не нужно фильтровать данные для подсчета определенных значений.
Функция Countif важна для подготовки вашей приборной панели.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel] | Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.