Tvorba podpůrného matematického aparátu
pro uživatele tabulkových procesorů

Funkce v doplňcích pro Excel

Doc. Dr. Vladimír Homola, Ph.D.

Abstrakt

Při řešení rozsáhlých (např. výzkumných) úkolů spolupracuje řada odborníků nejrůznějších vědních oborů. Většina z nich zpracovává data svého oboru za použití matematického aparátu charakteristického pro jejich vědní obor, a to převážně v datovém rámci tabulkových procesorů. To jsou však obecné programové celky neobsahující specifické funkce daného oboru. Článek popisuje jednu z možností rozšíření funkčnosti tabulkových procesorů - konkrétně programu Excel - tak, aby se jevily jako jejich integrální součást a nekladly na jejich uživatele zvýšené nároky na použití.

Doplňky pro Excel

Úvod

Článek je určen vývojářům, kteří by se rozhodli vytvářet doplňky pro použití běžnými uživateli Excelu (není tedy určen přímo těmto koncovým uživatelům). V celém článku jsou jednotlivé kroky demonstrovány na doplňku, který je obsahem sešitu HMMAPPRO.XLSX, převedeného posléze do formátu doplňku (ke stažení viz závěr článku). Uvedený sešit obsahuje několik funkcí pro transformaci mapových projekcí. Primárně je použito prostředí Windows 7 / Office 2010. V případě větších odlišností od dřívějších nebo pozdějších verzí jak operačního systému, tak systému Office, je v textu učiněna poznámka. Dlužno však říci, že až na tzv. "pás karet" jsou změny minimálně od Office 2003 naprosto zanedbatelné.

 

Pojem "Doplněk"

Pod pojmem Doplněk (angl. Add-In) se obecně rozumí předem připravené rozšíření funkčnosti nějaké aplikace. Pro tabulkový procesor Excel je možno takové rozšíření připravit ve formátu XLA (resp. XLAM v novějších verzích MS Office) a připojit ho pro následné použití v kterémkoliv jiném sešitu jako doplněk. Pro MS Office 2010 a Windows 7 se volba provede podle následujících schémat:
 


Obr. 1: Hlavní menu Vývojář a karta Doplňky

a po aktivaci zaškrtnutím označit požadovaný doplněk:



Obr. 2: Formulář Doplňky

Po připojení doplňku podle předchozího odstavce je možno používat objektů doplňku v každém sešitu Excelem otevřeným. Doplněk se totiž připojuje nikoliv ke konkrétnímu sešitu, ale skutečně ke konkrétní aplikaci, a tou je Excel Application. Doplněk lze ovšem připojit jen tehdy, je-li Excelem otevřen nějaký (libovolný) sešit. Informace o připojených doplňcích se uchovává v nastavení aplikace, zde Excelu. Po novém spuštění aplikace Excel má každý sešit aplikací otevřený k disposici ty doplňky, které byly označeny v okamžiku ukončení předchozího chodu aplikace Excel.

Chování doplňku v sešitech Excelu

Shora popsaný mechanismus může ovšem vést k problémům. Jestliže můj sešit A používá funkce z připojeného doplňku D a pokud po otevření mého sešitu B zruším připojení doplňku D, pak při opětovném otevření sešitu A mohu skutečně mít problémy.

Je proto vhodné vědět, jak s funkcemi z doplňku pracuje Excel.

Především tato informace: funkce z doplňku použitá ve vzorci jiného sešitu je ve vzorci uvedena absolutní adresací tvaru např.

='E:\Documents and Settings\Administrator\Data aplikací\Microsoft\Doplňky\Hom Mapové projekce.xla'!be2kr(A1;B1)

Z toho je zřejmé, že logicky nelze smazat soubor s doplňkem (i když fyzicky ano). Na druhé straně odstraní-li v mezidobí uživatel ze seznamu doplňků daný doplněk, po opětném otevření sešitu odkazujícího na funkci doplňku se sešit chová jako s "normálním" odkazem na externí zdroj. Především tedy následuje upozornění ekvivalentní následujícímu:

 

 

Stisknutí Ne sice pokračuje dále, ovšem bez možnosti měnit buňky dodávající data do vzorců s funkcemi doplňku.

Stisknutí Ano na první pohled (samozřejmě za předpokladu, že někdo doplněk fyzicky nesmazal!) nemá chybu. Aktualizují se informace o vazbách a lze měnit zdroj vzorců s jejich následným přepočtem. Může ovšem nastat i tato situace:

 

 

