Dejte Excelu funkci

Co je třeba si uvědomit

• Funkce je schopna vracet pouze hodnotu nebo matici hodnot. Není možné s pomocí funkce manipulovat s objekty, prostředím, buňkami a podobně a to ani odkazem na jinou proceduru. Pozn. Existují výjimky. Do funkcí je třeba možné zařadit MsgBox.
• Funkci je možné testovat buď na listu, nebo s pomocí jiné procedury. Proceduru funkce jako takovou samostatně nespustíte.
• Ve funkcích není možné použít odkaz na vestavěná makra verze Microsoft Excel 4.0 s pomocí ExecuteExcel4Macro. Vlastně by to znamenalo obejít zákaz funkce listu Call, která není dostupná od verze Excelu 2000.

Deklarace funkcí

Function MAFUNKCE()
   ...
   MAFUNKCE = ...
End Function

Smyslem funkcí je ale zpracovávat určitý vstup a na jeho základě vracet hodnotu (hodnoty). K tomu je nutné si něco říci o předávání parametrů.

Předávání parametrů obecně

Zatím jste se setkali například se zápisem Sub MojeProcedura(), kdy prázdné závorky značí, že procedura neočekává žádné parametry a existuje sama pro sebe. Síla procedur je ale v tom, že dokáží zpracovávat nějaké ty vstupní parametry (proměnné, řetězce apod.). Jejich počet nemusí být přitom striktně dán, navíc některé mohou být volitelné. Záleží také na tom, jak proměnnou proceduře předáme. Od toho se odvíjí pojmy předané hodnotou (proceduře předáme kopii obsahu proměnné) a předané odkazem, což znamená, že předáme proměnnou se vším všudy (předáváme odkaz na místo v paměti). Je to jako dát kopii meče na výstavu a pravý meč odkázat potomkovi. Logicky, práce s kopií proměnné neovlivní původní proměnnou.

Sub Zadani()
   Dim A As Integer

   A = 5

   'voláme procedury Ctverec1 a Ctverec2, předáváme proměnnou A
   'slovíčko Call je nepovinné
   Call Ctverec1(A)
   Call Ctverec2(A)

End Sub


Sub Ctverec1(ByVal X)
   'klíčové slůvko ByVal
   'procedura ukládá kopii původní proměnné do proměnné X (X=A)

   X = X ^ 2

End Sub

Sub Ctverec2(ByRef X)
   'klíčové slůvko ByRef nebo bez uvedení
   'procedura provede substituci X za předávanou proměnnou A (A=X)

   X = X ^ 2

End Sub

Slovní doprovod (krokování začíná v proceduře Zadani):

1. Do proměnné A typu Integer je vložena hodnota 5.
2. Voláme proceduru Ctverec1 a dáváme k dispozici proměnnou A.
3. Procedura Ctverec1 přejímá obsah (kopii) proměnné a ukládá jí do proměnné X (X=A). Kód dále mění proměnnou X na její mocninu a ukončuje se. Dochází k návratu do procedury Zadani.
4. Procedura Zadani pokračuje ve volání procedury Ctverec2 a opět dává k dispozici proměnnou A, jež nebyla doposud nijak ovlivněna.
5. Procedura Ctverec2 přebírá přímý odkaz na proměnnou A a provádí substituci, tj. odkazovat se na ni bude nyní jako na X (A=X). Do proměnné X se vkládá její mocnina a procedura se ukončuje.
6. Procedura Zadani pokračuje, proměnná A byla ovšem pozměněna (došlo vlastně ke zpětné substituci).

Poznámka č. 1: Předávání parametrů odkazem má stejný význam jako použití proměnné na úrovni modulu.

Poznámka č. 2: Předávání parametrů hodnotou neovlivní ani proměnnou deklarovanou na úrovni modulu (přestože jsou názvy proměnných shodné).

