Funkce listu VBA | Jak používat WorksheetFunction ve VBA?

Funkce listu Excel VBA

Funkce listu ve VBA se používá, když musíme odkazovat na konkrétní list, obvykle když vytváříme modul, kód se spustí v aktuálně aktivním listu sešitu, ale pokud chceme kód spustit v konkrétním listu, použijeme funkci listu, tato funkce má různé použití a aplikace ve VBA.

Nejlepší věc na VBA je, například to, jak používáme vzorce v listu, podobně má i VBA své vlastní funkce. Pokud je to nejlepší, pak to má také krásnou věc, kterou je „můžeme použít funkce listu i ve VBA“.

Ano!!! Slyšeli jste dobře, máme také přístup k funkcím listu ve VBA. Během psaní kódu můžeme přistupovat k některým funkcím listu a učinit jej součástí našeho kódu.

Jak používat funkce listu ve VBA?

Tuto šablonu VBA WorksheetFunction si můžete stáhnout zde - VBA WorksheetFunction Template

V listu začínají všechny vzorce znakem rovná se (=), podobně jako v kódování VBA, abychom měli přístup k vzorcům v listu, měli bychom použít slovo „WorksheetFunction“.

Než zadáte jakýkoli vzorec listu, musíte uvést název objektu „WorksheetFunction“, poté tečku (.) A poté získáte seznam všech dostupných funkcí pod tímto objektem.

V tomto článku se budeme výlučně soustředit na to, jak používat funkci listu v kódování VBA, což vašim znalostem kódování přidá větší hodnotu.

# 1 - Jednoduché funkce pracovního listu SUM

Dobře, pro začátek s funkcemi listu použijte jednoduchou funkci SUMA v Excelu a přidejte čísla z listu.

Předpokládejme, že máte v listu údaje o měsíčních tržbách a nákladech, jako je níže uvedený.

V B14 a C14 musíme dospět k součtu výše uvedených čísel. Podle následujících pokynů zahájíte proces aplikace funkce „SUM“ v aplikaci Excel VBA.

Krok 1: Vytvořte jednoduchý název makra aplikace Excel.

Kód:

 Sub Worksheet_Function_Example1 () End Sub 

Krok 2: Protože potřebujeme výsledek v buňce B14, spusťte kód jako Range („B14“). Hodnota =

Kód:

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

Krok 3: V B14 potřebujeme hodnotu jako výsledek součtu čísel. Chcete-li tedy získat přístup k funkci SUM z listu, spusťte kód jako „WorksheetFunction“.

Kód:

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

Krok 4: V okamžiku, kdy vložíte tečku (.), Se začnou zobrazovat dostupné funkce. Z toho vyberte SUM.

Kód:

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

Krok 5: Nyní uveďte odkaz na výše uvedená čísla, tj. Rozsah („B2: B13“).

Kód:

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

Krok 6: Podobně pro další sloupec použijte podobný kód změnou odkazů na buňky.

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: Nyní spusťte tento kód ručně nebo pomocí klávesy F5, abyste měli celkem v buňkách B14 a C14.

Páni, dostali jsme své hodnoty. Jedna věc, kterou si musíte všimnout, je, že v listu nemáme žádný vzorec, ale právě jsme dostali výsledek funkce „SUM“ ve VBA.

# 2 - Použijte VLOOKUP jako funkci listu

Uvidíme, jak používat VLOOKUP ve VBA. Předpokládejme, že níže jsou data, která máte ve svém listu Excel.

V buňce E2 jste vytvořili rozevírací seznam všech zón.

Na základě výběru, který jste provedli v buňce E2, musíme načíst kód PIN pro příslušnou zónu. Ale tentokrát prostřednictvím VBA VLOOKUP, ne listu VLOOKUP. Chcete-li použít VLOOKUP, postupujte podle následujících kroků.

Krok 1: Vytvořte jednoduchý název makra v dílčí proceduře.

Kód:

 Sub Worksheet_Function_Example2 () End Sub 

Krok 2: Potřebujeme výsledek v buňce F2. Takže začněte kód jako Range („F2“). Hodnota =

Kód:

 Sub Worksheet_Function_Example2 () Range ("F2"). Hodnota = End Sub 

Krok 3: Pro přístup k funkci listu VLOOKUP spustí kód jako „WorksheetFunction.VLOOKUP“.

Kód:

 Sub Worksheet_Function_Example2 () Range ("F2"). Hodnota = WorksheetFunction.Vlookup (End Sub 

Krok 4: Jedním z problémů je syntaxe, která vám neposkytne žádné pokyny pro práci s VLOOKUP. Musíte si být naprosto jisti syntaxí, na které pracujete.

První syntaxe VLOOKUP je „Hodnota vyhledávání“. V tomto případě je naší vyhledávací hodnotou hodnota buňky E2, takže kód zapište jako Range („E2“)

Kód:

 Sub Worksheet_Function_Example2 () Range ("F2"). Value = WorksheetFunction.Vlookup (Range ("E2"). Hodnota, End Sub 

Krok 5: Nyní je druhým argumentem naše pole tabulky, v tomto případě je náš rozsah pole tabulky od A2 do B6. Kód tedy bude Range („A2: B6“)

Kód:

 Sub Worksheet_Function_Example2 () Range ("F2"). Value = WorksheetFunction.Vlookup (Range ("E2"). Value, Range ("A2: B6"), End Sub 

Krok 6: Třetí argument bude, ze kterého sloupce potřebujeme data z pole tabulky. Zde potřebujeme data z druhého sloupce, takže argument bude 2.

Kód:

 Sub Worksheet_Function_Example2 () Range ("F2"). Value = WorksheetFunction.Vlookup (Range ("E2"). Value, Range ("A2: B6"), 2, End Sub 

Krok 7: Posledním argumentem je vyhledávání rozsahu, potřebujeme přesnou shodu, takže argument je nula (0).

Kód:

 Sub Worksheet_Function_Example2 () Range ("F2"). Value = WorksheetFunction.VLookup (Range ("E2"). Value, Range ("A2: B6"), 2, 0) End Sub 

Takže jsme skončili s kódovací částí. Nyní přejděte na list a vyberte některý z rozsahu.

Nyní přejděte do svého kódovacího modulu a spusťte makro pomocí klávesy F5 nebo ručně, abyste získali kód PIN vybrané zóny.

Nemůžeme se vrátit a spustit makro pokaždé, tak pojďme přiřadit makro tvarům. Vložte jeden z obrazců do listu.

Přidejte textovou hodnotu do vloženého tvaru.

Nyní klikněte pravým tlačítkem a přiřaďte tomuto tvaru název makra.

Po výběru názvu makra klikněte na ok.

Nyní tento tvar obsahuje kód našeho vzorce VLOOKUP. Takže kdykoli změníte název zóny, klikněte na tlačítko, aktualizuje se hodnoty.

Věci k zapamatování

  • Pro přístup k funkcím listu musíme napsat slovo „WorksheetFunction“ nebo „Application.WorksheetFunction“
  • Nemáme přístup ke všem funkcím jen k několika.
  • Skutečnou syntaxi funkcí listu nevidíme, takže si musíme být naprosto jistí, jakou funkci používáme.