Odhlédneme-li od - v tomto případě nepravdivé - první věty, je zdroj potíží ve druhé odrážce: v daném okamžiku skutečně musí být aktuálně označeny (vybrány) ty buňky, do kterých se umísťuje výsledek funkce. Odevzdává-li funkce jedinou hodnotu, problém nenastane. Odevzdává-li funkce jako výsledek více hodnot (vektor, matici), v případě alespoň dvou takových vzorců chyba nastane vždy. V případě jediného takového vzorce chyba nenastane tehdy, pokud už při aktivaci sešitu je označeno pole právě těch buněk přijímajících výsledek - ale ty musely být označeny už při předchozím uzavírání sešitu a kdo by proboha na to pamatoval?

Formát a umístění doplňku

Podle zkušeností autora se při vytváření doplňku jako nejméně problémový ukázal tento postup:


Obr. 3: Uložení jako doplněk

Aby bylo doplněk "vidět" ve formuláři "Doplňky" jako na obr. 2 bez jakýchkoliv dalších činností při každém spuštění Excelu, je nutno uložit soubor XLA resp. XLAM do jednoho ze dvou míst:

 

Obecně jsou doplňky dodané s Excelem (či spíše s MS Office) nainstalovány do adresáře Library podle schématu vlevo. MSOFFICE je ten adresář, který byl při instalaci označen jako cílový adresář instalace MS Office. 15 je číslo označující konkrétní verzi Office, např. adresář Office 11 je vytvořen při instalaci Office 2003, Office 15 při instalaci Office 2013. Pro uložení doplňků je při instalaci vytvořen adresář Library (Pozor! Je tam i ADDINS, ale ten slouží Officům k jiným účelům). Sem je možno uložit i vlastní doplňky, máme-li ovšem právo zápisu. Doplněk pak bude "vidět" každý uživatel systému.
Doplňky lze také umístit do adresáře Doplňky podle schématu vlevo. Doplněk pak bude vidět jen určitý uživatel systému, zde Admin (analogicky pro jiné uživatele). Opět platí, že při ukládání do tohoto adresáře musí být zajištěno právo zápisu.

 

Doplněk lze ovšem uložit kamkoliv a při jeho připojování použít tlačítko "Procházet" - viz obr. 2. To však běžní uživatelé PC mají strašně neradi.

Specifika sešitu ukládaného jako doplněk XLA

S1.

Při tvorbě sešitu, který má být uložen a používán jako doplněk, lze běžně používat všechny možnosti Excelu, specielně data a vzorce v listech. Z jiného sešitu však tyto listy při použití nejsou vidět a uživatel k nim tedy nemá přístup a nemůže je měnit. To poskytuje ochranu datům a vzorcům na listech doplňku.

S2.

Po připojení doplňku lze z jiného sešitu přímo použít pouze funkce deklarované v modulech doplňku, pokud autor doplňku nezajistí programově jinak. Přesněji, v jiném sešitu se připojený doplněk projeví pouze v průvodci funkcemi a možností použít funkce doplňku ve vzorcích:

 



Obr. 4: Funkce z doplňku

S3.

Po vytvoření doplňku lze před jeho uživatelem skrýt zvláště kód před prohlížením. To poskytuje ochranu autorovi doplňku. Zabezpečení je dostupné v prostředí Visual Basicu přes hlavní menu Tools, položka VBA Project Properties:

 



Obr. 5: Podmenu menu Tools

a po aktivaci označit požadované vlastnosti zabezpečení na záložce Protection:



Obr. 6: Formulář Project Properties

S4.

Text programu ve Visual Basicu použitý v aplikacích (VBA - Visual Basic for Applications) je obecně interpretován - přesněji: nejprve je mírně předkompilován a tento kód je pak interpretován. Při použití přímo v sešitu (souboru XLS, XLSM) je kód kompilován vždy při požadavku na jeho spuštění. V případě doplňku je však veškerý kód kompilován již při ukládání doplňku a proto je použití kódu z doplňku rychlejší než ze sešitu samotného.

S5.

V doplňku lze naprogramovat události AddinInstall a AddinUninstall prováděné v okamžiku nainstalování doplňku (viz obr. 2 - při "zaškrtnutí") resp. jeho odinstalování (při "odškrtnutí"). Protože z doplňku lze použít přímo pouze funkce, lze např. v události AddinInstall zajistit doplnění hlavního menu nebo jeho podmenu o položky spouštějící subroutiny, tj. jakoby původní "makra". V tom případě by naopak událost AddinUninstall měla takové položky odstranit.

S6.

Události doplňku nelze přejít resp. ignorovat držením klávesy SHIFT při otevírání doplňku. Zvláště je tedy zajištěno, že události AddinInstall a AddinUninstall se vždy korektně provedou.

Příprava doplňku

Jak bylo zmíněno shora, doplněk se připraví jako "normální" sešit. Vzhledem k předchozí kapitole je však třeba věnovat pozornost následujícímu:

 V následujících odstavcích jsou uvedené body popsána podrobněji.

