Vytvoření funkce definované uživatelem (UDF) v aplikaci Excel VBA (Ultimate Guide)

S VBA můžete vytvořit vlastní funkci (nazývanou také uživatelem definovaná funkce), kterou lze použít v listech stejně jako běžné funkce.

Ty jsou užitečné, když stávající funkce Excelu nestačí. V takových případech si můžete vytvořit vlastní uživatelsky definovanou funkci (UDF), která bude vyhovovat vašim konkrétním potřebám.

V tomto tutoriálu se budu zabývat vším o vytváření a používání vlastních funkcí ve VBA.

Pokud máte zájem naučit se VBA jednoduchým způsobem, podívejte se na můj Online školení Excel VBA.

Co je to funkční postup ve VBA?

Procedura funkce je kód VBA, který provádí výpočty a vrací hodnotu (nebo řadu hodnot).

Pomocí procedury Function můžete vytvořit funkci, kterou můžete použít v listu (stejně jako všechny běžné funkce Excelu, jako je SUM nebo VLOOKUP).

Když jste vytvořili proceduru Function pomocí VBA, můžete ji použít třemi způsoby:

1. Jako vzorec v listu, kde může přijímat argumenty jako vstupy a vrací hodnotu nebo pole hodnot.
2. Jako součást kódu podprogramu VBA nebo jiného kódu funkce.
3. V podmíněném formátování.

I když je v listu již k dispozici více než 450 vestavěných funkcí aplikace Excel, můžete potřebovat vlastní funkci, pokud:

• Vestavěné funkce nemohou dělat to, co chcete. V tomto případě můžete vytvořit vlastní funkci na základě vašich požadavků.
• Vestavěné funkce mohou práci dokončit, ale vzorec je dlouhý a komplikovaný. V takovém případě můžete vytvořit vlastní funkci, která se snadno čte a používá.

Všimněte si toho, že vlastní funkce vytvořené pomocí VBA mohou být výrazně pomalejší než vestavěné funkce. Proto jsou nejvhodnější pro situace, kdy pomocí vestavěných funkcí nemůžete dosáhnout výsledku.

Funkce vs. Podprogram ve VBA

„Podprogram“ vám umožňuje spustit sadu kódu, zatímco „funkce“ vrací hodnotu (nebo řadu hodnot).

Chcete -li uvést příklad, pokud máte seznam čísel (kladných i záporných) a chcete identifikovat záporná čísla, zde je uvedeno, co můžete dělat s funkcí a podprogramem.

Podprogram může procházet každou buňkou v rozsahu a může zvýraznit všechny buňky, které v sobě mají zápornou hodnotu. V tomto případě podprogram skončí změnou vlastností objektu rozsahu (změnou barvy buněk).

S vlastní funkcí ji můžete použít v samostatném sloupci a může vrátit hodnotu TRUE, pokud je hodnota v buňce záporná, a FALSE, pokud je kladná. Pomocí funkce nemůžete měnit vlastnosti objektu. To znamená, že nemůžete změnit barvu buňky pomocí samotné funkce (můžete to však provést pomocí podmíněného formátování s vlastní funkcí).

Když vytvoříte uživatelem definovanou funkci (UDF) pomocí VBA, můžete tuto funkci použít v listu stejně jako jakoukoli jinou funkci. Podrobněji se tomu budu věnovat v části „Různé způsoby používání funkce definované uživatelem v aplikaci Excel“.

Vytvoření jednoduché funkce definované uživatelem ve VBA

Dovolte mi vytvořit jednoduchou uživatelsky definovanou funkci ve VBA a ukázat vám, jak funguje.

Níže uvedený kód vytvoří funkci, která extrahuje číselné části z alfanumerického řetězce.

Funkce GetNumeric (CellRef As String) jako Long Dim StringLength jako Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Další i GetNumeric = Funkce ukončení výsledku

Když máte v modulu výše uvedený kód, můžete tuto funkci použít v sešitu.

Níže je uvedeno, jak tato funkce - GetNumeric - lze použít v aplikaci Excel.

Než vám řeknu, jak je tato funkce ve VBA vytvořena a jak funguje, měli byste vědět několik věcí:

• Když vytvoříte funkci ve VBA, bude k dispozici v celém sešitu stejně jako všechny ostatní běžné funkce.
• Když zadáte název funkce a za ním znaménko rovná se, Excel vám název funkce zobrazí v seznamu odpovídajících funkcí. Když jsem ve výše uvedeném příkladu zadal = Získat, Excel mi ukázal seznam, který měl moji vlastní funkci.

Věřím, že je to dobrý příklad, když můžete pomocí VBA vytvořit v Excelu snadno použitelnou funkci. Totéž můžete udělat také pomocí vzorce (jak je ukázáno v tomto tutoriálu), ale to se stává komplikovaným a těžko pochopitelným. S tímto UDF stačí předat jeden argument a výsledek je jasný.

Anatomie funkce definované uživatelem ve VBA

Ve výše uvedené části jsem vám dal kód a ukázal vám, jak funkce UDF funguje v listu.

Nyní se ponořme do hloubky a podívejme se, jak je tato funkce vytvořena. Níže uvedený kód musíte umístit do modulu v editoru VB. Tomuto tématu se věnuji v sekci - „Kam umístit kód VBA pro funkci definovanou uživatelem“.

