Функция ЕНД и как использовать его, чтобы игнорировать ошибку в формуле ВПР

Posted on: 2020年10月22日 2020年11月12日
Tags: tips

Такие функции, как ВПР, СЧЁТЕСЛИ, СУММЕСЛИ, называются функциями рабочего листа. Как правило, функции, предопределенные в Excel и готовые к использованию на листе, являются функциями рабочего листа. Вы не можете изменить или увидеть код этих функций в VBA.

С другой стороны, определяемые пользователем функции и функции, специфичные для VBA, такие как MsgBox или InputBox, являются функциями VBA.

Все мы знаем, как использовать функции VBA в VBA. Но что, если мы хотим использовать VLOOKUP в VBA. Как мы это делаем? В этой статье мы исследуем именно это.

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

Для доступа к функции рабочего листа мы используем класс Application. Почти все функции рабочего листа перечислены в классе Application.Worksheet Function. А используя оператор точки, вы можете получить к ним доступ.

В любом подпункте напишите Application.Worksheet Function. И начните писать название функции. 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 Dim name, city 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)

Здесь мы используем функцию ВПР, чтобы получить. Теперь, когда вы исправляете функцию и открываете круглые скобки, она покажет вам необходимые аргументы, но не так информативно, как в 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

When you run the above sub, it will return:

Надеюсь, эта статья о том, как использовать функции рабочего листа, такие как VLOOKUP в VBA в Excel, является пояснительной. Дополнительные статьи о формулах VBA и связанных формулах Excel можно найти здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected].

Статьи по теме:

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]: для переворота числа и text мы используем циклы и среднюю функцию в 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, чтобы выделить текущая строка и столбец листа.

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

Как использовать функцию VLOOKUP в VBA

Как это работает?

1.

2.

3.

4.

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

Статьи по теме:

Популярные статьи: