Vytvorenie funkcie definovanej používateľom (UDF) v programe Excel VBA (Ultimate Guide)

Pomocou VBA môžete vytvoriť vlastnú funkciu (nazývanú aj funkcia definovaná používateľom), ktorú je možné použiť v pracovných hárkoch rovnako ako bežné funkcie.

Sú užitočné, keď existujúce funkcie programu Excel nestačia. V takýchto prípadoch si môžete vytvoriť vlastnú vlastnú funkciu definovanú používateľom (UDF), ktorá bude vyhovovať vašim konkrétnym potrebám.

V tomto návode sa budem zaoberať všetkým o vytváraní a používaní vlastných funkcií vo VBA.

Ak máte záujem naučiť sa VBA jednoduchým spôsobom, pozrite sa na moje Online školenie Excel VBA.

Čo je to funkčný postup vo VBA?

Procedúra funkcie je kód VBA, ktorý vykonáva výpočty a vracia hodnotu (alebo pole hodnôt).

Procedúrou Function môžete vytvoriť funkciu, ktorú môžete použiť v pracovnom hárku (rovnako ako všetky bežné funkcie programu Excel, ako napríklad SUM alebo VLOOKUP).

Keď ste vytvorili procedúru funkcie pomocou VBA, môžete ju použiť tromi spôsobmi:

1. Ako vzorec v pracovnom hárku, kde môže ako vstupy používať argumenty a vracia hodnotu alebo pole hodnôt.
2. Ako súčasť vášho podprogramového kódu VBA alebo iného funkčného kódu.
3. V podmienenom formátovaní.

Aj keď je v pracovnom hárku k dispozícii už viac ako 450 vstavaných funkcií programu Excel, vlastnú funkciu budete potrebovať, ak:

• Vstavané funkcie nedokážu urobiť to, čo chcete. V takom prípade môžete vytvoriť vlastnú funkciu na základe vašich požiadaviek.
• Vstavané funkcie môžu prácu zvládnuť, ale vzorec je dlhý a komplikovaný. V takom prípade môžete vytvoriť vlastnú funkciu, ktorá sa ľahko číta a používa.

Všimnite si toho, že vlastné funkcie vytvorené pomocou VBA môžu byť výrazne pomalšie ako vstavané funkcie. Preto sú najvhodnejšie pre situácie, kde pomocou vstavaných funkcií nemôžete dosiahnuť výsledok.

Funkcia vs. Podprogram vo VBA

„Podprogram“ vám umožňuje vykonať sadu kódu, zatiaľ čo „funkcia“ vracia hodnotu (alebo pole hodnôt).

Aby sme vám poskytli príklad, ak máte zoznam čísiel (kladných aj záporných) a chcete identifikovať záporné čísla, tu je to, čo môžete urobiť s funkciou a podprogramom.

Podprogram môže prechádzať každou bunkou v rozsahu a môže zvýrazniť všetky bunky, ktoré majú zápornú hodnotu. V tomto prípade podprogram skončí zmenou vlastností objektu rozsahu (zmenou farby buniek).

S vlastnou funkciou ju môžete použiť v samostatnom stĺpci a môže vrátiť hodnotu TRUE, ak je hodnota v bunke záporná, a FALSE, ak je kladná. Pomocou funkcie nemôžete meniť vlastnosti objektu. To znamená, že nemôžete zmeniť farbu bunky pomocou samotnej funkcie (môžete to však urobiť pomocou podmieneného formátovania s vlastnou funkciou).

Keď vytvoríte funkciu definovanú používateľom (UDF) pomocou jazyka VBA, môžete túto funkciu použiť v pracovnom hárku rovnako ako všetky ostatné funkcie. Podrobnejšie sa tomu budem venovať v časti „Rôzne spôsoby použitia funkcie definovanej používateľom v programe Excel“.

Vytvorenie jednoduchej funkcie definovanej používateľom vo VBA

Dovoľte mi vytvoriť jednoduchú funkciu definovanú používateľom vo VBA a ukázať vám, ako funguje.

Nasledujúci kód vytvára funkciu, ktorá extrahuje číselné časti z alfanumerického reťazca.

Funkcia GetNumeric (CellRef As String) ako Long Dim StringLength Ako celé číslo StringLength = Len (CellRef) For i = 1 až StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Ďalej i GetNumeric = funkcia ukončenia výsledku

Keď máte vyššie uvedený kód v module, môžete túto funkciu použiť v zošite.

Nasleduje funkcia tejto funkcie - GetNumeric - je možné použiť v programe Excel.

Predtým, ako vám poviem, ako sa táto funkcia vo VBA vytvára a ako funguje, by ste mali vedieť niekoľko vecí:

• Keď vytvoríte funkciu vo VBA, bude k dispozícii v celom zošite rovnako ako všetky ostatné bežné funkcie.
• Keď zadáte názov funkcie a za ním znamienko rovná sa, Excel vám názov funkcie zobrazí v zozname zhodných funkcií. Keď som vo vyššie uvedenom príklade zadal = Získať, Excel mi ukázal zoznam, ktorý mal moju vlastnú funkciu.

Verím, že je to dobrý príklad, keď môžete použiť VBA na vytvorenie ľahko použiteľnej funkcie v programe Excel. To isté môžete urobiť aj so vzorcom (ako je uvedené v tomto návode), ale stáva sa to komplikovaným a ťažko pochopiteľným. S týmto UDF stačí odovzdať jeden argument a výsledok je jasný.

Anatómia funkcie definovanej používateľom vo VBA

Vo vyššie uvedenej časti som vám poskytol kód a ukázal som vám, ako funkcia UDF funguje v pracovnom hárku.

Teraz sa ponoríme do hĺbky a uvidíme, ako sa táto funkcia vytvára. Nasledujúci kód musíte vložiť do modulu v editore VB. Tejto téme sa venujem v sekcii - „Kam umiestniť kód VBA pre funkciu definovanú používateľom“.