Funkce GetNumeric (CellRef As String) as Long 'Tato funkce extrahuje číselnou část z řetězce Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Výsledek a střed (CellRef, i, 1) Další i GetNumeric = Funkce ukončení výsledku

První řádek kódu začíná slovem - Funkce.

Toto slovo říká VBA, že náš kód je funkce (a ne podprogram). Za slovem Funkce následuje název funkce - GetNumeric. Toto je název, který budeme v listu používat k použití této funkce.

• Název funkce nesmí obsahovat mezery. Funkci také nemůžete pojmenovat, pokud koliduje s názvem odkazu na buňku. Například nemůžete pojmenovat funkci ABC123, protože také odkazuje na buňku v listu aplikace Excel.
• Neměli byste své funkci dávat stejný název jako stávající funkci. Pokud to uděláte, Excel dá přednost integrované funkci.
• Pokud chcete oddělit slova, můžete použít podtržítko. Přijatelný název je například Get_Numeric.

Za názvem funkce následují v závorce některé argumenty. Toto jsou argumenty, které by naše funkce od uživatele potřebovala. Jsou to stejné jako argumenty, které musíme dodat do vestavěných funkcí Excelu. Například ve funkci COUNTIF existují dva argumenty (rozsah a kritéria)

V závorkách musíte zadat argumenty.

V našem příkladu existuje pouze jeden argument - CellRef.

Je také vhodné určit, jaký argument funkce očekává. V tomto případě, protože funkci budeme krmit odkazem na buňku, můžeme argument zadat jako typ „Rozsah“. Pokud neurčíte datový typ, VBA by jej považoval za variantu (což znamená, že můžete použít jakýkoli datový typ).

Pokud máte více než jeden argument, můžete je zadat ve stejné závorce - oddělené čárkou. Později v tomto kurzu uvidíme, jak použít více argumentů v uživatelem definované funkci.

Funkce je uvedena jako datový typ „Řetězec“. To by VBA řeklo, že výsledek vzorce bude datového typu String.

I když zde mohu použít numerický datový typ (například dlouhý nebo dvojitý), omezilo by to rozsah čísel, která může vrátit. Pokud mám řetězec dlouhý 20 čísel, který potřebuji extrahovat z celkového řetězce, deklarace funkce jako Long nebo Double by způsobila chybu (protože číslo by bylo mimo jeho rozsah). Proto jsem ponechal výstupní datový typ funkce jako řetězec.

Druhý řádek kódu - ten zelený, který začíná apostrofem - je komentář. Při čtení kódu VBA tento řádek ignoruje. To můžete použít k přidání popisu nebo podrobností o kódu.

Třetí řádek kódu deklaruje proměnnou „StringLength“ jako celočíselný datový typ. Toto je proměnná, do které ukládáme hodnotu délky řetězce, který je vzorcem analyzován.

Čtvrtý řádek deklaruje proměnnou Result jako datový typ String. Toto je proměnná, kde budeme extrahovat čísla z alfanumerického řetězce.

Pátý řádek přiřadí délku řetězce ve vstupním argumentu proměnné ‘StringLength’. Všimněte si, že „CellRef“ odkazuje na argument, který uživatel uvede při použití vzorce v listu (nebo při použití ve VBA - což uvidíme později v tomto tutoriálu).

Šestý, sedmý a osmý řádek jsou součástí smyčky For Next. Smyčka běží tolikrát, kolik znaků obsahuje vstupní argument. Toto číslo je dáno funkcí LEN a je přiřazeno proměnné „StringLength“.

Smyčka tedy běží od ‘1 do Stringlength’.

Ve smyčce příkaz IF analyzuje každý znak řetězce, a pokud je číselný, přidá tento číselný znak do proměnné Výsledek. K tomu používá funkci MID ve VBA.

Druhý poslední řádek kódu přiřadí funkci hodnotu výsledku. Je to tento řádek kódu, který zajišťuje, že funkce vrátí hodnotu „Výsledek“ zpět do buňky (odkud je volána).

Poslední řádek kódu je End Function. Toto je povinný řádek kódu, který říká VBA, že kód funkce zde končí.

Výše uvedený kód vysvětluje různé části typické vlastní funkce vytvořené ve VBA. V následujících částech se podrobně ponoříme do těchto prvků a také uvidíme různé způsoby, jak spustit funkci VBA v aplikaci Excel.

Argumenty ve funkci definované uživatelem ve VBA

Ve výše uvedených příkladech, kde jsme vytvořili uživatelem definovanou funkci pro získání číselné části z alfanumerického řetězce (GetNumeric), byla funkce navržena tak, aby převzala jeden jediný argument.

V této části se budu zabývat tím, jak vytvářet funkce, které neberou žádný argument k těm, které používají více argumentů (povinné i volitelné argumenty).

Vytvoření funkce ve VBA bez jakýchkoli argumentů

V listu aplikace Excel máme několik funkcí, které nevyžadují žádné argumenty (například RAND, TODAY, NOW).

Tyto funkce nejsou závislé na žádných vstupních argumentech. Například funkce TODAY vrátí aktuální datum a funkce RAND vrátí náhodné číslo mezi 0 a 1.

Podobnou funkci můžete vytvořit také ve VBA.

