Tento tutoriál vás naučí vytvářet a používat funkce s parametry i bez parametrů ve VBA
VBA obsahuje velké množství vestavěných funkcí, které můžete používat, ale můžete také psát vlastní. Když píšete kód ve VBA, můžete jej zapsat do dílčí procedury nebo procedury funkce. Procedura funkce dokáže vrátit hodnotu do vašeho kódu. To je velmi užitečné, pokud chcete, aby VBA provedl úkol a vrátil výsledek. Funkce VBA lze také volat zevnitř Excelu, stejně jako vestavěné funkce Excelu v Excelu.
Vytvoření funkce bez argumentů
Chcete -li vytvořit funkci, musíte ji definovat pojmenováním funkce. Funkci pak lze definovat jako datový typ udávající typ dat, která má funkce vrátit.
Možná budete chtít vytvořit funkci, která při každém volání vrátí statickou hodnotu - trochu jako konstanta.
| 123 | Funkce GetValue () jako celé čísloGetValue = 50Koncová funkce |
Pokud byste funkci spustili, funkce by vždy vrátila hodnotu 50.
Můžete také vytvořit funkce, které odkazují na objekty ve VBA, ale k vrácení hodnoty z funkce je třeba použít Nastavit klíčové slovo.
| 123 | Funkce GetRange () jako rozsahNastavit GetRange = rozsah ("A1: G4")Koncová funkce |
Pokud byste v kódu VBA použili výše uvedenou funkci, funkce by vždy vrátila rozsah buněk A1 až G4 v jakémkoli listu, ve kterém pracujete.
Volání funkce z dílčí procedury
Jakmile vytvoříte funkci, můžete ji volat odkudkoli v kódu pomocí funkce Sub Procedura k volání funkce.
Hodnota 50 bude vždy vrácena.
Funkci GetRange můžete také zavolat z dílčí procedury.
Ve výše uvedeném příkladu je funkce GetRange volána dílčí procedurou pro zvýraznění buněk v objektu rozsahu.
Vytváření funkcí
Jediný argument
Ke své funkci můžete také přiřadit parametr nebo parametry. Tyto parametry lze označit jako argumenty.
| 123 | Funkce ConvertKilosToPounds (dblKilo jako Double) jako DoubleConvertKiloToPounds = dblKilo*2.2Koncová funkce |
Potom můžeme zavolat výše uvedenou funkci z dílčí procedury, abychom zjistili, kolik liber je konkrétní množství kilo.
Funkci lze v případě potřeby volat z více procedur v rámci kódu VBA. To je velmi užitečné v tom, že vám to zabrání v psaní stejného kódu znovu a znovu. Umožňuje také rozdělit dlouhé procedury na malé spravovatelné funkce.
Ve výše uvedeném příkladu máme 2 postupy - každý z nich používá funkci k výpočtu hodnoty libry kilogramů, které jim byly předány v dblKilo Argument funkce.
Více argumentů
Můžete vytvořit funkci s více argumenty a předat hodnoty funkci pomocí dílčí procedury.
| 123 | Funkce CalculateDayDiff (Datum1 jako Datum, Datum2 jako Datum) jako dvojnásobekCalculateDayDiff = Date2-Date1Koncová funkce |
Potom můžeme zavolat funkci a vypočítat počet dní mezi 2 daty.
Volitelné argumenty
Můžete také předat volitelné argumenty do funkce. Jinými slovy, někdy můžete argument potřebovat a někdy ne - podle toho, s jakým kódem funkci používáte.
| 123456 | Funkce CalculateDayDiff (Datum1 jako Datum, Volitelné Datum2 jako Datum) jako dvojnásobek'zkontrolujte druhé datum a pokud ne, udělejte Datum2 rovno dnešnímu datu.Pokud Date2 = 0, pak Date2 = Datum'vypočítat rozdílCalculateDayDiff = Date2-Date1Koncová funkce |
Výchozí hodnota argumentu
Při vytváření funkce můžete také nastavit výchozí hodnotu Volitelných argumentů, takže pokud uživatel argument vynechá, použije se místo toho hodnota, kterou jste zadali jako výchozí.
| 1234 | Funkce CalculateDayDiff (Date1 jako Date, Optional Date2 as Date = "06/02/2020") as Double'vypočítat rozdílCalculateDayDiff = Date2-Date1Koncová funkce |
ByVal a ByRef
Když předáváte funkci funkci, můžete použít ByVal nebo ByRef klíčová slova. Pokud některou z nich vynecháte, bude ByRef se používá jako výchozí.
ByVal znamená, že funkci předáváte kopii proměnné, zatímco ByRef znamená, že odkazujete na původní hodnotu proměnné. Když předáte kopii proměnné (ByVal), původní hodnota proměnné je NE změněno, ale když na proměnnou odkazujete, původní hodnota proměnné se funkcí změní.
| 1234 | Funkce GetValue (ByRef intA As Integer) As IntegerintA = intA * 4GetValue = intAKoncová funkce |
Ve výše uvedené funkci by ByRef mohl být vynechán a funkce by fungovala stejným způsobem.
| 1234 | Funkce GetValue (intA As Integer) As IntegerintA = intA * 4GetValue = intAKoncová funkce |
Chcete-li volat tuto funkci, můžeme spustit dílčí proceduru.
| 123456789 | Dílčí testovací hodnoty ()Dim intVal jako celé číslo'naplňte proměnnou hodnotou 10intVal = 10'spusťte funkci GetValue a zobrazte hodnotu v bezprostředním okně.'Debug.Print GetValue (intVal)'zobrazit hodnotu proměnné intVal v bezprostředním okněDebug.Print intValEnd Sub |
Všimněte si, že okna ladění zobrazují hodnotu 40 oba časy. Když předáte proměnné IntVal funkci - hodnota 10 je předána funkci a vynásobena 4. Použitím klíčového slova ByRef (nebo jeho úplným vynecháním) ZMĚŇTE hodnotu proměnné IntVal. To se zobrazí, když nejprve zobrazíte výsledek funkce v bezprostředním okně (40) a poté hodnotu proměnné IntVal v ladicím okně (také 40).
Pokud nechceme změnit hodnotu původní proměnné, musíme ve funkci použít ByVal.
| 1234 | Funkce GetValue (ByVal intA As Integer) As IntegerintA = intA * 4GetValue = intAKoncová funkce |
Pokud nyní zavoláme funkci z dílčí procedury, hodnota proměnné IntVal zůstane na 10.
Funkce ukončení
Pokud vytvoříte funkci, která testuje určitou podmínku, a jakmile se zjistí, že je podmínka pravdivá, chcete vrátit hodnotu z funkce, budete možná muset do funkce přidat příkaz Ukončit funkci, aby bylo možné funkci ukončit před prošli jste veškerý kód v této funkci.
| 12345678910111213 | Funkce FindNumber (strSearch As String) jako celé čísloDim i As Integer'smyčku skrz každé písmeno v řetězciPro i = 1 To Len (strSearch)'pokud je písmeno číselné, vraťte hodnotu funkciIf IsNumeric (Mid (strSearch, i, 1)) ThenFindNumber = Mid (strSearch, i, 1)'pak funkci ukončeteFunkce ukončeníKonec IfdalšíFindNumber = 0Koncová funkce |
Výše uvedená funkce bude procházet poskytnutým řetězcem, dokud nenajde číslo, a poté toto číslo z řetězce vrátí. Najde pouze první číslo v řetězci, jak to pak bude Výstup funkce.
Výše uvedenou funkci lze vyvolat sub rutinou, jako je ta níže.
| 1234567 | Dílčí šekForNumber ()Dim NumIs jako Integer'předejte textový řetězec funkci find numberNumIs = FindNumber ("Horní patro, 8 Oak Lane, Texas")'zobrazit výsledek v bezprostředním okněDebug.Print NumIsEnd Sub |
Použití funkce z listu aplikace Excel
Kromě volání funkce z kódu VBA pomocí dílčí procedury můžete tuto funkci také zavolat z listu aplikace Excel. Funkce, které jste vytvořili, by se ve výchozím nastavení měly zobrazovat ve vašem seznamu funkcí v části Funkce definované uživatelem v seznamu funkcí.
Klikněte na fx k zobrazení dialogového okna Vložit funkci.
Vybrat Definováno uživatelem ze seznamu kategorií
Vyberte z dostupných funkcí požadovanou funkci Uživatelem definované funkce (UDF).
Alternativně, když začnete psát svou funkci v aplikaci Excel, funkce by se měla objevit v rozevíracím seznamu funkcí.
Pokud nechcete, aby byla funkce k dispozici v listu aplikace Excel, musíte při vytváření funkce v kódu VBA umístit soukromé slovo před slovo Funkce.
| 123 | Soukromá funkce CalculateDayDiff (Date1 jako Date, Date2 as Date) as DoubleCalculateDayDiff = Date2-Date1Koncová funkce |
Nyní se nezobrazí v rozevíracím seznamu zobrazujícím dostupné funkce aplikace Excel.
Zajímavé však je, že tuto funkci můžete stále používat - při hledání ji prostě neobjeví v seznamu!
Pokud jste druhý argument deklarovali jako Volitelný, můžete jej vynechat v listu aplikace Excel i v kódu VBA.
Můžete také použít funkci, kterou jste vytvořili bez argumentů v listu aplikace Excel.
