Operátory a funkce vba excel. Funkce-procedury VBA. Použití uživatelských funkcí v procedurách a funkcích VBA

Aritmetické výrazy Visual Basic.

Teoretická část

Výrazy a funkce

LABORATORNÍ PRÁCE №4. Práce s aritmetikou

Otázky ke kontrole

1. Které komponenty vám umožňují vytvářet statické obrázky ve vaší aplikaci?

2. Jak vložit obrázek na formulář? Jaké metody pro tento účel existují a jak se liší?

3. Obrázky v jakém formátu se může komponenta Image připojit k aplikaci?

4. Která vlastnost objektu Image je zodpovědná za změnu měřítka? Pro viditelnost obrázku?

Cíl práce:seznámit se s aritmetickými výrazy a matematickými funkcemi jazyka Visual Basic; naučit se vyvíjet aplikace pomocí výrazů a matematických funkcí

Výrazy se používají pro operace s daty. V závislosti na datech a použitých operacích se výrazy dělí na aritmetický, logický a symbolický

Aritmetické výrazy je posloupnost čísel, konstant, proměnných, funkcí, které jsou vzájemně propojeny znaménky aritmetických operací. Speciálním případem výrazu je jednoduše konstanta, proměnná nebo volání funkce.

Aritmetické operace jazyka Visual Basic jsou uvedeny v tabulce 4.1.

Tabulka 4.1 - Aritmetické operace jazyka Visual Basic

Priorita provádět operace (v sestupném pořadí): umocňování, násobení a dělení, dělení celých čísel, výpočet zbytku, sčítání a odčítání. Výpočty se provádějí zleva doprava. Závorky mění prioritu.

Příklady:

14 /5*2 = 5,6 – operace se stejnou prioritou se provádějí zleva doprava

14\5*2 = 1 - násobení má vyšší prioritu a zlomková část se při dělení celého čísla zahodí

27^1/3 = 9 - umocnění má nejvyšší prioritu

27^(1/3) = 3 - závorky mění pořadí operací

Visual Basic má širokou škálu vložené (Standard) funkce, které usnadňují psaní programů. Jsou zde matematické funkce, funkce pro zpracování řetězců, funkce pro práci s daty a časy a finanční funkce.

Matematické funkce:

Abs (x) - vypočítá modul argumentu

Atn (x) - vypočítá arkus tangens čísla x

Sin (x) - vypočítá sinus čísla x

Cos (x) - vypočítá kosinus čísla x

Tan (x) - vypočítá tangens čísla x

Exp (x) - vypočítá e^x

Log (x) - vypočítá přirozený logaritmus

Sqr(x) – Vypočítá druhou odmocninu

rnd() - vrací náhodné číslo mezi 0 a 1

Int(x) - Vypočítá největší celé číslo, které není větší než x

Oprava (x) – vypočítá zaokrouhlené číslo (zahodí zlomkovou část argumentu)

CInt(x) - vrátí argument zaokrouhlený na celé číslo podle pravidel matematického zaokrouhlování

Sgn (x) - vrací znaménko čísla

X^(1/n) - extrahuje n-tou odmocninu X

Hex (x) - převede desetinné číslo na šestnáctkové

Oct(x) - převede desetinné číslo na osmičkové

Příklady zaokrouhlování čísel:

Int(7,32) = 7 CInt(5,5) = 6 Fix(-7,32) = -7

Int(-7,32) = -8 CInt(-7,5) = -7

Hodnoty argumentů v goniometrických funkcích jsou uvedeny v radiánech!

Chcete-li převést argument ze stupňů na radiány, použijte vzorec:

Argument (v radiánech) = Argument (ve stupních)*PI / 180

Počet PI lze určit podle vzorce:

PI = Atn(1)*4

Úplné informace o vestavěných funkcích a jejich použití naleznete v systému nápovědy jazyka Visual Basic.

Téměř veškerý programový kód modulů VBA je obsažen v procedurách dvou typů Sub (podprogramy) a Function (funkce). Hlavním úkolem procedury Function je vypočítat nějakou hodnotu a vrátit ji do bodu, kde byla procedura funkce volána.

Syntaxe funkce procedura-funkce:

Funkce NázevFunkce(argumenty As) As

Function_Name = Return_Value

koncová funkce

Funkční procedury lze použít v různých výrazech.

Příklad 1

Například nejjednodušším postupem je funkce Function:

Funkce F1(x jako měna) Jako měna

Funkci F1(x) lze použít v dalších výpočtech (programový kód modulu). Procedura typu Funkce může být provedena pouze jejím voláním z jiné procedury. K tomu je ve volací proceduře nutné přiřadit jméno této F1 (x) nějaké proměnné.

Příklad 2

Například funkci F1(x) lze použít v proceduře MySub() tak, že proměnné "y" dáte název F1(x).

Dim y As Single ‘Deklarace proměnné y

y = F1 (9) ‘Definujte F1 (x) pro hodnotu x=9

Debug.Print y 'Tisk hodnot v okně Immediate

Funkce F1 (x Jako Single) Jako Single

F1 = x^10 ‘Vrácená hodnota x na mocninu 10

Zde funkce F1(x)=$x^(10)$ pro hodnotu $ x=9$ vrátí hodnotu $3,486785E+09$ do volající procedury MySub(). Pokud není deklarován žádný datový typ pro vrácenou hodnotu funkce nebo proměnnou, která se používá v proceduře VBA, výchozí datový typ je Variant.

VBA používá procedury Function i vestavěné funkce.

Vestavěné funkce se skládají ze dvou částí: názvu (identifikátoru) a argumentů. Vestavěné funkce jsou hotové vzorce VBA, které provádějí určité akce s výrazy a v okamžiku jejich volání vracejí určitou hodnotu.

Funkce vrací místo svého názvu výslednou hodnotu, která se používá v dalších výpočtech. Funkce zpravidla vyžadují přítomnost argumentů, které jsou psány v závorkách oddělených čárkami. Některé funkce však nevyžadují žádné argumenty. Například funkce Now(), která nebere žádné argumenty, vrátí aktuální systémové datum a čas. Funkce lze použít k vytvoření nových výrazů nebo funkcí.

Jak vložit funkci do textu programu? Chcete-li použít funkci ve výrazech, musíte zadat její název do příkazu VBA. Chcete-li zavolat vestavěnou funkci, která nevyžaduje argumenty, stačí zadat její název (například Nyní) do programového kódu modulu:

SubMyDate()

Dim TD ‘deklarace proměnné TD

TD = Now 'Získat aktuální systémové datum a čas

Debug.Print TD ‘Vytisknout hodnoty v okně Immediate

Chcete-li volat funkci, která vyžaduje vstup jednoho nebo více argumentů, musíte zadat její název na pravou stranu operátoru přiřazení s parametry (hodnotami argumentů) uzavřenými v závorkách. Například pro volání vestavěného protokolu funkcí (N) s jednou proměnnou N v proceduře typu Sub je proměnné Log_N přiřazen název funkce Log (50) s hodnotou argumentu 50.

Sub Naturallogarithm()

Dim LogN ‘Deklarování proměnné LogN

Debug.Print LogN ‘Tisk hodnot v okně Immediate

Poznámka 1

Zde vestavěná funkce Log(N) pro hodnotu argumentu 50 vrací hodnotu 3,91202300542815 do volacího bodu Log(50) volající procedury "Sub Natural_Logarithm()". Funkci lze volat buď pomocí samostatného příkazu VBA, nebo umístěním jejího názvu se seznamem hodnot argumentů (parametrů) do vzorce nebo výrazu v programu VBA. Zkratka jazyka VBA používá vnoření funkcí, které umožňuje zadat volání jedné funkce jako argument jiné funkce. V tomto případě je návratová hodnota první funkce použita jako argument pro další funkci.

VBA má velkou sadu vestavěných funkcí a postupů, které usnadňují programování, které lze rozdělit do následujících kategorií:

matematický;
funkce kontroly typu;
funkce zpracování řetězců;
funkce formátování;
funkce převodu formátu;
funkce data a času.

Matematické funkce

Mezi matematické funkce patří:

Abs (x) - modul argumentu $x$;
Cos(x) - kosinus argumentu $x$;
Exp(x) - zvýšení základny přirozeného logaritmu na mocninu $x$;
Log(x) - přirozený logaritmus argumentu $x$;
Rnd - náhodné číslo z intervalu ;
Sin(x) - sinus argumentu $x$;
Sqr(x) - druhá odmocnina z $x$;
Atn(x) - arkus tangens $x$;
Tan(x) – tangens $x$;
Sgn(x) je znak $x$.

Fix(x) a Int(x) zahodí zlomkovou část čísla a vrátí celočíselnou hodnotu. Rozdíl mezi těmito funkcemi je pro záporné hodnoty argumentů. Int(x) vrátí nejbližší záporné celé číslo menší nebo rovné x, zatímco Fix(x) vrátí nejbližší záporné celé číslo větší nebo rovné x.

Funkce kontroly typu

