Přestože VBA nabízí slušný sortiment vestavěných funkcí, nemusíte vždy najít přesně to, co potřebujete. Většinu funkcí listu Excelu můžete naštěstí použít i ve svých postupech VBA. Jediné funkce listu, které nemůžete použít, jsou ty, které mají ekvivalentní funkci VBA. Nemůžete například použít funkci RAND aplikace Excel (která generuje náhodné číslo), protože VBA má ekvivalentní funkci: Rnd.
VBA zpřístupňuje funkce listu aplikace Excel prostřednictvím objektu WorksheetFunction, který je obsažen v objektu Application. Zde je příklad toho, jak můžete použít funkci SUM aplikace Excel v příkazu VBA:
Celkem = Application.WorksheetFunction.SUM(Rozsah(“A1:A12”))
Z výrazu můžete vynechat část Application nebo WorksheetFunction. V obou případech VBA zjistí, co děláte. Jinými slovy, všechny tyto tři výrazy fungují úplně stejně:
Celkem = Application.WorksheetFunction.SUM(Rozsah(“A1:A12”))
Celkem = WorksheetFunction.SUM(Rozsah(“A1:A12”))
Celkem = Aplikace.SOUČET(Rozsah(“A1:A12”))
Moje osobní preference je použít část WorksheetFunction jen proto, aby bylo dokonale jasné, že kód používá funkci Excel.
Příklady funkcí pracovního listu
Zde zjistíte, jak používat funkce listu ve výrazech VBA.
Nalezení maximální hodnoty v rozsahu
Zde je příklad, který ukazuje, jak používat funkci MAX listu aplikace Excel v proceduře VBA. Tento postup zobrazí maximální hodnotu ve sloupci A aktivního listu:
Použití funkce listu v kódu VBA.
Sub ShowMax()
Dim TheMax As Double
TheMax = WorksheetFunction.MAX(Rozsah(“A:A”))
MsgBox TheMax
End Sub
K získání nejmenší hodnoty v rozsahu můžete použít funkci MIN. A jak můžete očekávat, podobným způsobem můžete používat i další funkce listu. Můžete například použít funkci LARGE k určení k -té největší hodnoty v rozsahu. Demonstruje to následující výraz:
Druhá nejvyšší = Funkce listu.LARGE(Rozsah(“A:A”),2)
Všimněte si, že funkce LARGE používá dva argumenty. Druhý argument představuje k- tou část — v tomto případě 2 (druhá největší hodnota).
Výpočet splátky hypotéky
Následující příklad používá funkci listu PMT k výpočtu splátky hypotéky. K uložení dat předávaných funkci Pmt jako argumentů se používají tři proměnné. Okno se zprávou zobrazí vypočítanou platbu.
Sub PmtCalc()
Dim IntRate As Double
Dim LoanAmt As Double
Tlumené období stejně dlouhé
IntRate = 0,0625 / 12
Období = 30 * 12
Půjčka = 150 000
MsgBox WorksheetFunction.PMT(IntRate, Periods, -LoanAmt)
End Sub
Jak ukazuje následující příkaz, hodnoty můžete také vložit přímo jako argumenty funkce:
MsgBox WorksheetFunction.PMT(0,0625 /12, 360, -150000)
Použití proměnných k uložení parametrů však usnadňuje čtení a úpravy kódu, pokud je to nutné.
Použití vyhledávací funkce
Následující příklad používá funkce InputBox a MsgBox jazyka VBA plus funkci VLOOKUP aplikace Excel. Vyzve k zadání čísla dílu a poté získá cenu z vyhledávací tabulky. Níže je rozsah A1:B13 pojmenován Ceník.
Řada s názvem Ceník obsahuje ceny dílů.
Sub GetPrice()
Dim PartNum jako variantu
Ztlumit cenu jako dvojnásobnou
PartNum = InputBox(“Zadejte číslo součásti”)
Tabulky („Ceny“). Aktivovat
Cena = WorksheetFunction.VLOOKUP(Číslo dílu, Rozsah(“Ceník”), 2, False)
MsgBox PartNum & „costs“ & Price
End Sub
Postup GetPrice funguje takto:
-
Funkce InputBox VBA žádá uživatele o číslo dílu.
-
Číslo dílu, které uživatel zadá, je přiřazeno proměnné PartNum.
-
Další příkaz aktivuje list Ceny, pro případ, že to ještě není aktivní list.
-
Kód používá funkci VLOOKUP k nalezení čísla dílu v tabulce.
-
Všimněte si, že argumenty, které používáte v tomto příkazu, jsou stejné jako argumenty, které byste použili s funkcí ve vzorci listu. Tento příkaz přiřadí výsledek funkce do proměnné Cena.
-
Kód zobrazí cenu dílu prostřednictvím funkce MsgBox.
Tento postup nemá žádné ošetření chyb a selže, pokud zadáte neexistující číslo dílu. (Vyzkoušejte to.) Pokud by se jednalo o skutečnou aplikaci, která se používá ve skutečném podnikání, budete chtít přidat některá prohlášení, která se vypořádají s chybami elegantněji.
Zadávání funkcí listu
Dialogové okno Funkce vložení aplikace Excel nelze použít k vložení funkce listu do modulu VBA. Místo toho zadejte takové funkce staromódním způsobem: ručně. Nicméně, můžete použít dialogové okno funkce Vložit identifikovat funkci, kterou chcete použít a zjistit o svých argumentů.
Můžete také využít možnosti automatického seznamu členů VBE, která zobrazí rozevírací seznam všech funkcí listu. Stačí zadat Application.WorksheetFunction a za ním tečku. Poté uvidíte seznam funkcí, které můžete použít. Pokud tato funkce nefunguje, zvolte příkaz Nástroje → Možnosti VBE, klepněte na kartu Editor a zaškrtněte položku Automaticky seznam členů.
Získání seznamu funkcí listu, které můžete použít v kódu VBA.
Více o používání funkcí listu
Nováčci ve VBA si často pletou vestavěné funkce VBA a funkce sešitu Excelu. Je dobré si zapamatovat pravidlo, že VBA se nesnaží znovu objevit kolo. VBA z větší části neduplikuje funkce listu Excelu.
Pro většinu funkcí listu, které nejsou dostupné jako metody objektu WorksheetFunction, můžete použít ekvivalentní vestavěný operátor nebo funkci VBA. Například funkce listu MOD není k dispozici v objektu WorksheetFunction, protože VBA má ekvivalent: svůj vestavěný operátor Mod.
Sečteno a podtrženo? Pokud potřebujete použít funkci, nejprve zjistěte, zda má VBA něco, co vyhovuje vašim potřebám. Pokud ne, podívejte se na funkce listu. Pokud vše ostatní selže, možná budete moci napsat vlastní funkci pomocí VBA.