Dodatek: U přebíraných proměnných je možné nastavovat i datový typ (např. Sub Ctverec1(ByVal X As Single). Pokud je ale datový typ nepřeveditelný z původního typu, průběh kódu bude přerušen chybovým hlášením. Proto deklaraci typu doporučuji přinejmenším zvážit. Předávání proměnné odkazem (ByRef) je na toto obzvlášť choulostivé (zde platí přímo zákaz udávání typu).

A nyní pojďme rovnou k příkladům funkcí.

Funkce neočekávající parametr

Function TYPDNE()

   'pořadové číslo dne v týdnu (pondělí = 1)
   DenTydne = Weekday(Now, vbMonday)

   If DenTydne > 5 Then
      TYPDNE = "volný den"
   Else
      TYPDNE = "pracovní den"
   End If

End Function

Funkce očekávající jeden parametr

Function TYPCISLA(Cislo)

   If Cislo Mod 2 = 0 Then
      TYPCISLA = "sudé"
   Else
      TYPCISLA = "liché"
   End If

End Function

V příkladu výše funkce očekává jeden povinný parametr pojmenovaný Cislo. Parametr by mohl být uveden také s uvedením typu, např. Function TYPCISLA(Cislo as Range). V tom případě funkce očekává adresu buňky s číslem, ne číslo samotné. Tím ovšem ztrácíme na univerzálnosti, neboť takto nemůžeme zadat číslo přímo do funkce. Proto je často nutné typ vynechávat. Neznamená to ovšem, že bychom měli zapomínat na deklaraci ostatních proměnných v těle funkce! Příklad, jak jej vidíte, neobsahuje žádné ošetření chyb.

Funkce s volitelným parametrem

Funkce mohou obsahovat i volitelné parametry, uvozené slovem Optional. Ověření, zda-li bylo využito tohoto parametru, provádíme funkcí IsMissing. Funkce níže tak zjišťuje, zda-li byla zadána mocnina. Pokud ne, automaticky je spočtena druhá mocnina pro Cislo.

Function UMOCNENI(Cislo, Optional Mocnina)

   'kontrola přítomnosti volitelného parametru Mocnina
   If IsMissing(Mocnina) Then
      'není-li parametr zadán, je bráno Mocnina = 2
      UMOCNENI = Cislo ^ 2
   Else
      UMOCNENI = Cislo ^ Mocnina
   End If
   
End Function

Funkce využívající funkci listu

Ne pro všechno musíme vytvářet vlastní funkce. Některé standardní obsahuje přímo model VBA, některé jsou dostupné z katalogu funkcí pro list. Obecně platí, že pokud je funkce dostupná ve VBA, není k dispozici při výběru z katalogu listu (bohužel překrytí není úplně korektní). V příkladu níže funkce očekává zadaný poloměr typu Single a na jeho základě vrátí obsah kruhu. Jak vidíte, odkazuje se na funkci (lépe řečeno konstantu) listu PI.

Function OBSAHKRUHU(Polomer As Single)
   OBSAHKRUHU = WorksheetFunction.Pi() * Polomer ^ 2
End Function

Pozn. Na funkce se zde odkazuje anglickým názvem, jak je znázorněno dále. Nemusíte hledat překlady v literatuře, přehled najdete v příloze.

Funkce očekávající pole hodnot

Funkce také může zpracovávat pole hodnot. V tom případě v zápisu funkce uvedeme klíčove slovo ParamArray a za proměnnou, jak již znáte z deklarací polí, následují závorky. Typ musí být vždy v takovém případě deklarován jako Variant! Pole zpracováváme v cyklu.

Function SLOVO(ParamArray Pismena() As Variant) As String

   For Each Znak In Pismena
      SLOVO = SLOVO & Znak
   Next Znak

End Function

Je zde ještě jeden zádrhel. Taková funkce neumí zpracovat oblast (např. A1:A5), ale pouze seznam oddělených hodnot (A1, A2, A3, A4, A5). Pozn. Porovnejte vestavěné funkce SUMA a třeba CONCATENATE. I na nich je vidět rozdíl v chování.

Funkce vracející pole hodnot

Funkce nejenže umí zpracovávat pole, ony jej umí i vracet. Takové funkce fungují stejně jako maticové vzorce a zadávají se v listu stiskem Ctrl+Shift+Enter. Tady je nutné si uvědomit, že standardně má pole řádkový (horizontální) charakter, to znamená, že je představováno položkami v řádku. Pokud si přejeme navracet položky ve sloupci, je nutné použít vestavěnou funkci Transpose, jak ukazuje spodní část kódu.

Function PRACDNY()
   Dim Dny()

   Dny = Array("po", "út", "stř", "čt", "pá")

   'vodorovné pole
   PRACDNY = Dny

   'svislé pole
   'PRACDNY = WorksheetFunction.Transpose(Dny)

End Function

Tip. Při zadávání takových funkcí je prakticky nutné znát předem počet položek pole. Proto do nich doporučuji vkládat nepovinný parametr, při jehož uvedení funkce nevrátí samotné položky, ale jejich počet.

Nejčastější otázky

Jak je to se zobrazováním vlastních funkcí v dialogu Vložit funkci?
Naše funkce se budou běžně zobrazovat v kategorii vlastní.

Je možné změnit kategorii pro umístění funkce a přidat nápovědu nebo alespoň popis?
Osobně mám rád pořádek a mísení vlastních funkcí s vestavěnými nedoporučuji. Přesto nastíním postup, jak jej uvádí i John Walkenbach. Zařazení funkcí do kategorií je možné po načtení sešitu v modulu ThisWorkbook.

Private Sub Workbook_Open()

   'vložení funkce TypCisla do kategorie 3 (Matematické)
   Application.MacroOptions Macro:="TypCisla", _
         Category:=3, _
         Description:="Vrátí typ čísla (liché, sudé)."
         'popis funkce

End Sub

Vybraná čísla kategorií:

1. Finanční
2. Datum a čas
3. Matematické
4. Statistické
5. Vyhledávací
6. Databázové
7. Textové
8. Logické
9. Informační

Nápověda je dána parametry HelpFile (cesta k souboru nápovědy) a HelpContextID (identifikátor hesla).

Private Sub Workbook_Open()

   Application.MacroOptions Macro:="TypCisla", _
         Category:=3, _
         Description:="Vrátí typ čísla (liché, sudé).", _
         HelpFile:=ThisWorkbook.Path & "\MojeFunkce.hlp", _
         HelpContextID:=1000

End Sub

Jak se správně odkazovat na funkce?
Funkce, tak jak jsme je uváděli doposud, jsou dostupné pouze pro daný sešit. U odkazu z jiného sešitu musí být před názvem funkce uveden i název sešitu (případně s cestou k sešitu), v němž je funkce obsažena (např. =vba_funkce.xls!TYPCISLA(3). Chceme-li funkce využívat globálně, je třeba je uchovat v sešitu uloženém jako doplněk, jenž je načítán po startu aplikace. V tom případě se stačí na funkce odkazovat pouze názvem bez uvedení zdroje.

Vlastní funkce se mi nepřepočítávají.
Toto je klasický problém Excelu, který myslím nemá stoprocentní řešení. Funkce by se měly vždy přepočítávat po změně jednoho ze svých parametrů. Bohužel Excel ne vždy vyhodnotí událost jako změnu. Pokud třeba napíšeme funkci zjišťující barvu buňky, pak změna barvy sledované buňky nevyvolá přepočet funkce. Toho docílíme pouze ručně stiskem Ctrl+Alt+F9. Přesto běžně svým funkcím můžete dopomoci k přepočtu uvedením části kódu Application.Volatile True (mezera před True). Pak by se měla funkce přepočítávat při změně libovolné buňky.

Chci funkci používat jen pro své vnitřní účely.
Platí stejné pravidlo, jako pro klasické procedury. Pokud uvedete před funkcí klíčové slovo Private, funkce se nebude v dialogu Vložit funkci zobrazovat. Ručně ji ovšem nadále zadat v listu můžete.

Funkce nejdou samostatně testovat.
Máte možnost buď je volat z jiné procedury (s krokováním) nebo vyzkoušet přímo na listu s tím, že do procedury funkce umístíte zarážku.

Jsou funkce citlivé na velikost písmen a jak je to s diakritikou?
Stručná odpověď zní: Nejsou. Pozor ale na jednu věc. Doporučuji názvy funkcí psát už v samotném kódu velkými písmeny a stejně tak při prvním použití v listu! Excel si totiž velikost písmen pamatuje. Takže pokud poprvé v listu zapíšete TypCisla, tak je později těžké donutit Excel zachovat zápis TYPCISLA. V takovém případě neznám rychlé řešení, pravděpodobně je nejlepší funkci z listu vymazat, funkci přejmenovat a použít a pak se vrátit k původní podobě. Diakritika možná v názvech funkcí je.