Formální náležitosti

Aby byl doplněk uživatelem jednoduše připojitelný, zvláště jednoduše vyhledatelný a identifikovatelný (srov. obr. 2), měly by být být splněny jisté formální náležitosti.

Doplněk se především musí "sám" objevit ve formuláři Doplňky. To je zajištěno umístěním doplňku v jednom z adresářů podle kapitoly "Formát a umístění doplňku". Aby byl doplněk v tomto formuláři jednoduše vyhledatelný a identifikovatelný (tj. aby jeho nabídka ve formuláři a jeho popis odpovídal záměrům autora), je zapotřebí před uložením sešitu jako doplňku zadat odpovídající texty do vlastností sešitu podle obr. 7 a 8. Použije se k tomu hlavní menu Soubor, položka Vlastnosti následovně:

 



Obr. 7: Položka Vlastnosti v menu Soubor

a po aktivaci vyplnit pole Název a Komentář:



Obr. 8: Formulář Vlastnosti souboru

Text (sleduj také obr. 2) uvedený jako Název je pak zobrazen jako text v seznamu doplňků, text uvedený jako Komentáře je zobrazen jako popis pod seznamem doplňků.

Obecně použitelné funkce doplňku

Funkce doplňku, které mají být přímo použitelné v jiném sešitu, musí být v doplňku umístěné v modulu (tedy ne např. v kódu nějakého listu). Přidání modulu do sešitu zajišťuje v prostředí editoru Visual Basic kontextové menu Průzkumníka projektu (Project Explorer):

 



Obr. 9: Průzkumník projektu

pravý click kamkoliv do Průzkumníka projektu zobrazí kontextové menu, v něm je podmenu Insert (vlož):



Obr. 10: Výběr přidávaného objektu

Jméno modulu

Po vložení nového modulu jest mu Basicem přiděleno jméno tvaru Modulei (např. Module1). V okně vlastností (ukotveném pod Průzkumníkem projektu) lze toto jméno (vlastnost Name) změnit na takové, které lépe vystihuje obsah modulu - na obr. 9 je to jméno mdExclJTSK.

Zápis textu funkce

Aktivací modulu (=položky průzkumníka projektu) např. double-clickem otevře Visual Basic formulář pro zápis kódu modulu, tj. pro vlastní text funkcí a subroutin; část zapsaného textu viz následující obrázek:

 

 

Obr. 11: Text funkce v modulu

 

Takto zapsané funkce je pak možno použít ve vzorcích listů toho sešitu, který má doplněk připojený (sleduj obr. 4):

Zadání popisu funkce

Popis funkce se zadá relativně nejsložitěji. Je přitom vhodné znát alespoň rámcově strukturu objektu Workbook ("sešit Excelu"):

Pávě vlastnost Description je zapotřebí nastavit.

S jednotlivými objekty otevřeného projektu pracuje program označovaný jako Object Browser a který lze spustit např. z hlavního menu View, položka Object Browser:

 



Obr. 12: Spuštění Object Browseru

aktivací se pak spustí program Object Browser:



Obr. 13: Formulář programu Object Browser

 

Po spuštění zobrazí program Object Browser všechny objekty všech připojených knihoven (All Libraries). Je vhodné požádat o zobrazení objektů jen právě zpracovávaného sešitu.

 






Obr. 14: Volba knihovny








Aktivací se pak zobrazí jen objekty definované ve zpracovávaném sešitu. Výběrem modulu v levém seznamu jsou zobrazeny objekty modulu, výběrem objektu v pravém seznamu jsou dole zobrazeny informace o objektu - ze funkce be2kr:



Obr. 15: Formulář programu Object Browser

Pravý click na označený objekt (zde funkci be2kr) zobrazí kontextové menu objektu, kterým je člen kolekce programových jednotek (Member of Collection). Jednou z jeho položek jsou Vlastnosti (Properties):

 



Obr. 16: Kontextové menu funkce

Aktivací se zobrazí formulář volitelných vlastností (Options):



Obr. 17: Formulář volitelných vlastností

A konečně textové pole Popis (Description) je právě to místo, do něhož napsaný text se zobrazuje jako popis funkce. Uf!

Poznámka: Pokud by byl pro tento projekt (=budovaný doplněk) připraven soubor nápověd (Help File) a pokud by v tomto souboru byly připraveny kontextové nápovědy pod jistými číselnými identifikátory (Help Context ID), lze číslo kontextové nápovědy zadat ve formuláři dle obr. 17. Výsledek je ten, že ten, kdo vkládá do svého sešitu s připojeným doplňkem tuto funkci, může si pro ni vyžádat konkrétní nápovědu. Tvorba helpů však překračuje rámec tohoto článku.