Zde jsou funkce, které určují, jaký typ proměnné je:

IsArray(x) - funkce kontroluje, zda je proměnná pole;
IsDate(x) určuje, zda je proměnná datum;
IsError(x) určuje, zda je proměnná kód chyby;
IsNull(x) určuje, zda je proměnná null;
IsNumeric(x) určuje, zda je proměnná číselná hodnota;
IsObject(x) určuje, zda je proměnná objekt.

Funkce formátování

Funkce formátování vrací hodnotu Variant (String) obsahující výraz formátovaný podle syntaxe funkce:

Formát(Výraz[,Formát [,První den v týdnu[,První týden v roce]]]), kde:

Výraz - požadovaný argument (jakýkoli platný výraz je kombinací klíčových slov, operátorů, proměnných a konstant, jejímž výsledkem je řetězec, číslo nebo objekt);
Formát je volitelný parametr (jakýkoli platný výraz pojmenovaného nebo uživatelem definovaného formátu).

Pokud je k názvu funkce přidán znak $, pak funkce vrací hodnotu typu String Funkce vrací hodnotu typu String.

Při vytváření vlastního číselného formátu můžete použít následující znaky:

0 - rezervuje pozici digitálního bitu. Zobrazuje číslici nebo nulu. Pokud má formátované číslo jakoukoli číslici na bitové pozici, kde 0 je ve formátovacím řetězci, funkce tuto číslici zobrazí, pokud ne, zobrazí se na této pozici nula;
"#" - akce tohoto symbolu je podobná akci 0, jen s tím rozdílem, že se nezobrazují nevýznamné nuly;
. – vyhrazuje pozici oddělovače desetinných míst, určuje, kolik číslic musí být zobrazeno vlevo a vpravo od desetinné čárky;
% - rezervuje procentuální zobrazení čísla;
. odděluje stovky od tisíců.

Funkce převodu formátu

Tyto zahrnují:

Val(string) Vrací čísla obsažená v řetězci jako číselnou hodnotu příslušného typu.
Str(číslo) – Vrací hodnotu Variant (String), což je řetězcová reprezentace čísla.

Kromě funkcí Val a Str existuje řada funkcí pro převod typů výrazů.

Funkce zpracování řetězců

Mezi mnoha funkcemi jsou následující:

Chr(code) - Převede ASCII kód na řetězec. Například Chr(10) provede nový řádek, Chr(13) návrat vozíku;
Mid(string, pos[,length]) - vrací podřetězec řetězce obsahující zadaný počet znaků, kde String je řetězcový výraz, ze kterého je podřetězec extrahován;
Pos je pozice znaku v řetězci String, od které začíná požadovaný podřetězec;
Délka je počet znaků podřetězce, které se mají vrátit;
Len(string) Vrací počet znaků v řetězci.

Funkce data a času

Vrátí hodnotu Variant obsahující systémové datum, aktuální čas atd. Například funkce Datum vrátí hodnotu obsahující systémové datum.

funkce název ([seznam_ argumenty])
[instrukce]
[název = výraz]
[instrukce]
[název = výraz]
koncová funkce

Hodnota je vždy přiřazena názvu funkce alespoň jednou a obvykle po dokončení funkce. Chcete-li vytvořit vlastní funkci, začněte vytvořením modulu VBA (můžete také použít existující modul). Zadejte klíčové slovo Function následované názvem funkce a seznamem jejích argumentů (pokud existují) v závorkách. Můžete také deklarovat datový typ hodnoty vrácené funkcí pomocí klíčového slova As (toto je volitelné, ale doporučené). Vložte kód VBA, který provádí požadované akce, a ujistěte se, že je požadovaná hodnota přiřazena proměnné procedury odpovídající názvu funkce alespoň jednou v těle funkce. Funkce končí příkazem End Function.

Názvy funkcí se řídí stejnými pravidly jako . Pokud plánujete použít funkci ve vzorci listu, ujistěte se, že nadpis není ve tvaru adresy buňky. Také nezadávejte názvy funkcí, které odpovídají názvům vestavěných funkcí aplikace Excel. Pokud není nastaven rozsah funkce, výchozí je Veřejné. Funkce deklarované jako soukromé se v dialogu nezobrazují Průvodce funkcí.

Funkci lze volat jedním z následujících způsobů:

zavolejte to z jiného postupu;
zahrňte jej do vzorce listu;
zahrnout do vzorce podmíněného formátování;
zavolejte to v okně ladění VBE ( Bezprostřední). Tato metoda se obvykle aplikuje ve fázi testování (obr. 3).

Rýže. 3. Volání funkce v ladicím okně

Na rozdíl od procedur se funkce nezobrazují v dialogovém okně. Makro(Jídelní lístek Vývojář –> Kód –> Makra; nebo Alt+F8).

Funkční argumenty

Argumenty mohou být reprezentovány proměnnými (včetně polí), konstantami, znakovými daty nebo výrazy. Některé funkce nemají žádné argumenty. Funkce mají povinné i volitelné argumenty.

Funkce bez argumentů

Excel má několik vestavěných funkcí, které neberou žádné argumenty, například RAND, TODAY, TODAY. Je snadné vytvořit podobné vlastní funkce. Například:

Funkce User()
"Vrátí uživatelské jméno
Uživatel = Application.UserName
koncová funkce

Když zadáte vzorec =User(), buňka vrátí jméno aktuálního uživatele (obrázek 4). Upozorňujeme, že při použití funkce bez argumentu ve vzorci listu musíte uvést prázdné závorky.

Rýže. 4. Vzorec =User() vrací jméno aktuálního uživatele

Uživatelem definované funkce se chovají jako vestavěné funkce v Excelu. Obvykle se v případě potřeby přepočítává uživatelsky definovaná funkce, tzn. pokud se změní jeden z argumentů funkce. Funkce však můžete přepočítávat častěji. Funkce se přepočítá při změně libovolné buňky, pokud je do procedury přidán operátor

Aplikace. Volatile Pravda

Volatile metoda objektu Application má jeden argument (True nebo False). Pokud je funkce zvýrazněna jako nestálý(lze změnit), přepočítá se při každé změně libovolné buňky v listu. Když použijete argument False metody Volatile, funkce se přepočítá pouze tehdy, když se v důsledku přepočtu změní jeden z jejích argumentů.

Excel má vestavěnou funkci RAND. Moc se mi ale nelíbilo, že se náhodná čísla mění při každém přepočítávání listu. Proto jsem vyvinul funkci, která vrací náhodná čísla, která se při přepočtu vzorců nemění. K tomu byla použita vestavěná funkce VBA Rnd:

Funkce StaticRand()
" Vrátí náhodné číslo, které se při přepočítávání vzorců nemění
StaticRand = Rnd()
koncová funkce

Hodnoty získané pomocí tohoto vzorce se nikdy nemění. Ale uživatel má stále možnost vynutit si přepočet vzorce pomocí kombinace kláves .

Funkce s jedním argumentem

Rýže. 5. Tabulka komise

Existuje několik způsobů, jak vypočítat provize. Například pomocí následujícího vzorce (pokud je objem prodeje umístěn v buňce D1):

KDYŽ(A(D1>=0,D1<=9999,99);D1*0,08;ЕСЛИ(И(D1>=10 000; Dl<=19999,99);D1*0,105; ЕСЛИ(И(D1>=20 000; Dl<=39999,99);D1*0,12;ЕСЛИ(D1>=40000;D1*0,14))))

Tento vzorec selhává z několika důvodů. Jednak je složitý, není snadné ho psát a následně upravovat. Za druhé, hodnoty jsou ve vzorci přesně definovány, takže je těžké je změnit. Mnohem lepší je použít VLOOKUP (obr. 6).

Rýže. 6. Použití funkce VLOOKUP pro výpočet provizí

Ještě lépe (pak nemusíte používat vyhledávací tabulku) vytvořte vlastní funkci:

Funkční provize (prodej)
Konst. Tier1 = 0,08
Konst. Tier2 = 0,105
Konst. Tier3 = 0,12
Konst. Tier4 = 0,14
"Výpočet provize z prodeje
Vyberte Case Sales
Případ 0 až 9999,99: Provize = Prodej * Úroveň 1
Případ 10000 až 19999,99: Provize = Prodej * Úroveň 2
Případ 20000 až 39999,99: Provize = Prodej * Úroveň 3
Případ je >= 40000: Provize = Prodej * Úroveň 4
Konec Vyberte
koncová funkce

Po zadání do modulu VBA lze tuto funkci použít ve vzorci listu nebo volat z jiných procedur VBA. Když do buňky zadáte následující vzorec, výsledek bude 3000:

Provize(B2)

Funkce DoubleCell()
DoubleCell = Range(" Al ") * 2
koncová funkce

Přestože tato funkce funguje, v některých případech poskytuje nesprávné výsledky. Důvodem je, že výpočetní stroj Excel nebere v úvahu rozsahy, které nejsou předány jako argumenty. V důsledku toho někdy nejsou všechny přidružené hodnoty vyhodnoceny dříve, než funkce vrátí hodnotu. Měli byste také napsat funkci DoubleCell, která jako argument převezme hodnotu buňky A1.

