Funkcia pracovného listu VBA | Ako používať WorksheetFunction vo VBA?

Funkcie pracovného hárka programu Excel VBA

Funkcia pracovného hárka vo VBA sa používa, keď sa musíme odvolávať na konkrétny pracovný hárok, zvyčajne keď vytvoríme modul, kód sa vykoná v aktuálne aktívnom hárku zošita, ale ak chceme kód spustiť v konkrétnom pracovnom hárku, použijeme funkciu pracovného hárka, táto funkcia má rôzne použitie a aplikácie vo VBA.

Najlepšie na VBA je, napríklad to, ako používame vzorce v pracovnom hárku, podobne má aj VBA svoje vlastné funkcie. Ak je to najlepšie, potom má aj krásnu vec, že ​​„môžeme tiež používať funkcie pracovných hárkov vo VBA“.

Áno!!! Počuli ste dobre, máme tiež prístup k funkciám pracovného hárka vo VBA. Počas písania kódu môžeme získať prístup k niektorým funkciám pracovného hárka a vytvoriť z neho súčasť nášho kódu.

Ako používať funkcie pracovného hárka vo VBA?

Túto šablónu VBA WorksheetFunction si môžete stiahnuť tu - Šablóna VBA WorksheetFunction

V pracovnom hárku všetky vzorce začínajú znakom rovnosti (=), podobne ako v prípade kódovania VBA, aby sme sa dostali k vzorcom v pracovnom hárku, mali by sme použiť slovo „WorksheetFunction“.

Predtým, ako zadáte ľubovoľný vzorec pracovného hárka, musíte spomenúť názov objektu „WorksheetFunction“ a potom bodku (.), Potom získate zoznam všetkých dostupných funkcií pod týmto objektom.

V tomto článku sa zameriame výlučne na to, ako používať funkciu listu v kódovaní VBA, čo vašim znalostiam kódovania dodá väčšiu hodnotu.

# 1 - Jednoduché funkcie pracovného listu SUM

Dobre, na začiatok s funkciami hárka použite jednoduchú funkciu SUMA v Exceli a pridajte čísla z hárka.

Predpokladajme, že v pracovnom hárku máte údaje o mesačných tržbách a nákladoch, ako je uvedené nižšie.

V B14 a C14 musíme dospieť k súčtu vyššie uvedených čísel. Podľa nasledujúcich pokynov spustíte proces použitia funkcie „SUM“ v programe Excel VBA.

Krok 1: Vytvorte jednoduchý názov makra programu Excel.

Kód:

 Sub Worksheet_Function_Example1 () End Sub

Krok 2: Pretože potrebujeme výsledok v bunke B14, spustite kód ako Range („B14“)

Kód:

 Sub Worksheet_Function_Example1 () Range ("B14"). Hodnota = End Sub

Krok 3: V B14 potrebujeme hodnotu ako výsledok súčtu čísel. Aby ste teda mali prístup k funkcii SUM z pracovného hárka, spustite kód ako „WorksheetFunction“.

Kód:

Vedľajší pracovný hárok_funkcia_príklad1 () rozsah ("B14"). Hodnota = pracovný hárok. Koniec Sub

Krok 4: V okamihu, keď vložíte bodku (.), Začnú sa zobrazovať dostupné funkcie. Z tohto vyberte SUM.

Kód:

 Sub Worksheet_Function_Example1 () Range ("B14"). Hodnota = WorksheetFunction.Sum End Sub

Krok 5: Teraz uveďte odkaz na vyššie uvedené čísla, tj. Rozsah („B2: B13“).

Kód:

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

Krok 6: Podobne pre ďalší stĺpec použite podobný kód zmenou odkazov na bunky.

Kód:

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

Krok 7: Teraz spustite tento kód manuálne alebo pomocou klávesu F5, aby ste mali celkovo v bunkách B14 a C14.

Páni, dostali sme sa k našim hodnotám. Jedna vec, ktorú si tu musíte všimnúť, je, že v pracovnom hárku nemáme žiadny vzorec, ale práve sme dostali výsledok funkcie „SUM“ vo VBA.

# 2 - Použite VLOOKUP ako funkciu pracovného hárka

Uvidíme, ako použiť VLOOKUP vo VBA. Predpokladajme, že nižšie sú údaje, ktoré máte vo svojom excelovom liste.

V bunke E2 ste vytvorili rozbaľovací zoznam všetkých zón.

Na základe výberu, ktorý ste vykonali v bunke E2, musíme načítať kód PIN pre príslušnú zónu. Tentokrát však cez VBA VLOOKUP, nie cez hárok VLOOKUP. Podľa nasledujúcich pokynov použijete VLOOKUP.

Krok 1: Vytvorte jednoduchý názov makra v podprograme.

Kód:

 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.