Funkcia GetNumeric (CellRef As String) as Long 'Táto funkcia extrahuje číselnú časť z reťazca Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Výsledok a stred (CellRef, i, 1) Ďalej i GetNumeric = Funkcia ukončenia výsledku

Prvý riadok kódu začína slovom - Funkcia.

Toto slovo hovorí VBA, že náš kód je funkcia (a nie podprogram). Za slovom Funkcia nasleduje názov funkcie - GetNumeric. Toto je názov, ktorý budeme používať v pracovnom hárku na používanie tejto funkcie.

• Názov funkcie nemôže obsahovať medzery. Tiež nemôžete pomenovať funkciu, ak je v rozpore s názvom odkazu na bunku. Napríklad nemôžete pomenovať funkciu ABC123, pretože tiež odkazuje na bunku v hárku programu Excel.
• Funkcii by ste nemali dávať rovnaký názov ako existujúcej funkcii. Ak to urobíte, Excel bude uprednostňovať vstavanú funkciu.
• Ak chcete oddeliť slová, môžete použiť podčiarkovník. Prijateľný názov je napríklad Get_Numeric.

Za názvom funkcie nasledujú v zátvorke niektoré argumenty. Toto sú argumenty, ktoré by naša funkcia od používateľa potrebovala. Sú to rovnaké ako argumenty, ktoré musíme dodať vstavaným funkciám programu Excel. Napríklad vo funkcii COUNTIF existujú dva argumenty (rozsah a kritériá)

V zátvorke musíte zadať argumenty.

V našom prípade existuje iba jeden argument - CellRef.

Je tiež vhodné špecifikovať, aký argument funkcia funkcia očakáva. V tomto prípade, pretože pre funkciu budeme podávať referenciu na bunku, môžeme argument zadať ako typ „Rozsah“. Ak nezadáte typ údajov, VBA by ho považoval za variant (čo znamená, že môžete použiť akýkoľvek typ údajov).

Ak máte viac ako jeden argument, môžete ho zadať v rovnakej zátvorke - oddeliť čiarkou. Ďalej v tomto návode sa pozrieme na to, ako použiť viac argumentov v užívateľsky definovanej funkcii.

Všimnite si toho, že funkcia je špecifikovaná ako dátový typ „Reťazec“. To by VBA povedalo, že výsledok vzorca bude dátového typu String.

Aj keď tu môžem použiť numerický typ údajov (napríklad dlhý alebo dvojitý), obmedzilo by to rozsah čísel, ktoré môže vrátiť. Ak mám reťazec s dĺžkou 20 čísel, ktorý potrebujem extrahovať z celkového reťazca, vyhlásenie funkcie za dlhé alebo dvojité by znamenalo chybu (pretože číslo by bolo mimo jeho rozsah). Preto som ponechal dátový typ výstupu funkcie ako reťazec.

Druhý riadok kódu - ten zelený, ktorý začína apostrofom - je komentár. VBA pri čítaní kódu tento riadok ignoruje. Môžete to použiť na pridanie popisu alebo podrobností o kóde.

Tretí riadok kódu deklaruje premennú „StringLength“ ako celočíselný údajový typ. Toto je premenná, do ktorej ukladáme hodnotu dĺžky reťazca, ktorý je analyzovaný vzorcom.

Štvrtý riadok deklaruje premennú Výsledok ako typ údajov String. Toto je premenná, v ktorej extrahujeme čísla z alfanumerického reťazca.

Piaty riadok priradí dĺžku reťazca vo vstupnom argumente premennej „StringLength“. Upozorňujeme, že „CellRef“ odkazuje na argument, ktorý používateľ uvedie pri použití vzorca v pracovnom hárku (alebo pri použití vo VBA - čo uvidíme neskôr v tomto návode).

Šiesty, siedmy a ôsmy riadok sú súčasťou slučky For Next. Cyklus beží toľkokrát, koľkokrát je vo vstupnom argumente znak. Toto číslo je dané funkciou LEN a je priradené premennej „StringLength“.

Smyčka teda prebieha od „1 do dĺžky reťazca“.

V cykle príkaz IF analyzuje každý znak reťazca a ak je číselný, pridá tento číselný znak do premennej Výsledok. Na to používa funkciu MID vo VBA.

Druhý posledný riadok kódu priradí funkcii hodnotu výsledku. Je to tento riadok kódu, ktorý zaisťuje, že funkcia vráti hodnotu „Výsledok“ späť do bunky (odkiaľ sa volá).

Posledný riadok kódu je End Function. Toto je povinný riadok kódu, ktorý VBA hovorí, že tu funkčný kód končí.

Vyššie uvedený kód vysvetľuje rôzne časti typickej vlastnej funkcie vytvorenej vo VBA. V nasledujúcich častiach sa podrobne ponoríme do týchto prvkov a uvidíme aj rôzne spôsoby vykonávania funkcie VBA v programe Excel.

Argumenty vo funkcii definovanej používateľom vo VBA

Vo vyššie uvedených príkladoch, kde sme vytvorili používateľom definovanú funkciu na získanie numerickej časti z alfanumerického reťazca (GetNumeric), bola funkcia navrhnutá tak, aby používala jeden jediný argument.

V tejto časti sa budem zaoberať tým, ako vytvárať funkcie, ktoré neberú žiadny argument k tým, ktoré obsahujú viac argumentov (povinné aj voliteľné argumenty).

Vytvorenie funkcie vo VBA bez akýchkoľvek argumentov

V pracovnom hárku programu Excel máme niekoľko funkcií, ktoré nevyžadujú žiadne argumenty (napríklad RAND, TODAY, NOW).

Tieto funkcie nie sú závislé od žiadnych vstupných argumentov. Napríklad funkcia TODAY vráti aktuálny dátum a funkcia RAND vráti náhodné číslo medzi 0 a 1.

