Функція робочого аркуша VBA | Як використовувати WorksheetFunction у VBA?

Функції робочого аркуша Excel VBA

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

Найкраще у VBA - це те, як ми використовуємо формули на робочому аркуші, так само VBA теж має свої функції. Якщо це найкраще, тоді у нього є і прекрасна річ, яка полягає в тому, що "ми можемо використовувати функції робочого аркуша і в VBA".

Так!!! Ви добре чули, ми також можемо отримати доступ до функцій аркуша у VBA. Ми можемо отримати доступ до деяких функцій робочого аркуша під час написання коду і зробити його частиною нашого коду.

Як використовувати функції робочого аркуша у VBA?

Завантажити цей шаблон VBA WorksheetFunction можна тут - Шаблон VBA WorksheetFunction

На робочому аркуші всі формули починаються зі знака рівності (=), подібно до кодування VBA, щоб отримати доступ до формул робочого аркуша, слід використовувати слово “WorksheetFunction”.

Перш ніж вводити будь-яку формулу робочого аркуша, вам потрібно згадати назву об’єкта “WorksheetFunction”, потім поставити крапку (.), Тоді ви отримаєте список усіх доступних функцій цього об’єкта.

У цій статті ми зосередимось виключно на тому, як використовувати функцію робочого аркуша у кодуванні VBA, що додасть більшої цінності вашим знанням кодування.

# 1 - Прості функції робочого аркуша SUM

Добре, для початку з функцій робочого аркуша застосуйте просту функцію SUM в Excel, щоб додати числа з робочого аркуша.

Припустимо, у вас на аркуші є дані про щомісячні продажі та витрати, як показано нижче.

У B14 і C14 нам потрібно отримати загальну кількість вищевказаних цифр. Дотримуйтесь наведених нижче кроків, щоб розпочати процес застосування функції «SUM» у програмі Excel VBA.

Крок 1: Створіть просте ім'я макросу Excel.

Код:

 Вкладений робочий аркуш_Функція_Приклад1 () Кінець Під

Крок 2: Оскільки нам потрібен результат у комірці B14, почніть код як Range (“B14”). Значення =

Код:

 Додатковий аркуш_Функція_Приклад1 () Діапазон ("B14"). Значення = Кінець

Крок 3: У B14 нам потрібне значення як результат суми чисел. Отже, щоб отримати доступ до функції SUM з робочого аркуша, запустіть код як “WorksheetFunction”.

Код:

Додатковий робочий аркуш_Функція_Приклад1 () Діапазон ("B14"). Значення = WorksheetFunction. Кінець Sub

Крок 4: Як тільки ви поставите крапку (.), Вона почне відображати доступні функції. Тож виберіть із цього SUM.

Код:

 Додатковий робочий аркуш_Функція_Приклад1 () Діапазон ("B14"). Значення = WorksheetFunction.Sum End Sub

Крок 5: Дайте посилання на вищезазначені цифри, тобто діапазон (“B2: B13”).

Код:

 Додатковий аркуш_Функція_Приклад1 () Діапазон ("B14"). Значення = WorksheetFunction.Sum (Діапазон ("B2: B13")) Кінець

Крок 6: Аналогічним чином для наступного стовпця застосуйте подібний код, змінивши посилання на комірку.

Код:

 Додатковий робочий аркуш_Функція_Приклад1 () Діапазон ("B14"). Значення = WorksheetFunction.Sum (Діапазон ("B2: B13")) Діапазон ("C14"). Значення = WorksheetFunction.Sum (Діапазон ("C2: C13")) Кінець підпункту

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

Ого, ми отримали свої цінності. Тут потрібно зауважити одне: у нас на робочому аркуші немає жодної формули, але ми щойно отримали результат функції “SUM” у VBA.

# 2 - Використовуйте VLOOKUP як функцію робочого аркуша

Ми побачимо, як використовувати VLOOKUP у VBA. Припустимо, що нижче є дані, які ви маєте у своєму аркуші Excel.

У клітинці E2 ви створили випадаючий список усіх зон.

На основі вибору, який ви зробили в комірці E2, нам потрібно отримати PIN-код для відповідної зони. Але цього разу через VBA VLOOKUP, а не робочий лист VLOOKUP. Виконайте наведені нижче дії, щоб застосувати VLOOKUP.

Крок 1: Створіть просте ім'я макросу в підпроцедурі.

Код:

 Вкладений робочий аркуш_Функція_Приклад2 () Кінець Під

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.