VBA-regnearksfunktion | Hvordan bruges WorksheetFunction i VBA?

Excel VBA-regnearksfunktioner

Arbejdsarkfunktion i VBA bruges, når vi skal henvise til et specifikt regneark, normalt når vi opretter et modul, udfører koden i det aktuelt aktive ark i projektmappen, men hvis vi vil udføre koden i det specifikke regneark, bruger vi regnearkfunktionen, denne funktion har forskellige anvendelser og applikationer i VBA.

Det bedste ved VBA er, ligesom hvordan vi bruger formler i regnearket, ligesom VBA også har sine egne funktioner. Hvis dette er det bedste, så har det også en smuk ting, som er "vi kan også bruge regnearkfunktioner i VBA".

Ja!!! Du hørte det rigtigt, vi kan også få adgang til regnearksfunktioner i VBA. Vi kan få adgang til nogle af regnearkfunktionerne, mens vi skriver koden og gør den til en del af vores kode.

Hvordan bruges regnearkfunktioner i VBA?

Du kan downloade denne VBA WorksheetFunction-skabelon her - VBA WorksheetFunction Template

I regnearket starter alle formler med lige (=) tegn, ligesom i VBA-kodning for at få adgang til regnearkformler, skal vi bruge ordet "WorksheetFunction".

Før du indtaster en regnearkformel, skal du nævne "WorksheetFunction" -objektnavnet og derefter sætte en prik (.), Så får du en liste over alle de tilgængelige funktioner under dette objekt.

I denne artikel vil vi udelukkende koncentrere os om, hvordan du bruger regnearksfunktion i VBA-kodning, hvilket vil tilføje mere værdi til din kodningskendskab.

# 1 - Simple SUM-regnearksfunktioner

Ok, for at starte med regnearksfunktioner skal du anvende den enkle SUM-funktion i excel for at tilføje tal fra regnearket.

Antag, at du har månedlige salgs- og omkostningsdata i regnearket som nedenstående.

I B14 og C14 skal vi nå frem til summen af ​​ovenstående tal. Følg nedenstående trin for at starte processen med at anvende “SUM” -funktionen i Excel VBA.

Trin 1: Opret et simpelt Excel-makronavn.

Kode:

 Under regneark_Funktion_eksempel1 () Afslut sub 

Trin 2: Da vi har brug for resultatet i celle B14, start koden som Range ("B14"). Værdi =

Kode:

 Under regneark_Funktion_eksempel1 () Område ("B14"). Værdi = Afslut sub 

Trin 3: I B14 har vi brug for værdien som resultat af summen af ​​tallene. Så for at få adgang til SUM-funktionen fra regnearket, start koden som "WorksheetFunction".

Kode:

Under regneark_Funktion_eksempel1 () Område ("B14"). Værdi = Arbejdsarkfunktion. Afslut sub

Trin 4: I det øjeblik du sætter en prik (.), Begynder den at vise de tilgængelige funktioner. Så vælg SUM fra dette.

Kode:

 Under regneark_Funktion_eksempel1 () Område ("B14"). Værdi = ArbejdsarkFunktion.Sum slut sub 

Trin 5: Giv nu henvisningen til ovenstående tal, dvs. rækkevidde (“B2: B13”).

Kode:

 Under Arbejdsark_Funktion_ Eksempel1 () Område ("B14"). Værdi = Arbejdsark Funktion.Sum (Område ("B2: B13")) Afslut Under 

Trin 6: Anvend tilsvarende kode for næste kolonne ved at ændre cellereferencer.

Kode:

 Under regneark_Funktion_eksempel1 () Område ("B14"). Værdi = ArbejdsarkFunktion.Sum (Område ("B2: B13")) Område ("C14"). Værdi = ArbejdsarkFunktion.Sum (Område ("C2: C13")) Afslut sub 

Step 7: Now run this code manually or using the F5 key to have a total in B14 & C14 cells.

Wow, we got our values. One thing you need to notice here is we don’t have any formula in the worksheet but we just got the result of the “SUM” function in VBA.

#2 – Use VLOOKUP as a Worksheet Function

We will see how to use VLOOKUP in VBA. Assume below is the data you have in your excel sheet.

In E2 cell you had created a drop-down list of all the zones.

Based on the selection you made in the E2 cell we need to fetch the Pin Code for the respective zone. But this time through VBA VLOOKUP, not worksheet VLOOKUP. Follow the below steps to apply VLOOKUP.

Step 1: Create a simple macro name in the Sub Procedure.

Code:

 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.