Takúto podobnú funkciu môžete vytvoriť aj vo VBA.

Nasleduje kód, ktorý vám poskytne názov súboru. Nevyžaduje žiadne argumenty, pretože výsledok, ktorý potrebuje vrátiť, nezávisí od žiadneho argumentu.

Funkcia WorkbookName () As String WorkbookName = ThisWorkbook.Name Koniec funkcie

Vyššie uvedený kód určuje výsledok funkcie ako dátový typ String (ako výsledok chceme názov súboru - čo je reťazec).

Táto funkcia priradí funkcii hodnotu „ThisWorkbook.Name“, ktorá sa vráti, keď sa funkcia použije v pracovnom hárku.

Ak bol súbor uložený, vráti názov s príponou súboru, v opačnom prípade názov iba uvedie.

Vyššie uvedené má však jeden problém.

Ak sa názov súboru zmení, neaktualizuje sa automaticky. Za normálnych okolností sa funkcia obnoví vždy, keď dôjde k zmene vstupných argumentov. Pretože však v tejto funkcii nie sú žiadne argumenty, funkcia sa neprepočítava (aj keď zmeníte názov zošita, zatvorte ho a potom znova otvorte).

Ak chcete, môžete vynútiť prepočet pomocou klávesovej skratky - Ctrl + Alt + F9.

Aby sa vzorec prepočítal vždy, keď dôjde k zmene v pracovnom hárku, musíte doň vložiť riadok kódu.

Nasledujúci kód spôsobuje, že sa funkcia prepočíta vždy, keď dôjde k zmene v pracovnom hárku (rovnako ako ostatné podobné funkcie pracovného hárka, ako napríklad DNES alebo funkcia RAND).

Funkcia WorkbookName () ako reťazcová aplikácia.Volatile True WorkbookName = ThisWorkbook.Name Koniec funkcie

Ak teraz zmeníte názov zošita, táto funkcia sa aktualizuje vždy, keď dôjde k akejkoľvek zmene v pracovnom hárku alebo keď tento zošit znova otvoríte.

Vytvorenie funkcie vo VBA s jedným argumentom

V jednej z vyššie uvedených sekcií sme už videli, ako vytvoriť funkciu, ktorá používa iba jeden argument (vyššie uvedená funkcia GetNumeric).

Vytvorme ďalšiu jednoduchú funkciu, ktorá používa iba jeden argument.

Funkcia vytvorená pomocou nižšie uvedeného kódu by skonvertovala odkazovaný text na veľké písmená. Teraz už v Exceli na to máme funkciu a táto funkcia vám len ukáže, ako funguje. Ak to musíte urobiť, je lepšie použiť vstavanú funkciu HORNÁ.

Funkcia ConvertToUpperCase (CellRef ako rozsah) ConvertToUpperCase = UCase (CellRef) Koncová funkcia

Táto funkcia používa funkciu UCase vo VBA na zmenu hodnoty premennej CellRef. Potom priradí hodnotu funkcii ConvertToUpperCase.

Pretože táto funkcia vyžaduje argument, nepotrebujeme tu používať časť Application.Volatile. Hneď ako sa argument zmení, funkcia sa automaticky aktualizuje.

Vytvorenie funkcie vo VBA s viacerými argumentmi

Rovnako ako funkcie pracovného hárka, môžete vo VBA vytvárať funkcie, ktoré vyžadujú viac argumentov.

Nasledujúci kód by vytvoril funkciu, ktorá extrahuje text pred zadaný oddeľovač. Na to sú potrebné dva argumenty - odkaz na bunku s textovým reťazcom a oddeľovač.

Funkcia GetDataBeforeDelimiter (CellRef ako rozsah, Delim ako reťazec) ako reťazec Dim Výsledok ako reťazec Dim DelimPosition Ako celé číslo DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 výsledok = vľavo (CellRef, DelimPosition) GetDataBeultDelimit

Ak v používateľsky definovanej funkcii potrebujete použiť viac ako jeden argument, môžete v zátvorke oddeliť všetky argumenty čiarkou.

Všimnite si toho, že pre každý argument môžete zadať typ údajov. Vo vyššie uvedenom príklade bol „CellRef“ deklarovaný ako dátový typ rozsahu a „Delim“ bol deklarovaný ako typ údajov String. Ak nezadáte žiadny typ údajov, VBA sa domnieva, že ide o variantný typ údajov.

Keď v pracovnom hárku použijete vyššie uvedenú funkciu, musíte ako prvý argument uviesť odkaz na bunku, ktorá má ako prvý argument text a oddeľovacie znaky v úvodzovkách.

Potom pomocou funkcie INSTR vo VBA skontroluje polohu oddeľovača. Táto pozícia sa potom použije na extrahovanie všetkých znakov pred oddeľovač (pomocou funkcie VĽAVO).

Nakoniec funkcii priradí výsledok.

Tento vzorec nie je ani zďaleka dokonalý. Ak napríklad zadáte oddeľovač, ktorý sa v texte nenachádza, zobrazí sa chyba. Teraz môžete na odstránenie chýb použiť funkciu IFERROR v pracovnom hárku alebo môžete použiť nižšie uvedený kód, ktorý vráti celý text, keď nemôže nájsť oddeľovač.

Funkcia GetDataBeforeDelimiter (CellRef ako rozsah, Delim ako reťazec) ako reťazec Dim Výsledok ako reťazec Dim DelimPosition Ako celé číslo DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Ak DelimPosition <0 Potom DelimPosition = Len (CellRef) Výsledok = Left CellRef, DelimPosition) GetDataBeforeDelimiter = Funkcia ukončenia výsledku

Túto funkciu môžeme ďalej optimalizovať.

Ak zadáte text (z ktorého chcete extrahovať časť pred oddeľovačom) priamo do funkcie, zobrazila by sa vám chyba. Len do toho … vyskúšajte!

