VBA-regnearkfunksjon | Hvordan bruke regnearkfunksjon i VBA?

Excel VBA-regnearkfunksjoner

Regnearkfunksjon i VBA brukes når vi må referere til et bestemt regneark, normalt når vi oppretter en modul, kjøres koden i det nåværende aktive arket i arbeidsboken, men hvis vi ønsker å utføre koden i det spesifikke regnearket, bruker vi regnearkfunksjonen, denne funksjonen har forskjellige bruksområder og applikasjoner i VBA.

Det beste med VBA er, som hvordan vi bruker formler i regnearket, og VBA har også sine egne funksjoner. Hvis dette er det beste, har det også en vakker ting som er "vi kan også bruke regnearkfunksjoner i VBA".

Ja!!! Du hørte det riktig, vi har også tilgang til regnearkfunksjoner i VBA. Vi har tilgang til noen av regnearkfunksjonene mens vi skriver koden og gjør den til en del av koden vår.

Hvordan bruke regnearkfunksjoner i VBA?

Du kan laste ned denne VBA WorksheetFunction-malen her - VBA WorksheetFunction Mal

I regnearket begynner alle formlene med likhetstegn (=), på samme måte i VBA-koding for å få tilgang til regnearkformler, bør vi bruke ordet “WorksheetFunction”.

Før du går inn i en regnearkformel, må du nevne "WorksheetFunction" -objektnavnet og deretter sette en prikk (.), Så får du en liste over alle tilgjengelige funksjoner under dette objektet.

I denne artikkelen vil vi utelukkende konsentrere oss om hvordan du bruker regnearkfunksjon i VBA-koding, noe som vil gi mer verdi til kodingskunnskapen din.

# 1 - Enkle SUM-regnearkfunksjoner

Ok, for å starte med regnearkfunksjoner, bruk den enkle SUM-funksjonen i Excel for å legge til tall fra regnearket.

Anta at du har månedlige salgs- og kostnadsdata i regnearket som nedenfor.

I B14 og C14 må vi komme til summen av tallene ovenfor. Følg trinnene nedenfor for å starte prosessen med å bruke “SUM” -funksjonen i Excel VBA.

Trinn 1: Lag et enkelt Excel-makronavn.

Kode:

 Underarbeidsark_Funksjon_eksempel1 () Avslut sub

Trinn 2: Siden vi trenger resultatet i celle B14, start koden som Range ("B14"). Verdi =

Kode:

 Under regneark_Funksjon_eksempel1 () Område ("B14"). Verdi = slutt sub

Trinn 3: I B14 trenger vi verdien som resultat av summen av tallene. Så for å få tilgang til SUM-funksjonen fra regnearket, starter du koden som “WorksheetFunction”.

Kode:

Under Arbeidsark_Funksjon_ Eksempel1 () Område ("B14"). Verdi = ArbeidsarkFunksjon. Slutt Sub

Trinn 4: I det øyeblikket du setter en prikk (.), Begynner den å vise tilgjengelige funksjoner. Så velg SUM fra dette.

Kode:

 Underarbeidsark_Funksjon_eksempel1 () Område ("B14"). Verdi = ArbeidsarkFunksjon. Sluttsub

Trinn 5: Gi nå referansen til de ovennevnte tallene, dvs. Område (“B2: B13”).

Kode:

 Underarbeidsark_Funksjon_eksempel1 () Område ("B14"). Verdi = ArbeidsarkFunksjon.Sum (Område ("B2: B13")) Avslutte under

Trinn 6: På samme måte for neste kolonne, bruk den samme koden ved å endre cellereferansene.

Kode:

 Underarbeidsark_Funksjon_eksempel1 () Område ("B14"). Verdi = ArbeidsarkFunksjon.Sum (Område ("B2: B13")) Område ("C14"). Verdi = ArbeidsarkFunksjon.Sum (Område ("C2: C13")) End Sub

Trinn 7: Kjør nå denne koden manuelt eller bruk F5-tasten for å ha totalt B14 & C14 celler.

Wow, vi har verdiene våre. En ting du må legge merke til her er at vi ikke har noen formel i regnearket, men vi har nettopp fått resultatet av “SUM” -funksjonen i VBA.

# 2 - Bruk VLOOKUP som en regnearkfunksjon

Vi får se hvordan du bruker VLOOKUP i VBA. Anta nedenfor er dataene du har i excel-arket ditt.

I E2-cellen hadde du opprettet en rullegardinliste over alle sonene.

Basert på valget du har gjort i E2-cellen, må vi hente PIN-koden for den respektive sonen. Men denne gangen gjennom VBA VLOOKUP, ikke regneark VLOOKUP. Følg trinnene nedenfor for å bruke VLOOKUP.

Trinn 1: Opprett et enkelt makronavn i underprosedyren.

Kode:

 Underarbeidsark_Funksjon_eksempel2 () Avslut 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.