Функция рабочего листа VBA | Как использовать WorksheetFunction в VBA?

Функции листа Excel VBA

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

Лучшее в VBA - это то, как мы используем формулы на листе, аналогично VBA тоже имеет свои собственные функции. Если это лучший вариант, то в нем также есть прекрасная вещь: «мы также можем использовать функции рабочего листа в VBA».

Да!!! Вы правильно поняли, мы также можем получить доступ к функциям рабочего листа в VBA. Мы можем получить доступ к некоторым функциям рабочего листа во время написания кода и сделать их частью нашего кода.

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

Вы можете скачать этот шаблон VBA WorksheetFunction здесь - VBA WorksheetFunction Template

На листе все формулы начинаются со знака равенства (=), аналогично в кодировке VBA, чтобы получить доступ к формулам рабочего листа, мы должны использовать слово «WorksheetFunction».

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

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

# 1 - Простые функции рабочего листа SUM

Хорошо, чтобы начать с функций рабочего листа, примените простую функцию СУММ в Excel для сложения чисел из рабочего листа.

Предположим, у вас есть ежемесячные данные о продажах и затратах в таблице, как показано ниже.

В B14 и C14 нам нужно вычислить сумму вышеуказанных чисел. Выполните следующие шаги, чтобы начать процесс применения функции «СУММ» в Excel VBA.

Шаг 1. Создайте простое имя макроса Excel.

Код:

 Sub Worksheet_Function_Example1 () End Sub

Шаг 2. Поскольку нам нужен результат в ячейке B14, начните код как Range («B14»). Значение =

Код:

 Sub Worksheet_Function_Example1 () Диапазон ("B14"). Значение = End Sub

Шаг 3: В B14 нам нужно значение как результат суммы чисел. Итак, чтобы получить доступ к функции СУММ из рабочего листа, запустите код как «WorksheetFunction».

Код:

Sub Worksheet_Function_Example1 () Диапазон ("B14"). Значение = WorksheetFunction. Конец подписки

Шаг 4: В тот момент, когда вы поставите точку (.), Начнут отображаться доступные функции. Итак, выберите SUM из этого.

Код:

 Sub Worksheet_Function_Example1 () Диапазон ("B14"). Value = WorksheetFunction.Sum End Sub

Шаг 5: Теперь дайте ссылку на вышеуказанные числа, то есть диапазон («B2: B13»).

Код:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) End Sub

Шаг 6: Аналогично для следующего столбца примените аналогичный код, изменив ссылки на ячейки.

Код:

 Sub Worksheet_Function_Example1 () Диапазон ("B14"). Значение = WorksheetFunction.Sum (Range ("B2: B13")) Диапазон ("C14"). Value = WorksheetFunction.Sum (Range ("C2: C13")) End Sub

Шаг 7: Теперь запустите этот код вручную или с помощью клавиши F5, чтобы получить результат в ячейках B14 и C14.

Вау, у нас есть свои ценности. Здесь нужно отметить одну вещь: у нас нет никакой формулы на листе, но мы только что получили результат функции «СУММ» в VBA.

# 2 - Используйте ВПР как функцию рабочего листа

Мы увидим, как использовать VLOOKUP в VBA. Предположим, что ниже приведены данные, которые у вас есть в вашем листе Excel.

В ячейке E2 вы создали выпадающий список всех зон.

На основе выбора, сделанного вами в ячейке E2, нам нужно получить PIN-код для соответствующей зоны. Но на этот раз через VBA VLOOKUP, а не через VLOOKUP на листе. Выполните следующие шаги, чтобы применить ВПР.

Шаг 1. Создайте простое имя макроса в подпроцедуре.

Код:

 Sub Worksheet_Function_Example2 () End Sub

Step 2: We need the result in the F2 cell. So start the code as Range (“F2”).Value =

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = End Sub

Step 3: To access worksheet function VLOOKUP starts the code as “WorksheetFunction.VLOOKUP”.

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup( End Sub

Step 4: One of the problems here is syntax will not give you any sort of guidance to work with VLOOKUP. You need to be absolutely sure about the syntax you are working on.

The first syntax of VLOOKUP is “Lookup Value”. In this case, our lookup value is E2 cell value, so write the code as Range (“E2”).Value

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value, End Sub

Step 5: Now the second argument is our table array, in this case, our table array range is from A2 to B6. So the code will be Range (“A2:B6”)

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"), End Sub

Step 6: The Third argument will be from which column we need the data from the table array. Here we need the data from the 2nd column, so the argument will be 2.

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"),2, End Sub

Step 7: The final argument is range lookup, we need an exact match so the argument is zero (0).

Code:

 Sub Worksheet_Function_Example2() Range("F2").Value = WorksheetFunction.VLookup(Range("E2").Value, Range("A2:B6"), 2, 0) End Sub

So, we are done with the coding part. Now go to the worksheet and select any of the range.

Now go to your coding module and run the macro Using F5 key or manually to get the pin code of the selected zone.

We cannot go back and run the macro every time, so let’s assign a macro to shapes. Insert one of the shapes in a worksheet.

Add a text value to inserted shape.

Now right click and assign the macro name to this shape.

Click on ok after selecting the macro name.

Now, this shape holds the code of our VLOOKUP formula. So whenever you change the zone name click on the button, it will update the values.

Things to Remember

  • To access worksheet functions we need to write the word “WorksheetFunction” or “Application.WorksheetFunction”
  • We don’t have access to all the functions only a few.
  • We don’t see the actual syntax of worksheet functions, so we need to be absolutely sure of the function we are using.