Stáva sa to, pretože sme ako typ údajov rozsahu zadali „CellRef“.

Alebo, ak chcete, aby bol oddeľovač v bunke a namiesto pevného kódovania vo vzorci používal odkaz na bunku, s vyššie uvedeným kódom to nemôžete urobiť. Je to preto, že Delim bol deklarovaný ako reťazcový dátový typ.

Ak chcete, aby mala funkcia flexibilitu na to, aby od používateľa prijímala priame zadávanie textu alebo odkazy na bunky, musíte odstrániť deklaráciu typu údajov. To by skončilo tým, že by bol argument ako variantný dátový typ, ktorý môže obsahovať akýkoľvek typ argumentu a spracovať ho.

Nasledujúci kód by to urobil:

Funkcia GetDataBeforeDelimiter (CellRef, Delim) As String Dim Výsledok ako String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition <0 Then DelimPosition = Len (CellRef) Result = Left (CellRef, GetDataBeforeDelimiter = Funkcia ukončenia výsledku

Vytvorenie funkcie vo VBA s voliteľnými argumentmi

V programe Excel existuje mnoho funkcií, kde sú niektoré argumenty voliteľné.

Legendárna funkcia VLOOKUP má napríklad 3 povinné argumenty a jeden voliteľný argument.

Voliteľný argument, ako naznačuje názov, je voliteľný. Ak nezadáte jeden z povinných argumentov, vaša funkcia vám spôsobí chybu, ale ak nezadáte voliteľný argument, vaša funkcia bude fungovať.

Voliteľné argumenty však nie sú zbytočné. Umožňujú vám vybrať si z množstva možností.

Ak napríklad vo funkcii VLOOKUP nezadáte štvrtý argument, funkcia VLOOKUP vykoná približné vyhľadávanie a ak zadáte posledný argument ako NEPRAVDU (alebo 0), bude zodpovedať presnej zhode.

Pamätajte si, že voliteľné argumenty musia vždy nasledovať za všetkými požadovanými argumentmi. Na začiatku nemôžete mať voliteľné argumenty.

Teraz sa pozrime, ako vytvoriť funkciu vo VBA pomocou voliteľných argumentov.

Funkcia iba s voliteľným argumentom

Pokiaľ viem, neexistuje vstavaná funkcia, ktorá by používala iba voliteľné argumenty (tu sa môžem mýliť, ale na žiadnu takú funkciu nemôžem myslieť).

Ale môžeme ho vytvoriť pomocou VBA.

Nasleduje kód funkcie, ktorá vám poskytne aktuálny dátum vo formáte dd-mm-rrrr, ak nezadáte žiaden argument (tj. Ponecháte prázdne), a vo formáte „dd mmmm, rrrr“, ak zadáte čokoľvek ako argument (tj. čokoľvek, aby argument nebol prázdny).

Funkcia CurrDate (voliteľné fmt ako variant) Dim Výsledok Ak chýba (fmt) Then CurrDate = Format (Date, "dd-mm-yyyy") Else CurrDate = Format (Date, "dd mmmm, yyyy") End If End Function

Všimnite si toho, že vyššie uvedená funkcia používa „IsMissing“ na kontrolu, či argument chýba alebo nie. Ak chcete používať funkciu IsMissing, váš voliteľný argument musí byť variantného dátového typu.

Vyššie uvedená funkcia funguje bez ohľadu na to, čo zadáte ako argument. V kóde kontrolujeme iba to, či je zadaný voliteľný argument alebo nie.

Môžete to urobiť robustnejším tým, že ako argumenty použijete iba konkrétne hodnoty a vo zvyšných prípadoch zobrazíte chybu (ako je uvedené v nižšie uvedenom kóde).

Funkcia CurrDate (voliteľné fmt ako variant) Dim Výsledok Ak chýba (fmt) Then CurrDate = Format (Date, "dd-mm-yyyy") ElseIf fmt = 1 Then CurrDate = Format (Date, "dd mmmm, yyyy") Else CurrDate = CVErr (xlErrValue) Funkcia End If End

Vyššie uvedený kód vytvára funkciu, ktorá zobrazuje dátum vo formáte „dd-mm-rrrr“, ak nie je zadaný žiadny argument, a vo formáte „dd mmmm, rrrr“, ak je argument 1. Vo všetkých ostatných prípadoch ide o chybu.

Funkcia s požadovanými aj voliteľnými argumentmi

Už sme videli kód, ktorý extrahuje číselnú časť z reťazca.

Teraz sa pozrime na podobný príklad, ktorý berie požadované aj voliteľné argumenty.

Nasledujúci kód vytvára funkciu, ktorá extrahuje textovú časť z reťazca. Ak je voliteľný argument PRAVDA, dáva výsledok veľkými písmenami a ak je voliteľný argument NEPRAVDA alebo je vynechaný, výsledok bude taký, ako je.

Funkcia GetText (CellRef ako rozsah, voliteľný TextCase = False) Ako reťazec Dim StringLength ako celé číslo Dim Výsledok ako reťazec StringLength = Len (CellRef) Pre i = 1 až StringLength Ak nie (IsNumeric (Mid (CellRef, i, 1))) Then Výsledok = Výsledok a stred (CellRef, i, 1) Ďalej i Ak TextCase = True, potom Výsledok = UCase (Výsledok) GetText = Ukončiť funkciu

Všimnite si toho, že vo vyššie uvedenom kóde sme inicializovali hodnotu ‘TextCase’ ako False (pozrite sa do zátvorky v prvom riadku).

Tým sme zaistili, že voliteľný argument začína predvolenou hodnotou, ktorou je FALSE. Ak používateľ zadá hodnotu ako PRAVDU, funkcia vráti text veľkými písmenami a ak používateľ uvedie voliteľný argument ako FALSE alebo ho vynechá, potom bude vrátený text taký, aký je.

Vytvorenie funkcie vo VBA s poľom ako argumentom

Doteraz sme videli príklady vytvorenia funkcie pomocou voliteľných/požadovaných argumentov - kde tieto argumenty predstavovali jednu hodnotu.

Môžete tiež vytvoriť funkciu, ktorá môže považovať za argument pole. V funkciách pracovného hárka programu Excel existuje mnoho funkcií, ktoré preberajú argumenty poľa, napríklad SUM, VLOOKUP, SUMIF, COUNTIF atď.

Nasleduje kód, ktorý vytvára funkciu, ktorá dáva súčet všetkých párnych čísel v určenom rozsahu buniek.

Funkcia AddEven (CellRef ako 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 Funkcia

Túto funkciu môžete použiť v pracovnom hárku a zadať rozsah buniek, ktorých argumentom sú čísla. Funkcia by vrátila jednu hodnotu - súčet všetkých párnych čísel (ako je uvedené nižšie).

Vo vyššie uvedenej funkcii sme namiesto jednej hodnoty dodali pole (A1: A10). Aby to fungovalo, musíte sa uistiť, že váš dátový typ argumentu môže akceptovať pole.

Vo vyššie uvedenom kóde som zadal argument CellRef ako rozsah (ktorý môže mať vstup ako pole). Môžete tu tiež použiť variantný typ údajov.

V kóde je slučka For Every, ktorá prechádza každou bunkou a kontroluje, či je číslo nie. Ak nie je, nič sa nestane a presunie sa do ďalšej bunky. Ak je to číslo, skontroluje, či je rovnomerné alebo nie (pomocou funkcie MOD).

Nakoniec sa sčítajú všetky párne čísla a súčet sa priradí späť k funkcii.

Vytvorenie funkcie s neurčitým počtom argumentov

Pri vytváraní niektorých funkcií vo VBA možno nebudete vedieť presný počet argumentov, ktoré chce používateľ zadať. Preto je potrebné vytvoriť funkciu, ktorá bude akceptovať zadané množstvo argumentov a pomocou nich vráti výsledok.

Príkladom takejto funkcie pracovného hárka je funkcia SUM. Môžete k nemu zadať viacero argumentov (napríklad tento):

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

Vyššie uvedená funkcia by pridala hodnoty do všetkých týchto argumentov. Všimnite si tiež, že to môže byť jedna bunka alebo pole buniek.

Vo VBA môžete takú funkciu vytvoriť tak, že posledný argument (alebo to môže byť jediný argument) bude nepovinný. Tomuto voliteľnému argumentu by malo tiež predchádzať kľúčové slovo „ParamArray“.

„ParamArray“ je modifikátor, ktorý vám umožňuje prijať toľko argumentov, koľko chcete. Všimnite si toho, že ak použijete slovo ParamArray pred argumentom, argument bude nepovinný. Tu však nemusíte používať slovo Voliteľné.

Teraz vytvoríme funkciu, ktorá môže akceptovať ľubovoľný počet argumentov a pridala by všetky čísla do zadaných argumentov:

Funkcia AddArguments (ParamArray arglist () ako variant) Pre každý arg In arglist AddArguments = AddArguments + arg Next arg Ukončiť funkciu

Vyššie uvedená funkcia môže mať ľubovoľný počet argumentov a pridať tieto argumenty, aby poskytla výsledok.

Upozorňujeme, že ako argument môžete použiť iba jednu hodnotu, odkaz na bunku, logickú hodnotu alebo výraz. Ako argument nemôžete zadať pole. Ak je napríklad jeden z vašich argumentov D8: D10, tento vzorec by vám spôsobil chybu.

Ak chcete používať obidva viacbunkové argumenty, musíte použiť nasledujúci kód:

Funkcia AddArguments (ParamArray arglist () ako variant) Pre každý arg In arglist Pre každú bunku v arg AddArguments = AddArguments + bunka Nasledujúca bunka Nasledujúca arg Koniec Funkcia

Všimnite si toho, že tento vzorec funguje s viacerými bunkami a odkazmi na pole, nemôže však spracovávať pevne zakódované hodnoty alebo výrazy. Robustnejšou funkciou môžete vytvoriť kontrolu a ošetrenie týchto podmienok, ale o to tu nejde.

Cieľom je tu ukázať vám, ako ParamArray funguje, aby ste vo funkcii mohli povoliť neobmedzený počet argumentov. V prípade, že chcete lepšiu funkciu, ako je funkcia vytvorená vyššie uvedeným kódom, použite v pracovnom hárku funkciu SUMA.

Vytvorenie funkcie, ktorá vráti pole

Doteraz sme videli funkcie, ktoré vracajú jednu hodnotu.

Pomocou VBA môžete vytvoriť funkciu, ktorá vráti variant, ktorý môže obsahovať celé pole hodnôt.

Maticové vzorce sú k dispozícii aj ako vstavané funkcie v pracovných hárkoch programu Excel. Ak poznáte vzorce v programe Excel, vedeli by ste, že sa zadávajú pomocou klávesov Ctrl + Shift + Enter (namiesto klávesu Enter). Viac informácií o vzorcoch polí si môžete prečítať tu. Ak neviete o vzorcoch polí, nebojte sa, čítajte ďalej.

Vytvoríme vzorec, ktorý vráti pole troch čísel (1,2,3).

Nasledujúci kód by to urobil.

Funkcia ThreeNumbers () ako variant Dim DimValue (1 až 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue End Function

Vo vyššie uvedenom kóde sme ako variant uviedli funkciu „Tri čísla“. To mu umožňuje uchovávať množstvo hodnôt.

Premenná „NumberValue“ je deklarovaná ako pole s 3 prvkami. Obsahuje tri hodnoty a priradí ich k funkcii „Tri čísla“.

Túto funkciu môžete použiť v pracovnom hárku tak, že zadáte funkciu a stlačíte kláves Control + Shift + Enter (podržte klávesy Control a Shift a potom stlačte kláves Enter).

Keď to urobíte, vráti 1 do bunky, ale v skutočnosti obsahuje všetky tri hodnoty. Ak to chcete skontrolovať, použite nasledujúci vzorec:

= MAX (tri čísla ())

Vyššie uvedenú funkciu použite pomocou klávesov Control + Shift + Enter. Všimnite si, že výsledok je teraz 3, pretože ide o najväčšie hodnoty v poli vrátenom funkciou Max, ktorá získa tri čísla ako výsledok našej používateľom definovanej funkcie - ThreeNumbers.

Rovnakú techniku ​​môžete použiť na vytvorenie funkcie, ktorá vracia pole názvov mesiacov, ako ukazuje nasledujúci kód:

Funkcia Mesiace () Ako variant Dim DimMeno (1 až 12) MonthName (1) = "Január" MonthName (2) = "Február" MonthName (3) = "Marec" MonthName (4) = "Apríl" MonthName (5) = "Máj" MonthName (6) = "Jún" MonthName (7) = "Júl" MonthName (8) = "August" MonthName (9) = "September" MonthName (10) = "Október" MonthName (11) = "November "MonthName (12) =" December "Mesiace = MonthName Koniec funkcie

Keď teraz zadáte funkciu = Mesiace () do pracovného hárka programu Excel a použijete kombináciu klávesov Control + Shift + Enter, vráti celý rad názvov mesiacov. Všimnite si toho, že v bunke vidíte iba január, pretože to je prvá hodnota v poli. To neznamená, že pole vracia iba jednu hodnotu.

Ak chcete ukázať, že vracia všetky hodnoty, urobte to - vyberte bunku so vzorcom, prejdite na panel vzorcov, vyberte celý vzorec a stlačte kláves F9. To vám ukáže všetky hodnoty, ktoré funkcia vracia.

Môžete to použiť pomocou nižšie uvedeného vzorca INDEX na získanie zoznamu všetkých názvov mesiacov naraz.

= INDEX (mesiace (), ROW ())

Teraz, keď máte veľa hodnôt, nie je vhodné priradiť tieto hodnoty jednu po druhej (ako sme to urobili vyššie). Namiesto toho môžete vo VBA použiť funkciu Array.

Rovnaký kód, kde vytvoríme funkciu „Mesiace“, by sa teda skrátil, ako je uvedené nižšie:

Mesiace funkcie () Ako rôzne mesiace = pole („január“, „február“, „marec“, „apríl“, „máj“, „jún“, _ „júl“, „august“, „september“, „október“ , „November“, „December“) Ukončiť funkciu

Vyššie uvedená funkcia používa funkciu Array na priradenie hodnôt priamo k funkcii.

Všimnite si toho, že všetky funkcie vytvorené vyššie vracajú horizontálne pole hodnôt. To znamená, že ak vyberiete 12 horizontálnych buniek (povedzme A1: L1) a do bunky A1 zadáte vzorec = Mesiace (), poskytne vám všetky názvy mesiacov.

Ale čo keď chcete tieto hodnoty vo vertikálnom rozsahu buniek.

Môžete to urobiť pomocou vzorca TRANSPOSE v pracovnom hárku.

Jednoducho vyberte 12 zvislých buniek (susediacich) a zadajte nasledujúci vzorec.

Pochopenie rozsahu funkcie definovanej používateľom v programe Excel

Funkcia môže mať dva rozsahy - Verejné alebo Súkromné.

• A Verejná pôsobnosť znamená, že funkcia je k dispozícii pre všetky listy v zošite, ako aj pre všetky postupy (čiastkové a funkcie) pre všetky moduly v zošite. Je to užitočné, keď chcete zavolať funkciu z podprogramu (uvidíme, ako sa to robí v ďalšej časti).
• A Súkromný rozsah znamená, že funkcia je k dispozícii iba v module, v ktorom existuje. Nemôžete ho použiť v iných moduloch. Tiež ho neuvidíte v zozname funkcií v pracovnom hárku. Ak je napríklad názov vašej funkcie „Mesiace ()“ a zadáte funkciu v programe Excel (za znamienkom =), názov funkcie sa vám nezobrazí. Môžete ho však stále používať, ak zadáte názov vzorca.

Ak nič neurčíte, funkcia je predvolene verejná funkcia.

Nasleduje funkcia, ktorá je súkromnou funkciou:

Súkromná funkcia WorkbookName () As String WorkbookName = ThisWorkbook.Name Koniec funkcie

Túto funkciu môžete použiť v podprogramoch a postupoch v rovnakých moduloch, ale nemôžete ju použiť v iných moduloch. Táto funkcia by sa tiež nezobrazila v pracovnom hárku.

Nasledujúci kód by urobil túto funkciu verejnou. Zobrazí sa aj v pracovnom liste.

Funkcia WorkbookName () As String WorkbookName = ThisWorkbook.Name Koniec funkcie

Rôzne spôsoby použitia používateľom definovanej funkcie v programe Excel

Keď vo VBA vytvoríte používateľom definovanú funkciu, môžete ju používať mnohými rôznymi spôsobmi.

Najprv sa pozrime na to, ako používať funkcie v pracovnom hárku.

Použitie UDF v pracovných listoch

V pracovnom hárku sme už videli príklady použitia funkcie vytvorenej vo VBA.

Všetko, čo musíte urobiť, je zadať názov funkcií a zobrazí sa v inteligentnom jazyku.

Upozorňujeme, že na to, aby sa funkcia zobrazovala v pracovnom hárku, musí ísť o verejnú funkciu (ako je vysvetlené v sekcii vyššie).

Na vloženie funkcie definovanej používateľom môžete tiež použiť dialógové okno Vložiť funkciu (pomocou nižšie uvedených krokov). To by fungovalo iba pre funkcie, ktoré sú verejné.

• Prejdite na kartu Údaje.
• Kliknite na možnosť „Vložiť funkciu“.
• V dialógovom okne Vložiť funkciu vyberte ako kategóriu definovanú používateľom. Táto možnosť sa zobrazí iba vtedy, ak máte funkciu v editore VB (a funkcia je verejná).
• Vyberte funkciu zo zoznamu všetkých verejných funkcií definovaných používateľom.
• Kliknite na tlačidlo Ok.

Vyššie uvedené kroky by vložili funkciu do pracovného hárka. Zobrazí sa tiež dialógové okno Argumenty funkcií, ktoré vám poskytne podrobné informácie o argumentoch a výsledku.

Užívateľom definovanú funkciu môžete používať rovnako ako každú inú funkciu v programe Excel. To tiež znamená, že ho môžete používať s inými vstavanými funkciami programu Excel. Napríklad. Nasledujúci vzorec by poskytol názov zošita veľkými písmenami:

= HORNÁ (názov zošita ())

Používanie funkcií definovaných používateľom v procedúrach a funkciách VBA

Keď vytvoríte funkciu, môžete ju použiť aj v iných čiastkových procedúrach.

Ak je funkcia verejná, môže byť použitá v akejkoľvek procedúre v rovnakom alebo inom module. Ak je súkromný, môže byť použitý iba v tom istom module.

Nasleduje funkcia, ktorá vracia názov zošita.

Funkcia WorkbookName () As String WorkbookName = ThisWorkbook.Name Koniec funkcie

Nasledujúci postup volá funkciu a potom zobrazí meno v okne správy.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

Funkciu môžete zavolať aj z inej funkcie.

V nižšie uvedených kódoch prvý kód vráti názov zošita a druhý vráti meno veľkými písmenami zavolaním prvej funkcie.

Funkcia WorkbookName () As String WorkbookName = ThisWorkbook.Name Koniec funkcie

Funkcia WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) Koniec funkcie

Volanie funkcie definovanej používateľom z iných zošitov

Ak máte funkciu v zošite, môžete ju nazvať aj v iných zošitoch.

Existuje niekoľko spôsobov, ako to urobiť:

1. Vytvorenie doplnku
2. Funkcia ukladania do osobného zošita makra
3. Odkazovanie na funkciu z iného zošita.

Vytvorenie doplnku

Vytvorením a inštaláciou doplnku budete mať v ňom vlastnú funkciu k dispozícii vo všetkých zošitoch.

Predpokladajme, že ste vytvorili vlastnú funkciu - „GetNumeric“ a chcete ju vo všetkých zošitoch. Ak to chcete urobiť, vytvorte nový zošit a kód funkcie vložte do modulu v tomto novom zošite.

Teraz postupujte podľa nižšie uvedených krokov a uložte ho ako doplnok a potom ho nainštalujte do Excelu.

• Prejdite na kartu Súbor a kliknite na položku Uložiť ako.
• V dialógovom okne Uložiť ako zmeňte typ „Uložiť ako“ na .xlam. Názov, ktorý súboru priradíte, bude názov vášho doplnku. V tomto prípade je súbor uložený s názvom GetNumeric.
  • Všimnite si, že cesta k súboru, kam sa uloží, sa automaticky zmení. Môžete použiť predvolený alebo ho zmeniť, ak chcete.
• Otvorte nový zošit programu Excel a prejdite na kartu Vývojár.
• Kliknite na možnosť Doplnky programu Excel.
• V dialógovom okne Doplnky vyhľadajte a vyhľadajte súbor, ktorý ste uložili, a kliknite na tlačidlo OK.

Teraz bol doplnok aktivovaný.

Teraz môžete vlastnú funkciu používať vo všetkých zošitoch.

Uloženie funkcie do osobného zošita makra

Osobný zošit makra je skrytý zošit vo vašom systéme, ktorý sa otvára vždy, keď otvoríte aplikáciu Excel.

Je to miesto, kde môžete ukladať kódy makier a potom k nim pristupovať z akéhokoľvek zošita. Je to skvelé miesto na ukladanie makier, ktoré chcete často používať.

V predvolenom nastavení neexistuje vo vašom Exceli žiadny osobný zošit makier. Musíte ho vytvoriť zaznamenaním makra a jeho uložením do zošita Osobné makro.

Podrobný postup vytvárania a ukladania makier v osobnom zošite makier nájdete tu.

Odkazovanie na funkciu z iného zošita

Kým prvé dve metódy (vytvorenie doplnku a použitie osobného zošita makier) budú fungovať vo všetkých situáciách, ak chcete na funkciu odkazovať z iného zošita, tento zošit musí byť otvorený.

Predpokladajme, že máte pracovný zošit s názvom „Zošit so vzorcom, a má funkciu s názvom „GetNumeric '.

Ak chcete použiť túto funkciu v inom zošite (zatiaľ čo Pracovný zošit so vzorcom je otvorený), môžete použiť nasledujúci vzorec:

= „Zošit so vzorcom“! Získajte číselné údaje (A1)

Vyššie uvedený vzorec bude používať používateľom definovanú funkciu v súbore Pracovný zošit so vzorcom súbor a poskytne vám výsledok.

Všimnite si toho, pretože názov zošita obsahuje medzery, musíte ho uzavrieť do jednoduchých úvodzoviek.

Použitie príkazu na ukončenie funkcie VBA

Ak chcete ukončiť funkciu, keď je kód spustený, môžete to urobiť pomocou príkazu „Ukončiť funkciu“.

Nasledujúci kód by extrahoval prvé tri číselné znaky z alfanumerického textového reťazca. Hneď ako získa tri znaky, funkcia sa skončí a vráti výsledok.

Funkcia 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 Výsledok = Výsledok a stred (CellRef, i, 1) GetNumericFirstThree = Výsledok Koniec Ak Ďalej i Koniec Funkcia

Vyššie uvedená funkcia kontroluje počet znakov, ktoré sú číselné, a keď získa 3 číselné znaky, ukončí funkciu v ďalšej slučke.

Ladenie funkcie definovanej používateľom

Pri ladení funkcie definovanej používateľom vo VBA môžete použiť niekoľko techník:

Ladenie vlastnej funkcie pomocou poľa so správou

Na zobrazenie poľa so správou s konkrétnou hodnotou použite funkciu MsgBox.

Hodnota, ktorú zobrazíte, môže byť založená na tom, čo chcete testovať. Napríklad, ak chcete skontrolovať, či sa kód vykonáva alebo nie, bude fungovať akákoľvek správa a ak chcete skontrolovať, či slučky fungujú alebo nie, môžete zobraziť konkrétnu hodnotu alebo počítadlo slučiek.

Ladenie vlastnej funkcie nastavením bodu prerušenia

Nastavte bod zlomu, aby ste mohli prejsť krok za krokom každý riadok. Ak chcete nastaviť zarážku, vyberte riadok na požadovanom mieste a stlačte kláves F9 alebo kliknite na sivú zvislú oblasť, ktorá je vľavo od riadkov kódu. Každá z týchto metód by vložila zarážku (v sivej oblasti uvidíte červenú bodku).

Keď ste nastavili bod prerušenia a vykonali ste funkciu, prejde na čiaru bodu prerušenia a potom sa zastaví. Teraz môžete v kóde postupovať pomocou klávesu F8. Jedným stlačením klávesu F8 sa presuniete na nasledujúci riadok kódu.

Ladenie vlastnej funkcie pomocou Debug.Print v kóde

Na získanie hodnôt zadaných premenných/argumentov v bezprostrednom okne môžete vo svojom kóde použiť príkaz Debug.Print.

Napríklad v nižšie uvedenom kóde som použil Debug.Print na získanie hodnoty dvoch premenných - „j“ a „Výsledok“

Funkcia 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 Výsledok = Výsledok a stred (CellRef, i, 1) Debug.Print J, Výsledok GetNumericFirstThree = Výsledok Koniec Ak Ďalej i Koniec Funkcia

Keď sa tento kód spustí, v bezprostrednom okne sa zobrazí nasledujúce.

Vstavané funkcie programu Excel vs. Užívateľom definovaná funkcia VBA

Používanie vstavaných funkcií Excelu oproti vlastným funkciám vytvoreným vo VBA má niekoľko silných výhod.

• Vstavané funkcie sú oveľa rýchlejšie ako funkcie VBA.
• Keď vytvoríte zostavu/informačný panel pomocou funkcií VBA a odošlete ich klientovi/kolegovi, nemusí sa starať o to, či sú makrá povolené alebo nie. V niektorých prípadoch sa klienti/zákazníci desia tým, že sa im v žltom pruhu zobrazí upozornenie (ktoré ich jednoducho požiada o povolenie makier).
• Vďaka vstavaným funkciám Excelu si nemusíte robiť starosti s príponami súborov. Ak máte v zošite makrá alebo užívateľom definované funkcie, musíte ho uložiť vo formáte .xlsm.

Aj keď existuje veľa pádnych dôvodov na používanie vstavaných funkcií programu Excel, v niektorých prípadoch je lepšie používať funkciu definovanú používateľom.

• Je lepšie použiť funkciu definovanú používateľom, ak je váš vstavaný vzorec obrovský a komplikovaný. Toto je ešte dôležitejšie, keď potrebujete niekoho iného, ​​aby aktualizoval vzorce. Ak máte napríklad obrovský vzorec tvorený mnohými rôznymi funkciami, dokonca aj zmena odkazu na bunku môže byť únavná a náchylná na chyby. Namiesto toho môžete vytvoriť vlastnú funkciu, ktorá bude obsahovať iba jeden alebo dva argumenty a bude musieť vykonať všetky kroky, ktoré je potrebné vykonať.
• Keď musíte urobiť niečo, čo nie je možné vykonať vstavanými funkciami programu Excel. Príkladom toho môže byť situácia, keď chcete z reťazca extrahovať všetky číselné znaky. V takýchto prípadoch prospech z použitia funkcie definovanej používateľom Gar preváži jeho negatíva.

Kam umiestniť kód VBA pre funkciu definovanú používateľom

Pri vytváraní vlastnej funkcie musíte vložiť kód do okna kódu pre zošit, v ktorom chcete funkciu použiť.

Nasledujú kroky na vloženie kódu pre funkciu „GetNumeric“ do zošita.

1. Prejdite na kartu Vývojár.
2. Kliknite na možnosť Visual Basic. Tým sa otvorí editor VB v backende.
3. Na table Project Explorer v editore VB kliknite pravým tlačidlom myši na ľubovoľný objekt zošita, do ktorého chcete vložiť kód. Ak sa Project Explorer nezobrazuje, choďte na kartu Zobraziť a kliknite na Project Explorer.
4. Prejdite na položku Vložiť a kliknite na modul. Tým sa vloží objekt modulu do vášho zošita.
5. Skopírujte a prilepte kód do okna modulu.

Tiež by sa vám mohli páčiť nasledujúce návody na Excel VBA:

• Práca s bunkami a rozsahmi v programe Excel VBA.
• Práca s pracovnými listami v programe Excel VBA.
• Práca so zošitmi pomocou VBA.
• Ako používať slučky v programe Excel VBA.
• Udalosti programu Excel VBA - ľahký (a kompletný) sprievodca
• Použitie príkazov IF Then Else vo VBA.
• Ako zaznamenať makro v programe Excel.
• Ako spustiť makro v programe Excel.
• Ako zoradiť údaje v programe Excel pomocou VBA (Podrobný sprievodca).
• Funkcia Excel VBA InStr - vysvetlená s príkladmi.