Volání subroutin doplňku

Po připojení doplňku je možno z něj přímo používat jen funkce - jejich příprava je popsána shora. Nelze z něj tedy přímo volat to, co uživatelé Excelu znají jako tzv. makra a což jsou de facto subroutiny bez parametrů zapsané na kterémkoliv listě nebo v kterémkoliv modulu. Důvodem je hierarchická výstavba objektové struktury používané Excelem.

Aplikace Excel spouštěná konkrétním uživatelem však udržuje nastavení svého prostředí (přesněji obsah objektů Tool Box svého MDI formuláře) a toto prostředí je přístupné jiné aplikaci Excel používající (tedy i VBA - Visual Basic for Applications). 

Dále: Připojení nějakého doplňku ("zaškrtnutí" ve formuláři Doplňky - viz obr. 2) vyvolá událost AddinInstall v doplňku. Naopak, odpojení nějakého doplňku ("odškrtnutí" ve formuláři Doplňky) vyvolá událost AddinUninstall v doplňku. Připomínáme, že připojení doplňku je poznamenáno v aplikaci Excelu, nikoliv v sešitu, který je otevřen v okamžiku připojení doplňku (analogicky odpojení).

Autor doplňku má tedy možnost naprogramovat subroutiny bez parametru nazvané Workbook_AddinInstall a Workbook_AddinUninstall volané při uvedených událostech.

V instalační události je možno např. rozšířit kartu "Příkazy nabídky" hlavního menu DOPLŇKY o volání nějaké subroutiny bez parametrů. To totiž asi nejpřesněji odpovídá volání makra. V odinstalační události se nesmí zapomenout ji zase odebrat, protože při opětných připojováních doplňku by se položky karty zdvojovaly, ztrojovaly ...

Níže jsou uvedeny příklady obou subroutin. Inicializační subroutina přidá jako první položku příkazové tlačítko s textem Hom Projekce :

 

' Definice konstant, aby se nemusely psát přímo do příkazů
Const JmMbar = "Worksheet Menu Bar"
Const JmTools = "&Nástroje" 
Const JmMoje = "Hom Projekce ..."
 
' Instalační subroutina
Private Sub Workbook_AddinInstall()

   Dim br As CommandBar          ' Hlavní menu
   Dim cb As CommandBarPopup     ' Podmenu
   Dim bt As CommandBarButton    ' Položka (pod)menu
   
   Set br = Application.CommandBars(JmMbar)
   Set cb = br.Controls(JmTools)
   
   On Error Resume Next
   Set bt = cb.Controls(JmMoje)
   If Err.Number <> 0 Then
      ' Přidám PŘED PRVNÍ položku moji vlastní
      Set bt = cb.Controls.Add(Type:=msoControlButton, before:=1)
   End If
   On Error GoTo 0
   
   ' Teď to hlavní: vlastnosti nově přidané položky podmenu:
   With bt
      .Caption = JmMoje                 ' Text položky podmenu
      .OnAction = "AddInCode.AboutMe"   ' Co se má stát při použití
   End With

End Sub
 
' Odinstalační subroutina
Private Sub Workbook_AddinUninstall()
   
   Dim br As CommandBar          ' Hlavní menu
   Dim cb As CommandBarPopup     ' Podmenu
   
   Set br = Application.CommandBars(JmMbar)
   Set cb = br.Controls(JmTools)
   On Error Resume Next
   cb.Controls(JmMoje).Delete    ' Odstranění položky
   
End Sub

 

To nejdůležitější je v příkaze

bt.OnAction = "AddInCode.AboutMe"

Sděluje se jím, že v případě použití této položky (= "kliknutí" na text "Hom Projekce") se spustí subroutina (bez parametrů) AboutMe v modulu AdInCode (viz obr. 9). Protože jde o spuštění subroutiny bez parametrů, jde o - v terminologii MS Office - spuštění makra. Co je obsahem subroutiny, je věcí autora doplňku. Zde konkrétně subroutina volá zobrazení formuláře s informacemi typu About Doplněk.

V popsaném příkladě je situace následující:

 



Obr. 18: Nabídky po připojení

 

Závěr

Výsledky

Článek popsal mechanismus tvorby tzv. doplňku (Add In) použitelného programem Excel v jeho sešitech. Rozlišil vlastní funkce, makra, a dále programové jednotky definované a používané jen uvnitř doplňku. Poukázal na některé aspekty použití doplňku pro vkládání oblastí a objektů. Konečně pak zde zpřístupňuje ukázku konkrétní realizace doplňku, na níž lze založit vlastní aplikace tohoto typu.

 

 

 

Rev. 6 / 2016