Функция на VBA работен лист | Как да използвам функцията WorksheetFunction във VBA?

Функции на работен лист на Excel VBA

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

Най-доброто нещо за VBA е, като това как използваме формули в работен лист, също VBA също има свои собствени функции. Ако това е най-доброто, то има и красиво нещо, което е „можем да използваме и функции на работния лист във VBA“.

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

Как да използвам функциите на работния лист във VBA?

Можете да изтеглите този шаблон на VBA WorksheetFunction тук - VBA WorksheetFunction Template

В работния лист всички формули започват със знак за равен (=), подобно на VBA кодирането, за да получим достъп до формулите на работния лист, трябва да използваме думата „WorksheetFunction“.

Преди да въведете формула на работен лист, трябва да споменете името на обекта “WorksheetFunction”, след това поставете точка (.), След което ще получите списък с всички налични функции под този обект.

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

# 1 - Прости функции на работния лист на SUM

Добре, за да започнете с функциите на работния лист, приложете простата функция SUM в Excel, за да добавите числа от работния лист.

Да приемем, че имате месечни данни за продажбите и разходите в работния лист, като този по-долу.

В B14 и C14 трябва да стигнем до сумата от горните числа. Следвайте стъпките по-долу, за да започнете процеса на прилагане на функцията “SUM” в Excel VBA.

Стъпка 1: Създайте просто име на макрос на Excel.

Код:

 Sub Worksheet_Function_Example1 () Край Sub

Стъпка 2: Тъй като се нуждаем от резултата в клетка B14, стартирайте кода като Range (“B14”). Стойност =

Код:

 Sub Worksheet_Function_Example1 () Диапазон ("B14"). Стойност = Краен Sub

Стъпка 3: В B14 се нуждаем от стойността като резултат от сумата от числата. Така че, за да получите достъп до функцията SUM от работния лист, стартирайте кода като “WorksheetFunction”.

Код:

Sub Worksheet_Function_Example1 () Диапазон ("B14"). Стойност = WorksheetFunction. Крайна под

Стъпка 4: В момента, в който поставите точка (.), Тя ще започне да показва наличните функции. Така че изберете SUM от това.

Код:

 Sub Worksheet_Function_Example1 () Range ("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 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) Range ("C14"). Value = WorksheetFunction.Sum (Range ("C2: C13")) End Sub

Стъпка 7: Сега стартирайте този код ръчно или с помощта на клавиша F5, за да имате общо в клетките B14 и C14.

Уау, имаме нашите ценности. Едно нещо, което трябва да забележите тук, е, че нямаме формула в работния лист, но току-що получихме резултата от функцията „SUM“ във VBA.

# 2 - Използвайте VLOOKUP като функция на работен лист

Ще видим как да използваме VLOOKUP във VBA. Да приемем, че по-долу са данните, които имате във вашия Excel лист.

В клетка E2 сте създали падащ списък с всички зони.

Въз основа на избора, който сте направили в клетката E2, трябва да извлечем ПИН кода за съответната зона. Но този път чрез VBA VLOOKUP, а не работен лист VLOOKUP. Следвайте стъпките по-долу, за да приложите VLOOKUP.

Стъпка 1: Създайте просто име на макрос в подпроцедурата.

Код:

 Sub Worksheet_Function_Example2 () Край 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.