Funkce DoubleCell(buňka)
dvojitá buňka = buňka * 2
koncová funkce

Funkce se dvěma argumenty

Představte si, že výše zmíněný manažer zavádí novou politiku navrženou ke snížení fluktuace zaměstnanců: celková částka splatných provizí se zvyšuje o 1 % za každý rok, kdy zaměstnanec pro společnost pracoval. Změňme vlastní funkci Provize tak, aby vyžadovala dva argumenty. Nový argument představuje počet let, které zaměstnanec pro společnost odpracoval. Nazvěme tuto novou funkci Commission2:

Funkce Provize2 (tržby, roky) Jako svobodný
" Výpočet prodejních provizí na základě
"délka služby
Provize 2 = provize (prodej) + _
(Provize (prodej) * roky / 100)
koncová funkce

Funkce s argumentem pole

Funkce mohou mít jedno nebo více polí jako argumenty, zpracovat toto pole (pole) a vrátit jednu hodnotu. Níže uvedená funkce bere pole jako argument a vrací součet jeho prvků.

Funkce SumArray(List) As Double
Ztlumit položku jako variantu
SumArray = 0
Pro každou položku v seznamu
If WorksheetFunction.IsNumber(Item) Then _
SumArray = SumArray + položka
Další položka
koncová funkce

Funkce Excel INUMERIC zkontroluje, zda je každý prvek číslo, než jej přidá k součtu. Přidání tohoto jednoduchého operátoru ověření dat eliminuje chyby nesouladu typu při pokusu o provedení aritmetické operace na řetězci.

Funkce s volitelnými argumenty

Mnoho vestavěných funkcí aplikace Excel má volitelné argumenty. Příkladem je funkce LEFT, která vrací znaky z levé strany řetězce. Má následující syntaxi:

VLEVO, ODJET( text, počet_znaků)

První argument je povinný, na rozdíl od druhého. Pokud není zadán druhý argument, Excel předpokládá hodnotu 1.

Vlastní funkce navržené ve VBA mohou mít také volitelné argumenty. Volitelný argument určíte tak, že před název argumentu přidáte klíčové slovo Optional. V seznamu argumentů jsou volitelné argumenty definovány za všemi povinnými. Například:

Funkce User2 (volitelná velká písmena jako varianta)
If IsMissing(Uppercase) Then Uppercase = False
Uživatel2 = Application.UserName
If Uppercase Then User2 = UCase(User2)
koncová funkce

Pokud je argument False nebo je vynechán, vrátí se uživatelské jméno bez jakýchkoli změn. Pokud je argument funkce True, pak je uživatelské jméno vráceno velkými písmeny (pomocí funkce Ucase VBA). Věnujte pozornost prvnímu příkazu funkce – obsahuje funkci IsMissing VBA, která určuje přítomnost argumentu. Pokud neexistuje žádný argument, příkaz nastaví proměnnou Velká písmena na False (výchozí).

Funkce VBA vrací pole

VBA obsahuje velmi užitečnou funkci zvanou Array. Vrátí hodnotu Variant, která obsahuje pole (tj. více hodnot). Pokud nejste obeznámeni s maticovými vzorci v Excelu, doporučuji začít s . Vzorec pole zadaný do buňky po kliknutí . Excel přidá kolem vzorce závorky, které označují, že se jedná o maticový vzorec.

Funkce MonthNames je jednoduchý příklad použití funkce Array v uživatelsky definované funkci.