Níže je uveden kód, který vám poskytne název souboru. Nevyžaduje žádné argumenty, protože výsledek, který potřebuje vrátit, nezávisí na žádném argumentu.

Funkce WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Výše uvedený kód určuje výsledek funkce jako datový typ String (jako výsledek chceme název souboru - což je řetězec).

Tato funkce přiřadí funkci „ThisWorkbook.Name“, která je vrácena při použití funkce v listu.

Pokud byl soubor uložen, vrátí název s příponou, v opačném případě název pouze uvede.

Výše uvedené má však jeden problém.

Pokud se název souboru změní, automaticky se neaktualizuje. Normálně se funkce obnoví, kdykoli dojde ke změně vstupních argumentů. Ale protože v této funkci nejsou žádné argumenty, funkce se nepřepočítá (i když změníte název sešitu, zavřete jej a znovu otevřete).

Pokud chcete, můžete vynutit přepočet pomocí klávesové zkratky - Control + Alt + F9.

Chcete -li, aby se vzorec přepočítal vždy, když dojde k změně v listu, musíte do něj zadat řádek kódu.

Níže uvedený kód umožňuje funkci přepočítat vždy, když dojde ke změně v listu (stejně jako jiné podobné funkce listu, jako je funkce DNES nebo RAND).

Funkce WorkbookName () As String Application.Volatile True WorkbookName = ThisWorkbook.Name End Function

Pokud nyní změníte název sešitu, tato funkce by se aktualizovala vždy, když dojde k nějaké změně v listu nebo když tento sešit znovu otevřete.

Vytvoření funkce ve VBA s jedním argumentem

V jedné z výše uvedených sekcí jsme již viděli, jak vytvořit funkci, která přebírá pouze jeden argument (výše uvedená funkce GetNumeric).

Vytvořme další jednoduchou funkci, která vyžaduje pouze jeden argument.

Funkce vytvořená pomocí níže uvedeného kódu převede odkazovaný text na velká písmena. Nyní pro něj již máme funkci v Excelu a tato funkce vám jen ukáže, jak funguje. Pokud to musíte udělat, je lepší použít vestavěnou funkci UPPER.

Funkce ConvertToUpperCase (CellRef jako rozsah) ConvertToUpperCase = UCase (CellRef) Koncová funkce

Tato funkce používá funkci UCase ve VBA ke změně hodnoty proměnné CellRef. Poté přiřadí hodnotu funkci ConvertToUpperCase.

Protože tato funkce vyžaduje argument, nemusíme zde používat část Application.Volatile. Jakmile se argument změní, funkce se automaticky aktualizuje.

Vytvoření funkce ve VBA s více argumenty

Stejně jako funkce listu můžete ve VBA vytvářet funkce, které vyžadují více argumentů.

Níže uvedený kód by vytvořil funkci, která extrahuje text před zadaným oddělovačem. Jsou zapotřebí dva argumenty - odkaz na buňku s textovým řetězcem a oddělovač.

Funkce GetDataBeforeDelimiter (CellRef jako rozsah, Delim jako řetězec) jako řetězec Dim Výsledek jako řetězec Dim DelimPosition jako celé číslo DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 výsledek = vlevo (CellRef, DelimPosition) GetDataBeultDelimit

Pokud potřebujete v uživatelem definované funkci použít více než jeden argument, můžete mít všechny argumenty v závorkách oddělené čárkou.

Všimněte si, že pro každý argument můžete zadat datový typ. Ve výše uvedeném příkladu byl „CellRef“ deklarován jako datový typ rozsahu a „Delim“ byl deklarován jako datový typ String. Pokud nezadáte žádný datový typ, VBA je považuje za variantní datový typ.

Když použijete výše uvedenou funkci v listu, musíte jako první argument uvést odkaz na buňku, která má text jako znak, a znaky uvozovek ve dvojitých uvozovkách.

Poté zkontroluje polohu oddělovače pomocí funkce INSTR ve VBA. Tato pozice se pak použije k extrahování všech znaků před oddělovač (pomocí funkce VLEVO).

Nakonec funkci přiřadí výsledek.

Tento vzorec má k dokonalosti daleko. Pokud například zadáte oddělovač, který se v textu nenachází, způsobí chybu. Nyní můžete použít funkci IFERROR v listu, abyste se zbavili chyb, nebo můžete použít níže uvedený kód, který vrací celý text, když nemůže najít oddělovač.

Funkce GetDataBeforeDelimiter (CellRef jako rozsah, Delim jako řetězec) jako řetězec Dim Výsledek jako řetězec Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition <0 Then DelimPosition = Len (CellRef) Result = Left CellRef, DelimPosition) GetDataBeforeDelimiter = Koncová funkce výsledku

Tuto funkci můžeme dále optimalizovat.

Pokud zadáte text (ze kterého chcete extrahovat část před oddělovač) přímo do funkce, způsobí vám to chybu. Jen do toho … zkuste to!

K tomu dochází, protože jsme jako datový typ rozsahu zadali „CellRef“.

Nebo pokud chcete, aby byl oddělovač v buňce a místo pevného kódování ve vzorci použít odkaz na buňku, nemůžete to s výše uvedeným kódem udělat. Je to proto, že Delim byl deklarován jako řetězec datového typu.

