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.
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í.
Č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é.
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.
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?
Podle zkušeností autora se při vytváření doplňku jako nejméně problémový ukázal tento postup:
Vytvoří se a udržuje "normální" sešit Excelu (soubor s příponou XLS resp. XLSM). Při přípravě se však pracuje s vědomím, že z tohoto sešitu přímo využije sešit doplňku využívající pouze funkce. Specifika takového sešitu viz níže.
Tento sešit se uloží jako doplněk pomocí hlavního menu Soubor, položka Uložit jako. V dialogovém formuláři se nejprve vybere typ ukládaného souboru (XLA resp. XLAM - viz obr. 3), pak adresář a nakonec označení souboru.
V takto vytvořeném souboru XLA resp. XLAM (který lze otevřít samostatně Excelem) je možno nastavit některé vlastnosti - viz níže.
Při změně doplňku se provede změna původního XLS resp. XLSM souboru a ten se znovu uloží jako tentýž doplněk (přepíše původní).
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.
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.
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
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:
|
a po aktivaci označit požadované vlastnosti zabezpečení na záložce Protection: |
|
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.
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.
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.
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:
Doplněk musí být uživatelem jednoduše připojitelný, zvláště jednoduše vyhledatelný a identifikovatelný.
Bez další programové úpravy jsou z doplňku přístupné pouze funkce.
Volání subroutin zajistit lze, avšak za cenu programování inicializačních a ukončovacích událostí.
V následujících odstavcích jsou uvedené body popsána podrobněji.
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ě:
|
a po aktivaci vyplnit pole Název a Komentář: |
|
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ů.
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):
|
pravý click kamkoliv do Průzkumníka projektu zobrazí kontextové menu, v něm je podmenu Insert (vlož): |
|
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.
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):
Jméno funkce je zobrazeno ve formuláři Vložit funkci v sekci Vlastní (a také v sekci Vše)
Jména formálních parametrů jsou zobrazena v tučném záhlaví nápovědy dole
Stručný popis funkce je zobrazen pod tučným záhlavím funkce
Popis funkce se zadá relativně nejsložitěji. Je přitom vhodné znát alespoň rámcově strukturu objektu Workbook ("sešit Excelu"):
Sešit obsahuje kolekci modulů.
Modul obsahuje kolekci programových jednotek.
Programová jednotka má několik datových polí (vlastností).
Jednou z vlastností je Popis (angl. Description).
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:
|
aktivací se pak spustí program 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.
|
|
|
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):
|
Aktivací se zobrazí formulář volitelných vlastností (Options): |
|
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.
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í
Č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