Funkce MonthNames()
MonthNames = Array(" leden " , " únor " , " březen " , _
"duben", "květen", "červen", "červenec", "srpen", _
"září říjen listopad prosinec"
koncová funkce

Funkce MonthNames vrací vodorovné pole názvů měsíců. Na listu vyberte 12 buněk, zadejte vzorec =MonthNames() a klikněte . Pokud chcete vygenerovat vertikální pole názvů měsíců, vyberte vertikální rozsah, zadejte vzorec =TRANSPOSE(MonthNames()) a stiskněte .

Funkce, která vrací chybovou hodnotu

xlErrDivO (pro chybu #DIV/0!);
xlErrNA (pro chybu #N/A);
xlErrName (pro chybu #NAME?);
xlErrNull (pro chybu #NULL!);
xlErrNum (pro chybu #NUM!);
xlErrRef (pro chybu #REF!);
xlErrValue (pro chybu #HODNOTA!).

Níže je převedená funkce RemoveVowels (viz příklad na začátku). Konstrukce If-Then se používá k provedení alternativní akce, když argument není textový argument. Tato funkce volá funkci Excel ISTEXT, která určuje, zda argument obsahuje text. Pokud buňka obsahuje text, funkce vrátí normální výsledek. Pokud buňka obsahuje netext (nebo je prázdná), funkce vrátí chybu #HODNOTA!

Funkce RemoveVowels3(txt) jako varianta
" Odebere všechny samohlásky z argumentu Txt
" Vrátí chybu #HODNOTA!, pokud argument není řetězec
Dim i As Long
RemoveVowels3 = " "
If Application.WorksheetFunction.IsText(txt) Then
Pro i = 1 To Len(txt)
If Not UCase(Mid(txt, i, 1)) Like " " Then
RemoveVowels3 = RemoveVowels3 & Mid(txt, i, 1)
Konec, pokud
Příště já
Jiný
RemoveVowels3 = CVErr(xlErrValue)
Konec, pokud
koncová funkce

Všimněte si, že datový typ pro návratovou hodnotu funkce byl změněn. Protože funkce může vrátit něco jiného než řetězec, byl datový typ změněn na Variant.

Funkce s neurčitým počtem argumentů

Je možné vytvořit vlastní funkce, které mají neurčitý počet argumentů. Použijte pole jako poslední (nebo jediný) argument a před něj uveďte klíčové slovo ParamArray (ParamArray odkazuje pouze na poslední argument v seznamu argumentů procedury. Je to vždy datový typ Variant a je vždy volitelným argumentem). Následující funkce vrací součet všech argumentů, což může být buď jedna hodnota (buňka) nebo rozsah.

Funkce SimpleSum(ParamArray arglist() As Variant) As Double
Ztlumit buňku jako rozsah
Dim arg jako varianta
Pro každý argument v seznamu arglist
Pro každou buňku V arg
SimpleSum = SimpleSum + buňka
Další buňka
další arg
koncová funkce

Ladění funkcí

Při použití vzorce listu k testování funkce se chyby, ke kterým dojde během provádění, nezobrazí ve známém dialogovém okně zprávy. Vzorec jednoduše vrátí chybovou hodnotu (#HODNOTA!). Naštěstí to není velký problém při ladění funkcí, protože vždy existuje několik řešení.

Umístěte na důležitá místa funkci MsgBox pro ovládání hodnot jednotlivých proměnných.
Otestujte funkci jejím voláním z procedury, nikoli ze vzorce listu. Chyby za běhu se zobrazují obvyklým způsobem.
Definujte bod přerušení ve funkci a procházejte funkci krok za krokem. V tomto případě můžete použít všechny standardní nástroje pro ladění. Chcete-li přidat bod přerušení, umístěte kurzor do příkazu, kde se rozhodnete přerušit provádění, a vyberte příkaz ladit –> Přepnout bod přerušení (Ladění –> Bod zlomu) nebo klikněte .
Použijte jeden nebo více dočasných tiskových příkazů ve vašem programu k zobrazení hodnot v okně. Bezprostřední editor VBA. Chcete-li například ovládat hodnotu cyklování, použijte následující metodu:

Rýže. 7. Použijte ladicí okno k zobrazení výsledků při provádění funkce

V tomto případě se hodnoty dvou proměnných, Ch a i, zobrazí v ladicím okně ( Bezprostřední), kdykoli program narazí na příkaz Debug.Print. Umístěte kurzor kamkoli do procedury Test() a stiskněte F5. Na Obr. Obrázek 7 ukazuje výsledek pro případ, kdy funkce přebírá argument TusconArizona.

Pomocí metody MacroOptions

Můžete použít metodu MacroOptions objektu Application, která umožňuje zahrnout funkce, které jste vyvinuli jako součást vestavěných funkcí aplikace Excel. Tato metoda umožňuje:

přidejte popis funkce (počínaje Excelem 2010;
uveďte kategorii funkce;
přidejte popis argumentů funkce.

Sub DescribeFunction()
Dim FuncName jako řetězec
Dim FuncDesc As String
Dim FuncCat As Long
Ztlumit Arg1Desc jako řetězec, Arg2Desc jako řetězec
FuncName = "Draw"
FuncDesc = "Obsah buňky náhodného rozsahu"
FuncCat = 5 " Reference a pole
Arg1Desc = " Rozsah, který obsahuje hodnoty"
Arg2Desc = " (volitelné) Pokud je False nebo chybí, _
funkce Rnd se nepřepočítává. "
Arg2Desc = Arg2Desc & "Pokud je pravda, funkce Rnd se přepočítá"
Arg2Desc = Arg2Desc & "při jakékoli změně v listu."
Application.MacroOptions_
Makro:=FuncName, _
Description:=FuncDesc, _
Kategorie:=FuncCat, _
ArgumentDescriptions:=Array(Arg1Desc, Arg2Desc)
konec sub

Na Obr. 8 zobrazující dialogová okna Průvodce funkcí A Funkční argumenty po provedení procedury DescribeFunction().

Rýže. 8. Pohled na dialogová okna Průvodce funkcí A Funkční argumenty pro vlastní funkci

Procedura DescribeFunction() by měla být volána pouze jednou. Po jejím volání se informace spojené s funkcí uloží do sešitu. Pokud jste však postup upravili, zavolejte jej znovu.

Pokud neurčíte kategorii funkce pomocí metody MacroOptions, funkce vlastního listu se objeví v kategorii Definováno uživatelem dialogové okno Průvodce funkcí. V tabulce (obrázek 9) jsou uvedena čísla kategorií, která lze použít jako hodnoty pro argument Kategorie metody MacroOptions. Upozorňujeme, že některé z těchto kategorií (10 až 13) se v dialogu obvykle nezobrazují Průvodce funkcí. Pokud je takové kategorii přiřazena jedna z uživatelsky definovaných funkcí, zobrazí se v dialogovém okně.

Použití doplňků k ukládání uživatelských funkcí

Volitelně můžete často používané uživatelské funkce uložit do souboru doplňku. Hlavní výhodou tohoto přístupu je, že funkce lze použít ve vzorcích bez specifikátoru názvu souboru. Řekněme, že máte vlastní funkci ZapSpaces; je uložen v souboru Myfuncs.xlsm. Chcete-li jej použít ve vzorci v jiném sešitu (jiném než Myfuncs.xlsm), musíte zadat následující vzorec: =Myfuncs.xlsm!ZapSpaces(A1:C12).

Pokud vytvoříte doplněk založený na souboru Myfuncs.xlsm a doplněk se načte v aktuální relaci aplikace Excel, můžete odkaz na soubor přeskočit zadáním následujícího vzorce: =ZapSpaces(A1:C12). Vytvoření doplňků bude posuzováno samostatně.

Potenciální problém, který může nastat při používání doplňků k ukládání funkcí definovaných uživatelem, je závislost sešitu na souboru doplňku. Pokud sešit předáváte zaměstnanci, nezapomeňte předat také kopii doplňku, který obsahuje požadovanou funkcionalitu.

Použití funkcí Windows API

VBA si může vypůjčit metody z jiných souborů, které nemají nic společného s Excelem nebo VBA, jako jsou soubory Dynamic Link Library (DLL), které používají Windows a další programy. Díky tomu má VBA schopnost provádět operace, které bez vypůjčených metod přesahují možnosti jazyka.

Windows API (Application Programming Interface - Application Programming Interface) je sada funkcí dostupných programátorům v prostředí Windows. Když zavoláte funkci Windows z VBA, přistupujete k Windows API. Mnoho prostředků Windows používaných programátory Windows lze získat ze souborů DLL, které ukládají programy a funkce, které jsou zahrnuty během provádění programu, nikoli v době kompilace.

Než lze funkci Windows API použít, musí být deklarována v horní části programové jednotky. Pokud programový modul není standardní modul VBA (tj. modul pro UserForm, Prostěradlo nebo Tato kniha), pak musí být funkce API deklarována jako soukromá.

Deklarace funkce API má určitou složitost – funkce musí být deklarována co nejpřesněji. Deklarační příkaz říká VBA následující:

jakou funkci API používáte;
ve které knihovně je umístěna funkce API;
Argumenty funkce API.

Jakmile je funkce API deklarována, může být použita v programu VBA.

Podívejme se na příklad funkce API, která zobrazuje název složky Windows (někdy tento úkol nelze provést pomocí standardních příkazů VBA). Nejprve deklarujme funkci API:

Deklarujte funkci PtrSafe GetWindowsDirectoryA Lib "kernel32" _
(ByVal lpBuffer As String, ByVal nSize As Long) As Long

Tato funkce, která přebírá dva argumenty, vrací název složky, ve které je nainstalován operační systém Windows. Po zavolání této funkce bude cesta ke složce Windows uložena do proměnné lpBuffer a délka řetězce cesty bude uložena do proměnné nSize.

Následující příklad zobrazuje výsledek v okně zprávy:

Sub ShowWindowsDir()
Dim WinPath As String * 255
Dim WindDir jako řetězec
WinPath = mezera (255)
WinDir = Left(WinPath, GetWindowsDirectoryA _
(WinPath, Len(WinPath)))
MsgBox WinDir, vbInformation, "Windows Directory"
konec sub

Během procedury ShowWindowsDir se zobrazí okno se zprávou označující umístění složky Windows.

Někdy je potřeba vytvořit obal pro funkce API. Jinými slovy, vytvoříte si vlastní funkci pomocí funkce API. Tento přístup výrazně zjednodušuje použití funkce API. Níže je uveden příklad takové funkce VBA:

Funkce WindowsDir() jako řetězec
"Název složky Windows
Dim WinPath As String * 255
WinPath = mezera (255)
WindowsDir = Left(WinPath, GetWindowsDirectoryA _
(WinPath, Len(WinPath)))
koncová funkce

Po deklaraci této funkce ji můžete volat z jiné procedury: MsgBox WindowsDir(). Tuto funkci můžete také použít ve vzorci listu: =WindowsDir().

Pozornost! Nebuďte překvapeni pády systému, když používáte funkce Windows API ve VBA. Před testováním si práci uložte.

Určení stavu klíče

Řekněme, že jste napsali makro VBA, které bude spuštěno z tlačítka na panelu nástrojů. Chcete, aby toto makro běželo jinak, pokud uživatel po klepnutí na tlačítko podrží klávesu . Chcete-li se dozvědět o stisknutí klávesy , můžete použít GetKeyState API. Funkce GetKeyState hlásí, zda je stisknuta konkrétní klávesa. Funkce má jeden argument, nVirtKey, který představuje kód klíče, který vás zajímá.

Následuje program, který zjistí, že při provádění procedury události Button_Click byla stisknuta klávesa. . Všimněte si, že k určení stavu klíče je použita konstanta (která má hexadecimální hodnotu), která je pak použita jako argument funkce GetKeyState. Pokud GetKeyState vrátí hodnotu menší než 0, znamená to, že klíč lisovaný; jinak klíč není stisknuto. Obdobnou kontrolu lze provést u kláves Ctrl a Alt (obr. 10).

Rýže. 10. Kontrola stisknutí kláves Shift, Ctrl a Alt

Kód funkce VBA lze nalézt v přiloženém souboru Excel

Práce s funkcemi Windows API může být docela ošemetná. Mnoho knih o programování uvádí příkazy deklarace funkcí API s odpovídajícími příklady. Zpravidla stačí zkopírovat deklarační výrazy a používat funkce, aniž byste se ponořili do jejich podstaty. Většina programátorů Excel VBA vidí funkce API jako všelék pro většinu úkolů. Na internetu najdete stovky celkem spolehlivých příkladů, které můžete zkopírovat a vložit do vlastního programu.

Textový soubor obsahuje deklarace a konstanty Windows API. Tento soubor můžete otevřít v textovém editoru a zkopírovat příslušná prohlášení do modulu VBA.

Na základě knihy. - M: Dialektika, 2013. - S. 287-323.

S VBA můžete vytvořit vlastní funkci, kterou lze použít v listech stejně jako normální funkce.

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

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

Co je to funkční procedura ve VBA?

Funkční procedura je kód VBA, který provádí výpočty a vrací hodnotu (nebo pole hodnot).

Pomocí procedury Function můžete vytvořit funkci, kterou můžete použít v listu (stejně jako jakoukoli běžnou funkci Excelu, jako je SUM nebo SVYHLEDAT).

Jakmile vytvoříte proceduru Function pomocí jazyka VBA, můžete ji použít třemi způsoby:

Jako vzorec na listu, kde může vzít argumenty jako vstup a vrátit hodnotu nebo pole hodnot.
Jako součást kódu rutiny VBA nebo jiného kódu funkce.
V podmíněném formátování

Přestože list již obsahuje více než 450 vestavěných funkcí aplikace Excel, možná budete potřebovat vlastní funkci, pokud:

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

Upozorňujeme, že uživatelsky definované funkce vytvořené pomocí VBA mohou být výrazně pomalejší než vestavěné funkce. Proto se nejlépe hodí pro situace, kdy nemůžete dosáhnout výsledku pomocí vestavěných funkcí.

Funkce vs podprogram ve VBA

"Podprogram" umožňuje provádět sadu kódu, zatímco "funkce" vrací hodnotu (nebo pole hodnot).

Máte-li například seznam čísel (kladných i záporných) a chcete identifikovat záporná čísla, zde je to, co můžete s funkcí a podprogramem udělat.

Podprogram může iterovat každou buňku v rozsahu a může vybrat všechny buňky, které v ní mají zápornou hodnotu. V tomto případě podprogram dokončí změnu vlastností objektu range (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 změnit vlastnosti objektu. To znamená, že nemůžete změnit barvu buňky pomocí samotné funkce (můžete to však udělat pomocí podmíněného formátování s vlastní funkcí).

Když vytvoříte uživatelem definovanou funkci (UDF) pomocí jazyka VBA, můžete tuto funkci použít v listu stejně jako kteroukoli jinou funkci. Podrobněji se tomu budu věnovat v části „Různé způsoby použití uživatelských funkcí v Excelu“.

Vytvoření jednoduché uživatelské funkce ve VBA

Dovolte mi vytvořit jednoduchou vlastní funkci ve VBA a ukázat vám, jak to funguje.

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

Funkce GetNumeric(CellRef As String) as Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric

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

Následující text ukazuje, jak lze tuto funkci GetNumeric použít v aplikaci Excel.

Nyní, než vám řeknu, jak je tato funkce vytvořena ve VBA a jak funguje, je několik věcí, které potřebujete vědět:

Když vytvoříte funkci ve VBA, bude dostupná v celém sešitu stejně jako jakákoli jiná běžná funkce.
Když zadáte název funkce následovaný rovnítkem, Excel vám zobrazí název funkce v seznamu odpovídajících funkcí. Ve výše uvedeném příkladu, když jsem zadal =Get, Excel mi ukázal seznam, který měl moji vlastní funkci.

Myslím, že toto je dobrý příklad, kde můžete použít VBA k vytvoření snadno použitelné funkce v Excelu. Totéž můžete udělat se vzorcem (jak je ukázáno v tomto tutoriálu), ale je to komplikované a těžko pochopitelné. S tímto UDF stačí předat jeden argument a dostanete výsledek.

Anatomie uživatelem definované funkce ve VBA

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

Nyní se pojďme ponořit a podívat se, jak tato funkce vzniká. Níže uvedený kód musíte vložit do modulu v editoru VB. Tomuto tématu se věnuji v sekci

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 = Result, Result, Další End Get Function Result & Mid,CellRef

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

Toto slovo říká VBA, že náš kód je funkce (ne podprogram). Za slovem Function následuje název funkce GetNumeric. Toto je název, který použijeme na listu, abychom mohli tuto funkci použít.

Název funkce nesmí obsahovat mezery. Funkci také nemůžete pojmenovat, pokud je v konfliktu s názvem odkazu na buňku. Nemůžete například 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 existující funkci. Pokud to uděláte, Excel dá přednost vestavěné funkci.
Pokud chcete oddělit slova, můžete použít podtržítka. Například Get_Numeric je platný název

Za názvem funkce následují některé argumenty v závorkách. Toto jsou argumenty, které naše funkce od uživatele potřebuje. Je to jako argumenty, které musíme poskytnout vestavěným funkcím Excelu. Například funkce COUNTIF má dva argumenty (rozsah a kritéria).

Argumenty musí být uvedeny v závorkách.

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

Je také užitečné určit, jaký argument funkce očekává. V tomto příkladu, protože budeme funkci předávat odkaz na buňku, můžeme argument zadat jako typ "Rozsah". Pokud neurčíte datový typ, VBA jej bude považovat za možnost (to znamená, že můžete použít libovolný datový typ).

Pokud máte více než jeden argument, můžete zadat stejné v závorkách - oddělené čárkami. Později v tomto tutoriálu uvidíme, jak používat více argumentů ve vlastní funkci.

Všimněte si, že funkce je uvedena jako datový typ "String". Tím VBA sdělíte, že výsledek vzorce bude datového typu String.

Zde mohu použít číselný datový typ (například Long nebo Double), ale omezí se tím rozsah vrácených čísel. Pokud mám řetězec dlouhý 20 čísel, který potřebuji extrahovat z obecného řetězce, deklarování funkce jako Long nebo Double povede k chybě (protože číslo bude mimo rozsah). Ponechal jsem tedy výstupní datový typ funkce jako String.

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

Třetí řádek kódu deklaruje proměnnou StringLength jako datový typ Integer. Jedná se o proměnnou, do které ukládáme hodnotu délky řetězce, který je analyzován vzorcem.

Č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ý poskytne uživatel při použití vzorce v listu (nebo při jeho 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. Cyklus se provede tolikrát, kolikrát je znaků ve vstupním argumentu. Toto číslo je dáno funkcí LEN a přiřazeno proměnné "StringLength".

Takže smyčka jde od "1 do délky řetězce".

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

Druhý poslední řádek kódu přiřadí hodnotu výsledku funkce. Právě tento řádek kódu 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ý VBA sděluje, že zde kód funkce 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 ponoříme do těchto prvků a také uvidíme různé způsoby, jak spustit funkci VBA v Excelu.

Argumenty ve vlastní funkci ve VBA

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

V této části vám ukážu, jak vytvořit funkce, které nemají žádné argumenty, pro funkce, které mají více argumentů (povinných i volitelných).

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

V listu Excelu máme několik funkcí, které nemají žádné argumenty (např. RAND, TODAY, NOW).

Tyto funkce nezávisí na vstupních argumentech. Například funkce DNES vrátí aktuální datum, zatímco funkce RAND vrátí náhodné číslo mezi 0 a 1.

Stejnou funkci můžete vytvořit ve VBA.

Níže je uveden kód, který vám poskytne název souboru. Nepřijímá žádné argumenty, protože výsledek, který má být vrácen, nezávisí na žádném argumentu.

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

Tato funkce přiřadí funkci hodnotu "ThisWorkbook.Name", která je vrácena při použití funkce na listu.

Pokud byl soubor uložen, vrátí název s příponou souboru, jinak pouze uvede název.

Výše uvedené má ale jeden problém.

Pokud se název souboru změní, nebude automaticky aktualizován. Normálně se funkce aktualizuje, když se změní vstupní argumenty. 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 přepočet vynutit pomocí klávesové zkratky - Control + Alt + F9.

Aby se vzorec přepočítal vždy, když dojde ke změnám v listu, potřebujete k němu řádek kódu.

Níže uvedený kód způsobí, že se funkce přepočítá, kdykoli dojde ke změně v listu (stejně jako jiné podobné funkce listu, jako je funkce TODAY nebo RAND).

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

Pokud nyní změníte název sešitu, bude tato funkce aktualizována vždy, když dojde k jakékoli změně v tabulce nebo když tento sešit znovu otevřete.

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

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

Vytvořme další jednoduchou funkci, která bude mít pouze jeden argument.

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

Funkce ConvertToUpperCase(CellRef As Range) ConvertToUpperCase = UCase(CellRef) End Function

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 přijímá 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é přebírají více argumentů.

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

Funkce GetDataBeforeDelimiter(CellRef As Range, Delim As String) jako String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Výsledek = Left(CellRef, DelimForePosition) GetData Delimit

Pokud potřebujete ve vlastní funkci použít více než jeden argument, můžete všechny argumenty v závorkách oddělit čá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 neurčíte žádný datový typ, VBA předpokládá, že se jedná o datovou variantu.

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

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čem (pomocí funkce LEFT).

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

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

Funkce GetDataBeforeDelimiter(CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition< 0 Then DelimPosition = Len(CellRef) Result = Left(CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

Tuto funkci můžeme dále optimalizovat.

Pokud zadáte text (ze kterého chcete vyjmout část před oddělovačem) přímo do funkce, dojde k chybě. Pojď.. zkus to!

To se stane, když jsme zadali "CellRef" jako datový typ rozsahu.

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 udělat s výše uvedeným kódem. Důvodem je, že Delim byl deklarován jako datový typ řetězce.

Pokud chcete, aby funkce měla flexibilitu přijímat přímý textový vstup nebo odkazy na buňky od uživatele, musíte odstranit deklaraci datového typu. Tím se argument vytvoří jako alternativní datový typ, který může přijmout argumenty libovolného typu a zpracovat je.

Udělá to kód níže:

Funkce GetDataBeforeDelimiter(CellRef, Delim) As String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition< 0 Then DelimPosition = Len(CellRef) Result = Left(CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

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

Excel má mnoho funkcí, z nichž některé jsou volitelné.

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

Volitelný argument, jak název napovídá, je volitelný. Pokud nezadáte jeden z povinných argumentů, vaše funkce vám oznámí chybu, ale pokud nezadáte volitelný argument, vaše funkce bude stále fungovat.

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

Pokud například ve funkci SVYHLEDAT nezadáte čtvrtý argument, funkce SVYHLEDAT provede přibližné vyhledávání, a pokud zadáte poslední argument jako FALSE (nebo 0), provede přesnou shodu.

Pamatujte, že volitelné argumenty musí vždy následovat za všemi povinnými argumenty. Na začátku nemůžete mít další argumenty.

Nyní se podívejme, jak vytvořit funkci ve VBA s volitelnými argumenty.

Funkce pouze s volitelným argumentem

Pokud vím, neexistuje žádná vestavěná funkce, která přijímá pouze volitelné argumenty (mohu se mýlit, ale žádná taková funkce mě nenapadá).

Můžeme ji však vytvořit pomocí VBA.

Níže je kód funkce, která vám poskytne aktuální datum ve formátu dd-mm-rrrr, pokud nezadáte žádné argumenty (tj. toto pole ponecháte prázdné), a ve formátu „dd mmmm, yyyy“, pokud zadáte cokoliv jako argument (tj. cokoliv, co argument není prázdný).

Funkce CurrDate(Volitelné fmt jako varianta) Dim Result If 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, musí být volitelný argument datového typu variant.

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

Můžete to udělat robustnější tím, že jako argumenty vezmete pouze určité hodnoty a jinak zobrazíte chybu (jak je uvedeno v kódu níže).

Funkce CurrDate(Volitelné fmt jako varianta) Dim Result If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-yyyy") ElseIf fmt = 1 Then CurrDate = Format(Date, "dd mmmm, yyyy") Else CurrDate = CVErr End If

Výše uvedený kód vytvoří funkci, která zobrazí datum ve formátu "dd-mm-rrrr", pokud argument není zadán, a ve formátu "dd mmmm, yyy", pokud je argument 1. Ve všech ostatních případech je vyvolána chyba.

Funkce s povinnými a volitelnými argumenty

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

Nyní se podívejme na podobný příklad, který akceptuje povinné i volitelné argumenty.

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

Funkce GetText(CellRef As Range, Optional TextCase = False) As String Dim StringLength As Integer Dim Result As StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result &Cell Result = Result iC Result = Result sult = UCase (Výsledek) GetText = Výsledek End Function

Všimněte si, že ve výše uvedeném kódu jsme inicializovali hodnotu "TextCase" na False (viz v závorkách na prvním řádku).

Tímto jsme zajistili, že volitelný argument začíná výchozí hodnotou, která je FALSE. Pokud uživatel zadá hodnotu jako TRUE, funkce vrátí text velkými písmeny, a pokud uživatel zadá volitelný argument jako FALSE nebo jej přeskočí, vrácený text zůstane tak, jak je.

Vytvoření funkce ve VBA s polem jako argumentem

Dosud jsme viděli příklady vytvoření funkce s volitelnými/povinnými argumenty, kde tyto argumenty byly jedinou hodnotou.

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

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

Funkce AddEven(CellRef as Range) Dim Cell As Range for Every 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 Function

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

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

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

Kód má cyklus For Each, který prochází každou buňkou a kontroluje, zda číslo není. Pokud 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).

Na konci se sečtou všechna sudá čísla a součet se vrátí zpět do funkce.

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 poskytnout. Proto je nutné vytvořit funkci, která dokáže převzít tolik argumentů, kolik je potřeba, a pomocí nich vrátit výsledek.

Příkladem takové funkce listu je funkce SUM. Můžete uvést více argumentů (jako je tento):

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

Výše uvedená funkce přidá hodnoty ke všem těmto argumentům. Všimněte si také, že to může být jedna buňka nebo pole buněk.

Takovou funkci můžete vytvořit ve VBA zadáním posledního argumentu (nebo jediného argumentu) jako nepovinného. Tento volitelný argument také musí předcházet klíčové slovo "ParamArray".

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

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

Funkce AddArguments(ParamArray arglist() As Variant) Pro každý argument v seznamu arglist AddArguments = AddArguments + arg Další argument End Function

Výše uvedená funkce může převzí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. Nemůžete zadat pole jako argument. Pokud je například jeden z vašich argumentů D8:D10, tento vzorec vám poskytne chybu.

Pokud chcete použít oba vícebuňkové argumenty, musíte použít následující kód:

Funkce AddArguments(ParamArray arglist() As Variant) For Every arg In arglist For Every Cell In arg AddArguments = AddArguments + Cell Next Cell Next arg End Function

Všimněte si, že tento vzorec funguje s více buňkami a odkazy na pole, ale nedokáže zpracovat pevně zakódované hodnoty nebo výrazy. Kontrolou a zpracováním těchto podmínek můžete vytvořit robustnější funkci, ale to není cílem.

Cílem je ukázat vám, jak ParamArray funguje, abyste mohli povolit neomezený počet argumentů ve funkci. Pokud potřebujete lepší funkci, než je ta vytvořená v kódu výše, použijte funkci SUM na listu.

Vytvoření funkce, která vrací pole

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

S VBA můžete vytvořit funkci, která vrátí variantu obsahující celé pole hodnot.

Pole vzorce jsou také k dispozici jako vestavěné funkce v listech aplikace Excel. Pokud znáte maticové vzorce v Excelu, víte, že se zadávají pomocí kláves Control + Shift + Enter (nejen Enter). Více o maticových vzorcích si můžete přečíst zde. Pokud neznáte maticové vzorce, nebojte se, pokračujte ve čtení.

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

Níže uvedený kód to udělá.

Funkce ThreeNumbers() As Variant Dim NumberValue(1 až 3) NumberValue(1) = 1 NumberValue(2) = 2 NumberValue(3) = 3 ThreeNumbers = NumberValue End Function

Ve výše uvedeném kódu jsme zadali funkci ThreeNumbers jako možnost. To mu umožňuje obsahovat pole hodnot.

Proměnná NumberValue je deklarována jako pole 3 prvků. Obsahuje tři hodnoty a přiřadí je funkci „Tři čísla“.

Tuto funkci můžete použít na listu. Zadejte tuto funkci a stiskněte 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(ThreeNumbers())

Použijte výše uvedenou funkci pomocí Control + Shift + Enter. Všimnete si, že výsledek je nyní 3, protože to jsou největší hodnoty v poli vrácené funkcí Max, která získá tři čísla jako výsledek naší vlastní funkce ThreeNumbers.

Stejnou techniku můžete použít k vytvoření funkce, která vrací pole názvů měsíců, jak je znázorněno v kódu níže:

Funkce Months() As Variant Dim MonthName(1 To 12) MonthName(1) = "Leden" MonthName(2) = "Únor" MonthName(3) = "March" MonthName(4) = "Duben" MonthName(5) = "May" MonthName(6)" Měsíc = "Jmeno 8" = "Jmeno 7. srpen" MonthName(9) = "September" MonthName(10) = "October" MonthName(11) = "November" MonthName(12) = "Prosinec" Months = MonthName End Function

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

Chcete-li zobrazit skutečnost, že vrací všechny hodnoty, udělejte toto - vyberte buňku se vzorcem, přejděte na řádek vzorců, vyberte celý vzorec a stiskněte F9. To vám ukáže všechny hodnoty, které funkce vrací.

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

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

Proto se stejný kód, ve kterém vytváříme funkci „Měsíce“, zkrátí, jak je znázorněno níže:

Funkce Měsíce() Jako Varianta Měsíce = Array("leden", "únor", "březen", "duben", "květen", "červen", _ "červenec", "srpen", "září", "říjen", "listopad", "prosinec") End Function

Výše uvedená funkce používá funkci Array k přímému přiřazení hodnot této funkci.

Všimněte si, že všechny výše vytvořené funkce vracejí 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 =Months(), získáte všechny názvy měsíců.

Ale co když chcete tyto hodnoty ve vertikálním rozsahu buněk.

Můžete to provést pomocí vzorce TRANSPOSE na listu.

Stačí vybrat 12 svislých buněk (sousedních) a zadat vzorec níže.

Pochopení rozsahu vlastní funkce v Excelu

Funkce může mít dva rozsahy – veřejný nebo soukromý.

Sdílený rozsah znamená, že funkce je dostupná všem listům v sešitu a také všem procedurám (pomocným i funkčním) ve všech modulech v sešitu. To je užitečné, když chcete volat funkci z podprogramu (jak se to dělá, uvidíme v další části).
Soukromý rozsah znamená, že funkce je dostupná pouze v modulu, ve kterém existuje. Nemůžete jej použít v jiných modulech. Neuvidíte to ani v seznamu funkcí na listu. Pokud je například název vaší funkce "Měsíce()" a funkci zadáte v Excelu (za znaménkem =), nezobrazí se vám název funkce. Stále jej však můžete použít, pokud zadáte název vzorce

Pokud nic neurčíte, funkce je standardně veřejná.

Následuje funkce, která je soukromou funkcí:

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

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

Níže uvedený kód tuto funkci zveřejní. To se také zobrazí na listu.

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

Různé způsoby použití vlastní funkce v Excelu

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

Nejprve se podíváme na to, jak používat funkce na listu.

Použití uživatelských funkcí v sešitech

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

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

Všimněte si, že aby se funkce objevila na listu, musí to být veřejná funkce (jak je popsáno v části výše).

K vložení uživatelské funkce můžete také použít dialogové okno Vložit funkci (pomocí níže uvedených kroků). To bude fungovat pouze pro veřejné funkce.

Přejděte na kartu "Data".
Klepněte na tlačítko Vložit funkci.

V dialogovém okně Vložit funkci vyberte jako kategorii Definováno uživatelem. Tato možnost se zobrazí pouze v případě, že máte funkci v editoru VB (a veřejnou funkci).

Vyberte funkci ze seznamu všech veřejných vlastních funkcí.
Klepněte na tlačítko OK

Výše uvedené kroky vloží funkci do listu. Zobrazí také dialogové okno "Argumenty funkcí", které vám poskytne podrobné informace o argumentech a výsledku.

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

=UPPER(název sešitu())

Použití uživatelských funkcí v procedurách a funkcích VBA

Jakmile funkci vytvoříte, můžete ji použít i v jiných podprogramech.

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

Následuje funkce, která vrací název sešitu.

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

Následující postup zavolá funkci a zobrazí název v okně se zprávou.

Sub ShowWorkbookName() MsgBox Název sešitu 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.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function WorkbookNameinUpper() WorkbookNameinUpper = UCase(WorkbookName) End Function

Volání vlastní funkce z jiných sešitů

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

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

Vytvořte doplněk
Uložit funkci do osobního makra
Odkaz na funkci z jiného sešitu.

Vytvořte doplněk

Vytvořením a instalací doplňku budete mít vlastní funkci dostupnou ve všech knihách.

Předpokládejme, že jste vytvořili vlastní funkci - "GetNumeric" a chcete, aby byla ve všech sešitech. Chcete-li to provést, vytvořte nový sešit a umístěte kód funkce do modulu tohoto nového sešitu.

Klikněte na záložku "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 dáte, bude názvem vašeho doplňku. V tomto příkladu je soubor uložen pod názvem GetNumeric.
- Všimnete si, že cesta k souboru, kde je uložen, se automaticky změní. Můžete použít výchozí nebo změnit, pokud chcete.

Otevřete nový excelový sešit a přejděte na kartu Vývojář.
Vyberte možnost "Doplňky aplikace Excel".

V dialogovém okně Doplňky vyhledejte a najděte uložený soubor a klikněte na OK.

Doplněk byl nyní aktivován.

Nyní můžete používat vlastní funkce ve všech sešitech.

Uložení funkce do osobní knihy maker

Osobní sešit maker je skrytý sešit ve vašem systému, který se otevře pokaždé, když otevřete aplikaci Excel.

Toto je místo, kde můžete ukládat makra a přistupovat k nim z libovolného sešitu. Je to skvělé místo pro uložení maker, která chcete často používat.

Ve výchozím nastavení váš Excel nemá osobní sešit maker. Musíte jej vytvořit záznamem makra a jeho uložením do osobní knihy maker.

Odkaz na funkci z jiného sešitu

Zatímco první dvě metody (vytvoření doplňku a použití osobního sešitu maker) budou fungovat 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 vzorců" a má funkci s názvem "GetNumeric".

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

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

Výše uvedený vzorec použije vlastní funkci v souboru Sešit se vzorcem a poskytne vám výsledek.

Všimněte si, že protože název knihy obsahuje mezery, musí být uzavřen v jednoduchých uvozovkách.

Použití výstupního příkazu VBA

Pokud chcete ukončit funkci, zatímco je kód spuštěn, můžete tak učinit pomocí příkazu Exit Function.

Níže uvedený kód extrahuje první tři číselné znaky z alfanumerického textového řetězce. Jakmile funkce obdrží tři znaky, 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 Get J = J + 1Nhref Result = iResult &Three Result = Resrst sult End If Next i End Function

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

Ladění vlastní funkce

Při ladění vlastní funkce ve VBA můžete použít několik metod:

Ladění vlastní funkce se zprávou

Pomocí funkce zobrazíte okno se zprávou s konkrétní hodnotou.

Zobrazená hodnota může být založena na tom, co chcete zkontrolovat. Pokud chcete například zkontrolovat, zda kód běží nebo ne, bude fungovat jakákoli zpráva a pokud chcete zkontrolovat, zda smyčky běží nebo ne, můžete zobrazit konkrétní hodnotu nebo čítač smyček.

Ladění vlastní funkce nastavením bodu přerušení

Nastavte zarážku, abyste mohli krok za krokem procházet každý řádek. Chcete-li nastavit bod přerušení, vyberte požadovaný řádek a stiskněte klávesu F9 nebo klikněte na šedou svislou oblast nalevo od řádků kódu. Každá metoda by vložila bod přerušení (v šedé oblasti uvidíte červenou tečku).

Jakmile nastavíte bod přerušení a spustíte funkci, přejde na řádek bodu přerušení a poté se zastaví. Nyní můžete kód zobrazit klávesou F8. Jedním stisknutím klávesy F8 přejděte 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 okamžité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 "Result".

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) J = J + 1 Result (Výsledek) = iPrint. Nume ricFirstThree = Výsledek End If Next i End Function

Když je tento kód spuštěn, zobrazí se v bezprostředním okně následující.

Vestavěné funkce aplikace Excel vs. funkce definované uživatelem VBA

Použití vestavěných funkcí Excelu má oproti vlastním funkcím vytvořeným ve VBA několik silných výhod.

Vestavěné funkce jsou mnohem rychlejší než funkce VBA.
Když vytvoříte sestavu/dashboard pomocí funkcí VBA a odešlete ji 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í, když uvidí varování ve žlutém pruhu (které je pouze žádá o povolení maker).
Díky vestavěným funkcím aplikace Excel se nemusíte starat o přípony souborů. Pokud máte v sešitu makra nebo uživatelsky definované funkce, musíte je uložit ve formátu .xlsm

I když existuje mnoho dobrých důvodů, proč používat vestavěné funkce Excelu, existují některé případy, kdy je lepší použít vlastní funkci.

Pokud je váš vložený vzorec obrovský a složitý, je lepší použít vlastní funkci. To se stává ještě důležitější, 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 zdlouhavá 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 dělá veškerou tvrdou práci backendu.
Když potřebujete udělat něco, co nelze provést pomocí vestavěných funkcí Excelu. Příkladem může být situace, kdy chcete z řetězce extrahovat všechny číselné znaky. V takových případech výhody použití vlastní funkce gar převažují nad jejími nevýhodami.

Kam umístit kód VBA pro vlastní funkci

Když vytvoříte vlastní funkci, musíte kód umístit do okna kódu pro sešit, ve kterém chcete funkci použít.

Následují pokyny pro umístění kódu pro funkci "GetNumeric" v sešitu.

Než začnete vytvářet své vlastní funkce VBA, je dobré vědět, že Excel VBA má bohatou sbírku předpřipravených vestavěných funkcí, které můžete použít při psaní kódu.

Seznam těchto funkcí lze zobrazit v editoru VBA:

Otevřete sešit aplikace Excel a spusťte editor VBA (kliknutím to provedete Alt+F11) a poté stiskněte F2.
Vyberte knihovnu z rozevíracího seznamu v levé horní části obrazovky VBA.
Zobrazí se seznam vestavěných tříd a funkcí VBA. Kliknutím na název funkce zobrazíte její stručný popis ve spodní části okna. lisování F1 otevře stránku online nápovědy pro danou funkci.

Úplný seznam vestavěných funkcí VBA s příklady lze navíc nalézt na webu Visual Basic Developer Center.

Vlastní procedury "Function" a "Sub" ve VBA

V aplikaci Excel Visual Basic je sada příkazů, které provádějí konkrétní úkol, umístěna do procedury. funkce(Funkce) popř Sub(Podprogram). Hlavní rozdíl mezi postupy funkce A Sub je to postup funkce vrátí výsledek, postup Sub- Ne.

Pokud tedy potřebujete provést akce a získat nějaký výsledek (například sečíst několik čísel), obvykle se používá postup funkce a abyste mohli jednoduše provést některé akce (například změnit formátování skupiny buněk), musíte vybrat postup Sub.

Argumenty

Různá data lze předat procedurám VBA pomocí argumentů. Seznam argumentů je uveden při deklaraci procedury. Například postup Sub ve VBA přidá dané celé číslo (Integer) do každé buňky ve vybraném rozsahu. Toto číslo můžete předat proceduře pomocí argumentu, jako je tento:

Sub AddToCells(i As Integer) ... End Sub

Mějte na paměti, že mít argumenty pro postupy funkce A Sub ve VBA je volitelný. Některé postupy nevyžadují argumenty.

Nepovinné argumenty

Procedury VBA mohou mít volitelné argumenty. Toto jsou argumenty, které může uživatel zadat, pokud chce, a pokud jsou vynechány, procedura pro ně použije výchozí hodnoty.

Vrátíme-li se k předchozímu příkladu, aby byl celočíselný argument funkce volitelný, byl by deklarován takto:

Sub AddToCells (Volitelné i As Integer = 0)

V tomto případě celočíselný argument i výchozí bude 0.

V proceduře může být několik volitelných argumentů, z nichž všechny jsou uvedeny na konci seznamu argumentů.

Předávání argumentů hodnotou a odkazem

Argumenty ve VBA lze předat proceduře dvěma způsoby:

ByVal– předání argumentu hodnotou. To znamená, že do procedury je předána pouze hodnota (tj. kopie argumentu), a proto budou při ukončení procedury ztraceny všechny změny provedené v argumentu uvnitř procedury.
ByRef- předání argumentu odkazem. To znamená, že skutečná adresa umístění argumentu v paměti je předána proceduře. Jakékoli změny provedené v argumentu uvnitř procedury budou uloženy při ukončení procedury.

Pomocí klíčových slov ByVal nebo ByRef v deklaraci procedury můžete určit, jak je argument předán proceduře. To je ukázáno na příkladech níže:

Pamatujte, že argumenty ve VBA jsou standardně předávány odkazem. Jinými slovy, pokud se nepoužívají klíčová slova ByVal nebo ByRef, pak bude argument předán odkazem.

Než budete pokračovat v postupech funkce A Sub podrobněji bude užitečné znovu se podívat na vlastnosti a rozdíly mezi těmito dvěma typy postupů. Následují krátké diskuse o postupech VBA funkce A Sub a jsou uvedeny jednoduché příklady.

VBA procedura "Funkce"

Editor VBA postup rozpozná funkce

Funkce ... Konec Funkce

Jak již bylo zmíněno dříve, postup funkce ve VBA (na rozdíl od Sub) vrátí hodnotu. Pro návratové hodnoty platí následující pravidla:

Datový typ návratové hodnoty musí být deklarován v záhlaví procedury funkce.
Proměnná obsahující vrácenou hodnotu musí být pojmenována stejně jako procedura funkce. Tuto proměnnou není nutné deklarovat samostatně, protože vždy existuje jako nedílná součást procedury. funkce.

Dobře to ilustruje následující příklad.

Příklad funkce VBA: Provádění matematické operace na 3 číslech

Následuje příklad kódu procedury VBA funkce, který přebírá tři argumenty typu Dvojnásobek(čísla s plovoucí desetinnou čárkou s dvojitou přesností). V důsledku toho procedura vrátí jiné číslo typu Dvojnásobek rovná se součtu prvních dvou argumentů mínus třetí argument:

Funkce SumMinus(dNum1 jako dvojnásobek, dNum2 jako dvojnásobek, dNum3 jako dvojnásobek) jako dvojnásobek SumMinus = dNum1 + dNum2 - dNum3 Koncová funkce

Tento velmi jednoduchý postup VBA funkce ilustruje, jak jsou data předávána proceduře prostřednictvím argumentů. Můžete vidět, že datový typ vrácený procedurou je definován jako Dvojnásobek(slova říkají jako dvojitý po seznamu argumentů). Tento příklad také ukazuje, jak výsledek postupu funkce uloženy v proměnné se stejným názvem jako název procedury.

Volání procedury VBA "Funkce"

Pokud výše uvedený jednoduchý postup funkce vložen do modulu v editoru Visual Basic, lze jej volat z jiných procedur VBA nebo použít na listu v sešitu aplikace Excel.

Volejte proceduru VBA "Function" z jiné procedury

postup funkce lze volat z jiné procedury VBA jednoduchým přiřazením této procedury k proměnné. Následující příklad ukazuje volání procedury SumMinus, který byl definován výše.

Sub main() Dim total as Double total = SumMinus(5, 4, 3) End Sub

Volejte proceduru VBA "Function" z listu

Postup VBA funkce lze volat z listu aplikace Excel stejným způsobem jako jakoukoli jinou vestavěnou funkci aplikace Excel. Proto postup vytvořený v předchozím příkladu funkce – SumMinus lze volat zadáním následujícího výrazu do buňky listu:

SumMinus(10, 5, 2)

Dílčí procedura VBA

Editor VBA chápe, že před ním je procedura Sub, když narazí na skupinu příkazů uzavřených mezi následujícími úvodními a závěrečnými příkazy:

Sub... Konec Sub

Procedura VBA "Sub": Příklad 1. Zarovnání na střed a změna velikosti písma ve vybraném rozsahu buněk

Zvažte příklad jednoduché procedury VBA Sub, jehož úkolem je změnit formátování vybraného rozsahu buněk. Buňky jsou vycentrovány (vertikálně i horizontálně) a velikost písma je změněna na uživatelem zadanou:

Sub Format_Centered_And_Sized(Volitelné iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

Tento postup Sub provádí akce, ale nevrací výsledek.

Tento příklad také používá volitelný argument iFontSize. Pokud argument iFontSize nepřešlo do řízení Sub, pak jeho výchozí hodnota je 10. Pokud však argument iFontSize předán do řízení Sub, pak bude vybraný rozsah buněk nastaven na velikost písma zadanou uživatelem.

Procedura VBA "Sub": Příklad 2 - Zarovnat na střed a použít tučné písmo ve vybraném rozsahu buněk

Následující postup je podobný právě probíranému, ale tentokrát místo změny velikosti aplikuje na vybraný rozsah buněk styl tučného písma. Toto je příklad postupu Sub, který nebere žádné argumenty:

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub

Volání procedury "Sub" v aplikaci Excel VBA

Volejte proceduru VBA "Sub" z jiné procedury

Volání procedury VBA Sub z jiného postupu VBA, musíte napsat klíčové slovo volání, název procedury Sub a dále v závorce jsou argumenty postupu. To je znázorněno na níže uvedeném příkladu:

Sub main() Call Format_Centered_And_Sized(20) End Sub

Pokud postup Format_Centered_And_Sized má více než jeden argument, musí být odděleny čárkami. Takhle:

Sub main() Call Format_Centered_And_Sized(arg1, arg2, ...) End Sub

Volejte proceduru VBA "Sub" z listu

Postup Sub nelze zadat přímo do buňky listu Excel, jak to lze provést pomocí postupu funkce protože postup Sub nevrací hodnotu. Nicméně postupy Sub, které nemají žádné argumenty a jsou deklarovány jako Veřejnost(jak je uvedeno níže) bude k dispozici uživatelům listu. Pokud tedy jednoduché postupy diskutované výše Sub vložen do modulu v editoru jazyka Visual Basic, procedura Format_Centered_And_Tučné bude k dispozici pro použití v excelovém listu a postupu Format_Centered_And_Sized– nebude k dispozici, protože má argumenty.

Zde je snadný způsob, jak spustit (nebo spustit) proceduru Sub, přístupný z pracovního listu:

Klikněte Alt+F8(stiskněte klávesu alt a přidržte jej a stiskněte klávesu F8).
V zobrazeném seznamu maker vyberte to, které chcete spustit.
Klikněte Běh(Běh)

Chcete-li provést postup Sub rychle a snadno mu můžete přiřadit klávesovou zkratku. Pro tohle:

Klikněte Alt+F8.
V zobrazeném seznamu maker vyberte to, kterému chcete přiřadit klávesovou zkratku.
Klikněte Možnosti(Možnosti) a v zobrazeném dialogovém okně zadejte klávesovou zkratku.
Klikněte OK a zavřete dialog Makro(makro).

Pozornost: Při přiřazování klávesové zkratky makru se ujistěte, že se nepoužívá jako standardní v Excelu (např. ctrl+c). Pokud vyberete již existující klávesovou zkratku, bude znovu přiřazena k makru a v důsledku toho může uživatel spustit makro omylem.

Rozsah postupu VBA

Část 2 tohoto tutoriálu pojednávala o rozsahu proměnných a konstant a roli klíčových slov. Veřejnost A Soukromé. Tato klíčová slova lze také použít s procedurami VBA:

Pamatujte, že pokud před deklarováním procedury VBA funkce nebo Sub klíčové slovo není vloženo, je pro proceduru nastavena výchozí vlastnost Veřejnost(to znamená, že bude k dispozici všude v tomto projektu VBA). To je na rozdíl od deklarací proměnných, které ve výchozím nastavení jsou Soukromé.

Předčasné ukončení procedur VBA "Function" a "Sub"

Pokud potřebujete ukončit provádění procedury VBA funkce nebo Sub, bez čekání na jeho přirozený konec, pak pro to existují operátory výstupní funkce A výstupní sub. Použití těchto operátorů je ukázáno níže pomocí jednoduchého postupu jako příkladu. funkce A, která očekává, že obdrží kladný argument k provedení dalších operací. Pokud je do procedury předána nekladná hodnota, nelze provést žádné další operace, takže by se uživateli měla zobrazit chybová zpráva a procedura by měla být okamžitě ukončena:

Funkce VAT_Amount(sVAT_Rate As Single) As Single VAT_Amount = 0 If sVAT_Rate<= 0 Then MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate Exit Function End If ... End Function

Vezměte prosím na vědomí, že před dokončením postupu funkce – Částka_DPH, je do kódu vložena vestavěná funkce VBA MsgBox, která uživateli zobrazí vyskakovací okno s varováním.