Pokud chcete, aby funkce měla flexibilitu přijímat přímé zadávání textu nebo odkazy na buňky od uživatele, musíte odebrat deklaraci datového typu. To by skončilo vytvořením argumentu jako variantního datového typu, který může přijmout jakýkoli typ argumentu a zpracovat ho.

Následující kód by to udělal:

Funkce GetDataBeforeDelimiter (CellRef, Delim) As String Dim Výsledek jako String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition <0 Then DelimPosition = Len (CellRef) Result = Left (CellRef, GetDataBeforeDelimiter = Výsledná koncová funkce

Vytvoření funkce ve VBA s volitelnými argumenty

V aplikaci Excel existuje mnoho funkcí, kde jsou některé argumenty volitelné.

Například legendární funkce VLOOKUP má 3 povinné argumenty a jeden volitelný argument.

Volitelný argument, jak naznačuje název, je volitelný. Pokud nezadáte jeden z povinných argumentů, funkce vám způsobí chybu, ale pokud nezadáte nepovinný argument, vaše funkce bude fungovat.

Volitelné argumenty ale nejsou zbytečné. Umožňují vám vybrat si z řady možností.

Pokud například ve funkci VLOOKUP nezadáte čtvrtý argument, VLOOKUP provede přibližné vyhledávání a pokud zadáte poslední argument jako NEPRAVDA (nebo 0), pak bude mít přesnou shodu.

Pamatujte, že volitelné argumenty musí vždy následovat po všech požadovaných argumentech. Na začátku nemůžete mít volitelné argumenty.

Nyní se podívejme, jak vytvořit funkci ve VBA pomocí volitelných argumentů.

Funkce pouze s volitelným argumentem

Pokud vím, neexistuje žádná vestavěná funkce, která by brala pouze volitelné argumenty (zde se mohu mýlit, ale na žádnou takovou funkci nemůžu myslet).

Ale můžeme vytvořit jeden s VBA.

Níže je uveden kód funkce, která vám poskytne aktuální datum ve formátu dd-mm-rrrr, pokud nezadáte žádný argument (tj. Ponecháte prázdné), a ve formátu „dd mmmm, rrrr“, pokud zadáte cokoli jako argument (tj. cokoli, aby argument nebyl prázdný).

Funkce CurrDate (volitelně fmt jako varianta) Dim Výsledek Pokud IsMissing (fmt) Then CurrDate = Format (Date, "dd-mm-yyyy") Else CurrDate = Format (Date, "dd mmmm, yyyy") End If End Function

Všimněte si, že výše uvedená funkce používá „IsMissing“ ke kontrole, zda argument chybí nebo ne. Chcete -li použít funkci IsMissing, váš volitelný argument musí být variantního datového typu.

Výše uvedená funkce funguje bez ohledu na to, co zadáte jako argument. V kódu kontrolujeme pouze to, zda je zadán volitelný argument nebo ne.

Můžete to udělat robustnějším tím, že jako argumenty vezmete pouze konkrétní hodnoty a ve zbytku případů zobrazíte chybu (jak je uvedeno v níže uvedeném kódu).

Funkce CurrDate (volitelně fmt jako varianta) Dim Výsledek Pokud IsMissing (fmt) Then CurrDate = Format (Date, "dd-mm-yyyy") ElseIf fmt = 1 Then CurrDate = Format (Date, "dd mmmm, yyyy") Else CurrDate = CVErr (xlErrValue) Funkce End If End

Výše uvedený kód vytvoří funkci, která zobrazuje datum ve formátu „dd-mm-rrrr“, pokud není zadán žádný argument, a ve formátu „dd mmmm, rrrr“, pokud je argument 1. Dává chybu ve všech ostatních případech.

Funkce s požadovanými i volitelnými argumenty

Už jsme viděli kód, který extrahuje číselnou část z řetězce.

Nyní se podívejme na podobný příklad, který bere jak povinné, tak nepovinné argumenty.

Níže uvedený kód vytvoří funkci, která extrahuje textovou část z řetězce. Pokud je volitelný argument PRAVDA, dává výsledek velkými písmeny a pokud je volitelný argument NEPRAVDA nebo je vynechán, dává výsledek takový, jaký je.

Funkce GetText (CellRef jako rozsah, volitelná TextCase = False) jako řetězec Dim StringLength jako Integer Dim Výsledek jako řetězec StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Then Result = Result & Mid (CellRef, i, 1) Next i If TextCase = True Then Result = UCase (Result) GetText = Result End Function

Všimněte si, že ve výše uvedeném kódu jsme inicializovali hodnotu ‘TextCase’ jako False (podívejte se do závorek v prvním řádku).

Tím jsme zajistili, že volitelný argument začíná výchozí hodnotou, která je FALSE. Pokud uživatel zadá hodnotu jako PRAVDA, funkce vrátí text velkými písmeny a pokud uživatel zadá volitelný argument jako NEPRAVDA nebo jej vynechá, pak bude vrácený text takový, jaký je.

Vytvoření funkce ve VBA s polem jako argumentem

Doposud jsme viděli příklady vytváření funkce s volitelnými/požadovanými argumenty - kde tyto argumenty byly jedinou hodnotou.

Můžete také vytvořit funkci, která může mít jako argument pole. Ve funkcích listu aplikace Excel existuje mnoho funkcí, které přebírají argumenty pole, například SUM, VLOOKUP, SUMIF, COUNTIF atd.

Níže je kód, který vytváří funkci, která dává součet všech sudých čísel v zadaném rozsahu buněk.

Funkce AddEven (CellRef jako rozsah) Dim Cell as Range for each Cell in CellRef If IsNumeric (Cell.Value) Then If Cell.Value Mod 2 = 0 Then Result = Result + Cell.Value End If End If Next Cell AddEven = Result End Funkce

Tuto funkci můžete použít v listu a zadat rozsah buněk, které mají jako argument čísla. Funkce by vrátila jednu hodnotu - součet všech sudých čísel (jak je uvedeno níže).

Ve výše uvedené funkci jsme místo jediné hodnoty dodali pole (A1: A10). Aby to fungovalo, musíte se ujistit, že váš datový typ argumentu může přijmout pole.

Ve výše uvedeném kódu jsem zadal argument CellRef jako Range (který může mít jako vstup pole). Můžete zde také použít datový typ varianty.

V kódu je smyčka For Every, která prochází každou buňkou a kontroluje, zda je číslo ne. Pokud tomu tak není, nic se nestane a přesune se do další buňky. Pokud je to číslo, zkontroluje, zda je sudé nebo ne (pomocí funkce MOD).

Nakonec se sečtou všechna sudá čísla a součet se přiřadí zpět funkci.

Vytvoření funkce s neurčitým počtem argumentů

Při vytváření některých funkcí ve VBA možná neznáte přesný počet argumentů, které chce uživatel zadat. Je tedy nutné vytvořit funkci, která bude akceptovat zadané množství argumentů, a použít je k vrácení výsledku.

Příkladem takovéto funkce listu je funkce SUMA. Můžete mu dodat více argumentů (například tento):

= SUM (A1, A2: A4, B1: B20)

Výše uvedená funkce by přidala hodnoty do všech těchto argumentů. Všimněte si také, že to může být jedna buňka nebo pole buněk.

Ve VBA můžete vytvořit takovou funkci tak, že poslední argument (nebo to může být jediný argument) bude volitelný. Tomuto volitelnému argumentu by také mělo předcházet klíčové slovo „ParamArray“.

„ParamArray“ je modifikátor, který vám umožňuje přijmout tolik argumentů, kolik chcete. Všimněte si toho, že použití slova ParamArray před argumentem činí argument nepovinným. Zde však nemusíte používat slovo Volitelné.

Nyní vytvořme funkci, která může přijmout libovolný počet argumentů a přidá všechna čísla do zadaných argumentů:

Funkce AddArguments (ParamArray arglist () jako varianta) Pro každý arg V arglist AddArguments = AddArguments + arg Další arg Koncová funkce

Výše uvedená funkce může mít libovolný počet argumentů a přidat tyto argumenty k získání výsledku.

Všimněte si, že jako argument můžete použít pouze jednu hodnotu, odkaz na buňku, logickou hodnotu nebo výraz. Jako argument nemůžete zadat pole. Pokud je například jeden z vašich argumentů D8: D10, tento vzorec by vám dal chybu.

Chcete-li používat oba argumenty s více buňkami, musíte použít níže uvedený kód:

Funkce AddArguments (ParamArray arglist () jako varianta) Pro každý arg In arglist Pro každou buňku v arg AddArguments = AddArguments + buňka Další buňka Další arg Koncová funkce

Všimněte si, že tento vzorec funguje s více buňkami a odkazy na pole, ale nemůže zpracovávat pevně zakódované hodnoty nebo výrazy. Můžete vytvořit robustnější funkci kontrolou a ošetřením těchto podmínek, ale o to zde nejde.

Účelem je ukázat vám, jak ParamArray funguje, takže ve funkci můžete povolit neurčitý počet argumentů. V případě, že chcete lepší funkci, než je ta, kterou vytvořil výše uvedený kód, použijte v listu funkci SUMA.

Vytvoření funkce, která vrací pole

Doposud jsme viděli funkce, které vracejí jedinou hodnotu.

S VBA můžete vytvořit funkci, která vrací variantu, která může obsahovat celé pole hodnot.

Maticové vzorce jsou k dispozici také jako vestavěné funkce v listech aplikace Excel. Pokud znáte maticové vzorce v Excelu, věděli byste, že se zadávají pomocí Ctrl + Shift + Enter (namísto pouze Enter). Více o vzorcích pole si můžete přečíst zde. Pokud nevíte o maticových vzorcích, nebojte se, pokračujte ve čtení.

Vytvoříme vzorec, který vrátí pole tří čísel (1,2,3).

Níže uvedený kód by to udělal.

Funkce ThreeNumbers () jako varianta Dim DimValue (1 až 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue End Function

Ve výše uvedeném kódu jsme jako variantu zadali funkci „ThreeNumbers“. To mu umožňuje uchovávat řadu hodnot.

Proměnná ‘NumberValue’ je deklarována jako pole se 3 prvky. Obsahuje tři hodnoty a přiřadí je funkci „ThreeNumbers“.

Tuto funkci můžete použít v listu zadáním funkce a stisknutím klávesy Control + Shift + Enter (podržte klávesy Control a Shift a poté stiskněte Enter).

Když to uděláte, vrátí 1 v buňce, ale ve skutečnosti obsahuje všechny tři hodnoty. Chcete -li to zkontrolovat, použijte následující vzorec:

= MAX (tři čísla ())

Výše uvedenou funkci použijte pomocí Ctrl + Shift + Enter. Všimnete si, že výsledek je nyní 3, protože jde o největší hodnoty v poli vráceném funkcí Max, která získá tři čísla jako výsledek naší uživatelem definované funkce - ThreeNumbers.

Stejnou technikou můžete vytvořit funkci, která vrací pole Month Names, jak ukazuje následující kód:

Funkce Měsíce () Jako varianta Dim DimNázev měsíce (1 až 12) Název měsíce (1) = "Leden" Název měsíce (2) = "Únor" Název měsíce (3) = "Březen" Název měsíce (4) = "Duben" Název měsíce (5) = "May" MonthName (6) = "June" MonthName (7) = "July" MonthName (8) = "August" MonthName (9) = "September" MonthName (10) = "October" MonthName (11) = "November "MonthName (12) =" Prosinec "Měsíce = MonthName Koncová funkce

Když nyní zadáte funkci = Měsíce () do listu aplikace Excel a použijete Control + Shift + Enter, vrátí celou řadu názvů měsíců. Všimněte si, že v buňce vidíte pouze leden, protože to je první hodnota v poli. To neznamená, že pole vrací pouze jednu hodnotu.

Chcete -li ukázat skutečnost, že vrací všechny hodnoty, proveďte to - vyberte buňku se vzorcem, přejděte na lištu vzorců, vyberte celý vzorec a stiskněte klávesu F9. Zobrazí se vám všechny hodnoty, které funkce vrací.

Můžete to použít pomocí níže uvedeného vzorce INDEX k získání seznamu všech názvů měsíců najednou.

= INDEX (měsíce (), ŘÁDEK ())

Pokud nyní máte mnoho hodnot, není vhodné tyto hodnoty přiřazovat jednu po druhé (jak jsme to udělali výše). Místo toho můžete ve VBA použít funkci Array.

Stejný kód, kde vytváříme funkci „Měsíce“, by se tedy zkrátil, jak je uvedeno níže:

Měsíce funkcí () Jako variabilní měsíce = pole („leden“, „únor“, „březen“, „duben“, „květen“, „červen“, _ „červenec“, „srpen“, „září“, „říjen“ , „Listopad“, „Prosinec“) Koncová funkce

Výše uvedená funkce používá funkci Array k přiřazení hodnot přímo funkci.

Všimněte si, že všechny funkce vytvořené výše vrátí horizontální pole hodnot. To znamená, že pokud vyberete 12 vodorovných buněk (řekněme A1: L1) a do buňky A1 zadáte vzorec = Měsíce (), zobrazí se vám názvy všech měsíců.

Ale co když chcete tyto hodnoty ve svislém rozsahu buněk.

To lze provést pomocí vzorce TRANSPOSE v listu.

Jednoduše vyberte 12 svislých buněk (souvislých) a zadejte níže uvedený vzorec.

Pochopení rozsahu funkce definované uživatelem v aplikaci Excel

Funkce může mít dva obory - Veřejnost nebo Soukromý.

• A Veřejná působnost znamená, že funkce je k dispozici pro všechny listy v sešitu i pro všechny postupy (dílčí a funkce) ve všech modulech v sešitu. To je užitečné, když chcete zavolat funkci z podprogramu (uvidíme, jak se to dělá v další části).
• A Soukromý rozsah znamená, že funkce je k dispozici pouze v modulu, ve kterém existuje. Nelze jej použít v jiných modulech. Rovněž jej neuvidíte v seznamu funkcí v listu. Pokud je například název vaší funkce „Měsíce ()“ a zadáte funkci v Excelu (za znaménkem =), název funkce se vám nezobrazí. Můžete jej však stále používat, pokud zadáte název vzorce.

Pokud nic neurčíte, je tato funkce ve výchozím nastavení veřejná.

Níže je funkce, která je soukromou funkcí:

Soukromá funkce WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Tuto funkci můžete použít v podprogramech a postupech ve stejných modulech, ale nemůžete ji použít v jiných modulech. Tato funkce by se také nezobrazila v listu.

Níže uvedený kód by tuto funkci učinil veřejnou. Zobrazí se také v pracovním listu.

Funkce WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Různé způsoby používání funkce definované uživatelem v aplikaci Excel

Jakmile ve VBA vytvoříte uživatelem definovanou funkci, můžete ji používat mnoha různými způsoby.

Podívejme se nejprve na to, jak používat funkce v listu.

Použití UDF v pracovních listech

Už jsme viděli příklady použití funkce vytvořené ve VBA v listu.

Vše, co musíte udělat, je zadat název funkce a zobrazí se v intellisense.

Aby se funkce zobrazila v listu, musí to být veřejná funkce (jak je vysvětleno v části výše).

K vložení funkce definované uživatelem můžete také použít dialogové okno Vložit funkci (pomocí níže uvedených kroků). To by fungovalo pouze pro funkce, které jsou veřejné.

• Přejděte na kartu Data.
• Klikněte na možnost „Vložit funkci“.
• V dialogovém okně Vložit funkci vyberte kategorii definovanou uživatelem. Tato možnost se zobrazí pouze v případě, že máte funkci v editoru VB (a funkce je veřejná).
• Vyberte funkci ze seznamu všech veřejných funkcí definovaných uživatelem.
• Klikněte na tlačítko OK.

Výše uvedené kroky by vložily funkci do listu. Rovněž zobrazí dialogové okno Argumenty funkcí, které vám poskytne podrobnosti o argumentech a výsledku.

Uživatelsky definovanou funkci můžete použít stejně jako jakoukoli jinou funkci v Excelu. To také znamená, že jej můžete použít s jinými vestavěnými funkcemi aplikace Excel. Například. níže uvedený vzorec by dal název sešitu velkými písmeny:

= HORNÍ (název sešitu ())

Používání funkcí definovaných uživatelem v procedurách a funkcích VBA

Když jste vytvořili funkci, můžete ji použít i v jiných dílčích procedurách.

Pokud je funkce veřejná, lze ji použít v jakékoli proceduře ve stejném nebo jiném modulu. Pokud je soukromý, lze jej použít pouze ve stejném modulu.

Níže je funkce, která vrací název sešitu.

Funkce WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Níže uvedený postup volá funkci a poté zobrazí jméno v okně se zprávou.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

Můžete také volat funkci z jiné funkce.

V níže uvedených kódech první kód vrací název sešitu a druhý vrací název velkými písmeny voláním první funkce.

Funkce WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Funkce WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) Koncová funkce

Volání funkce definované uživatelem z jiných sešitů

Pokud máte funkci v sešitu, můžete tuto funkci volat i v jiných sešitech.

Existuje několik způsobů, jak to provést:

1. Vytvoření doplňku
2. Funkce ukládání do osobního sešitu maker
3. Odkazování na funkci z jiného sešitu.

Vytvoření doplňku

Vytvořením a instalací doplňku budete mít vlastní funkci v něm k dispozici ve všech sešitech.

Předpokládejme, že jste vytvořili vlastní funkci - „GetNumeric“ a chcete ji ve všech sešitech. Chcete -li to provést, vytvořte nový sešit a mít kód funkce v modulu v tomto novém sešitu.

Nyní postupujte podle níže uvedených kroků a uložte jej jako doplněk a poté jej nainstalujte do aplikace Excel.

• Přejděte na kartu Soubor a klikněte na Uložit jako.
• V dialogovém okně Uložit jako změňte typ „Uložit jako“ na .xlam. Název, který souboru přiřadíte, bude název vašeho doplňku. V tomto případě je soubor uložen pod názvem GetNumeric.
  • Všimnete si, že cesta k souboru, kam se ukládá, se automaticky změní. Můžete použít výchozí nebo jej změnit, pokud chcete.
• Otevřete nový sešit aplikace Excel a přejděte na kartu Vývojář.
• Klikněte na možnost Doplňky aplikace Excel.
• V dialogovém okně Doplňky procházejte a vyhledejte soubor, který jste uložili, a klikněte na OK.

Nyní byl doplněk aktivován.

Nyní můžete použít vlastní funkci ve všech sešitech.

Uložení funkce do osobního sešitu maker

Osobní sešit maker je skrytý sešit ve vašem systému, který se otevře při každém otevření aplikace Excel.

Je to místo, kam můžete ukládat kódy maker a poté k nim přistupovat z jakéhokoli sešitu. Je to skvělé místo pro ukládání těch maker, která chcete často používat.

Ve výchozím nastavení ve vašem Excelu neexistuje žádný osobní sešit maker. Musíte jej vytvořit zaznamenáním makra a jeho uložením do osobního sešitu makra.

Podrobné kroky, jak vytvářet a ukládat makra v osobním sešitu maker, najdete zde.

Odkazování na funkci z jiného sešitu

Zatímco první dvě metody (vytvoření doplňku a použití osobního sešitu makra) by fungovaly ve všech situacích, pokud chcete odkazovat na funkci z jiného sešitu, musí být tento sešit otevřený.

Předpokládejme, že máte sešit s názvem „Sešit se vzorcem, a má funkci s názvem „GetNumeric ‘.

Chcete -li tuto funkci použít v jiném sešitu (zatímco Sešit se vzorcem je otevřený), můžete použít následující vzorec:

= 'Sešit se vzorcem'! GetNumeric (A1)

Výše uvedený vzorec bude používat uživatelem definovanou funkci v Sešit se vzorcem soubor a poskytne vám výsledek.

Všimněte si toho, protože název sešitu obsahuje mezery, musíte jej uzavřít do jednoduchých uvozovek.

Použití příkazu Exit Function Statement VBA

Pokud chcete ukončit funkci, když je kód spuštěn, můžete to provést pomocí příkazu „Ukončit funkci“.

Níže uvedený kód by extrahoval první tři číselné znaky z alfanumerického textového řetězce. Jakmile získá tři znaky, funkce skončí a vrátí výsledek.

Funkce GetNumericFirstThree (CellRef As Range) As Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric (Mid (CellRef, i, 1)) Then J = J + 1 Result = Result & Mid (CellRef, i, 1) GetNumericFirstThree = Result End If Next i End Funkce

Výše uvedená funkce kontroluje počet znaků, které jsou číselné, a když získá 3 číselné znaky, ukončí funkci v další smyčce.

Ladění funkce definované uživatelem

Při ladění funkce definované uživatelem ve VBA můžete použít několik technik:

Ladění vlastní funkce pomocí pole se zprávou

Pomocí funkce MsgBox zobrazte okno se zprávou s konkrétní hodnotou.

Hodnota, kterou zobrazujete, může být založena na tom, co chcete testovat. Pokud například chcete zkontrolovat, zda se kód spouští nebo ne, jakákoli zpráva by fungovala a pokud chcete zkontrolovat, zda smyčky fungují nebo ne, můžete zobrazit konkrétní hodnotu nebo čítač smyček.

Ladění vlastní funkce nastavením zarážky

Nastavte zarážku, abyste mohli procházet každý řádek jeden po druhém. Chcete -li nastavit zarážku, vyberte řádek, kde jej chcete mít, a stiskněte klávesu F9, nebo klikněte na šedou svislou oblast, která je vlevo u řádků kódu. Každá z těchto metod by vložila zarážku (v šedé oblasti uvidíte červenou tečku).

Jakmile nastavíte zarážku a spustíte funkci, přejde na čáru zarážky a poté se zastaví. Nyní můžete procházet kódem pomocí klávesy F8. Jedním stisknutím klávesy F8 přejdete na další řádek v kódu.

Ladění vlastní funkce pomocí Debug.Print v kódu

Pomocí příkazu Debug.Print ve svém kódu můžete získat hodnoty zadaných proměnných/argumentů v bezprostředním okně.

Například v níže uvedeném kódu jsem použil Debug.Print k získání hodnoty dvou proměnných - „j“ a „Výsledek“

Funkce GetNumericFirstThree (CellRef As Range) As Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric (Mid (CellRef, i, 1)) Then J = J + 1 Result = Result & Mid (CellRef, i, 1) Debug.Print J, Result GetNumericFirstThree = Result End If Next i End Funkce

Po spuštění tohoto kódu se v bezprostředním okně zobrazí následující.

Vestavěné funkce aplikace Excel vs. Uživatelsky definovaná funkce VBA

Použití vestavěných funkcí aplikace Excel oproti vlastním funkcím vytvořeným ve VBA má několik zásadních výhod.

• Vestavěné funkce jsou mnohem rychlejší než funkce VBA.
• Když vytvoříte sestavu/řídicí panel pomocí funkcí VBA a odešlete je klientovi/kolegovi, nemusí se starat o to, zda jsou makra povolena nebo ne. V některých případech se klienti/zákazníci vyděsí tím, že se jim ve žlutém pruhu zobrazí varování (které je jednoduše požádá o povolení maker).
• S integrovanými funkcemi Excelu si nemusíte dělat starosti s příponami souborů. Pokud máte v sešitu makra nebo funkce definované uživatelem, musíte jej uložit do souboru .xlsm.

I když existuje mnoho pádných důvodů pro použití funkcí integrovaných v Excelu, v několika případech je lepší použít uživatelem definovanou funkci.

• Je lepší použít uživatelem definovanou funkci, pokud je váš vestavěný vzorec obrovský a komplikovaný. To se stává ještě relevantnějším, když potřebujete někoho jiného, ​​aby aktualizoval vzorce. Pokud máte například obrovský vzorec složený z mnoha různých funkcí, může být i změna odkazu na buňku únavná a náchylná k chybám. Místo toho můžete vytvořit vlastní funkci, která přebírá pouze jeden nebo dva argumenty a zvládne veškeré náročné operace.
• Když musíte udělat něco, co nelze provést pomocí vestavěných funkcí aplikace Excel. Příkladem toho může být situace, kdy chcete z řetězce extrahovat všechny číselné znaky. V takových případech převažuje výhoda používání funkce definované uživatelem gar nad jejími negativy.

Kam vložit kód VBA pro funkci definovanou uživatelem

Při vytváření vlastní funkce je třeba vložit kód do okna kódu pro sešit, ve kterém chcete funkci.

Níže jsou uvedeny kroky pro vložení kódu pro funkci „GetNumeric“ do sešitu.

1. Přejděte na kartu Vývojář.
2. Klikněte na možnost Visual Basic. Tím se otevře editor VB v backendu.
3. V podokně Průzkumník projektů v editoru VB klikněte pravým tlačítkem na libovolný objekt sešitu, do kterého chcete vložit kód. Pokud Průzkumníka nevidíte, přejděte na kartu Zobrazit a klikněte na Průzkumník projektů.
4. Přejděte na Vložit a klikněte na Modul. Tím se vloží objekt modulu pro váš sešit.
5. Zkopírujte a vložte kód do okna modulu.

Také by se vám mohly líbit následující návody Excel VBA:

• Práce s buňkami a rozsahy v aplikaci Excel VBA.
• Práce s listy v Excelu VBA.
• Práce se sešity pomocí VBA.
• Jak používat smyčky v aplikaci Excel VBA.
• Události aplikace Excel VBA - snadný (a kompletní) průvodce
• Použití příkazů IF Then Else ve VBA.
• Jak zaznamenat makro v aplikaci Excel.
• Jak spustit makro v aplikaci Excel.
• Jak třídit data v aplikaci Excel pomocí VBA (průvodce krok za krokem).
• Funkce Excel VBA InStr - vysvětleno na příkladech.