Excel VBA: Programi i njihove trake

Među tekstovima iz starih brojeva koje mi čitaoci najčešće traže važno mesto zauzimaju tri članka iz 2008. godine koja se bave Excel VBA programiranjem: „Uz malo bejzika“ (PC#146), „Od bejzika do VBA“ (PC#147), i „Objekti Excel-a“ (PC#148). Nije to neobično – uvek govorim mladim programerima, pa i amaterima, da upoznaju Excel jer će uz poznavanje tog fascinantnog alata uvek imati osiguran posao, a i redovne „tezge“ u raznim firmama koje pokušavaju da (re)organizuju svoje podatke. Ta tri članka u PDF formatu možete da preuzmete sa našeg sajta pc.pcpress.rs, iz arhive pc274vba.zip koja prati ovaj tekst.

Ribbon kao adut

Excel VBA se nije bitnije promenio tokom prethodnih 12 godina – tu i tamo se pojavljivala neka nova funkcija ili objekat, ali je suština ostala ista, baš kao i programersko okruženje; reklo bi se da Microsoft smatra da tim koji dobro igra ne treba menjati. Bitna promena su formati datoteka – umesto nekadašnjeg XLS i XLA, sada postoje formati XLSX (klasična radna sveska), XLSM (radna sveska u koju su ugrađeni makroi) i XLAM, što je Excel add-in.

U ranijim tekstovima objašnjeno je zbog čega je VBA add-in najzgodniji način da distribuirate svoj VBA softver. Ostaje još pitanje kako se taj softver pokreće. U PC#146 smo objavili proceduru koja kreira tablu sa ikonama svake od funkcija programa, a koja se nalazi na Add-ins panelu Excel-a. Nove verzije Excel okruženja su nekako potisnule taj Add-ins panel – on je i dalje funkcionalan, ali sve izgleda prilično jadno, a dešava se i da panel vašeg add-in-a volšebno nestane, pa program treba ponovo da ga kreira. Mnogo bi lepše bilo rasporediti funkcije programa na pravi ribbon, dodeliti im veće sličice, uvesti i neki meni, yes/no polje i druge elemente.

Ribbon se generiše tako što odgovarajuće formatiran XML fajl ugradite u XLSM/XLAM datoteku. Svi Excel fajlovi sa četvoroslovnim ekstenzijama su zapravo ZIP arhive. Prekopirajte neki XLSM fajl u istoimeni ZIP i otvorite ga, pa ćete videti razne fajlove i foldere među kojima je i folder customUI. U njemu je fajl customUI14.xml u koji se upisuju izmene strukture ribbon-a. Da se ne biste mučili sa XML sintaksom, koristite Custom UI Editor For Microsoft Office, prilično stari i besplatni program koji možete naći i u arhivi koja prati ovaj tekst. Instalirajte ga, pokrenite i otvorite u njemu XLAM fajl (potrebno je da Excel bude zatvoren da bi moglo da se pristupi tom fajlu), pa dodate stranicu sa XML kodom u kome su definisani tasteri, kao na slici 1. U ovom primeru smo napravili ribbon PC Press koji se nalazi iza ribbon-a View i koji sadrži jednu sekciju (Proba) sa dve komande. Programski kod koji realizuje te komande je ui_prva odnosno ui_druga i nalazi se u samom add-in-u. U istom fajlu su po potrebi definisane i ikone koje će biti nacrtane na novokreiranim tasterima.

ImageMso znači da ćete koristiti neku od ikona koje već postoje u Excel-u, iz gotovo nepreglednog spiska koji ćete naći na Internetu. Za ikonu možete da koristite i neku svoju sličicu, koju ćete kreirati kao bitmapu 256×256 tačaka u PNG formatu, a onda je uključiti u XLAM fajl koristeći i dalje CustomUI editor. Tada stavljate image="ime" (umesto imageMso="ime"). Kada malo uvežbate XML, kreiraćete i menije, yes/no polja i druge elemente korisničkog interfejsa.

Ribbon koji se menja

Znatno složeniji zalogaj je pravljenje ribbon-a koji se menja u zavisnosti od učitanog fajla ili izvršavanjem neke komande. Ova ideja se često pominje na raznim sajtovima naprednih korisnika Excel-a, ali su često dati neki primeri koji ne rade, ili ogromni listinzi iz kojih je teško izvaditi ono što je ključno.

Najbolje objašnjenje sam našao kod čuvenog Mr. Spreadsheet-a (John Walkbach), u knjizi Excel 2013 Power Programming with VBA iz 2013. godine. Na osnovu tih informacija nastao je primer dyn.xlsm koji prati ovaj tekst. Da bi stvari bile jednostavnije za probu, to je samostalni Excel fajl sa ugrađenim makroima; lako ga je pretvoriti u add-in ako je potrebno.

U primeru se koristi deklaracija nalik na sliku 2. Promenljiva dynamicMenyContent je kontejner u koji, iz VBA programa, ugradite XML kod koji definiše stavke menija i programe koje te stavke pozivaju. Kad promenite vrednost promenljive (u datom primeru se to dešava kada prelazite sa sheet-a na sheet) promeni se i meni. Na ovaj način možete da promenite meni, ali ne možete da dodajete još neke tastere u njega. Stvar je u tome što postoji Menu / dynamicMenu, ali ne postoji button / dynamicButton. Ukratko, može da se napravi custom ribbon tako da se menjaju meniji na tasterima, ali ne i da se dodaju / izbacuju sami tasteri ili grupe tastera.

Plašt nevidljivosti

Ako ne očekujete „beskonačno“ varijanti ribbon-a već dve ili tri od kojih će samo jedna biti aktivna u zavisnosti od uslova, možete da napravite nekoliko tabulatora i tastera, pa onda da neke od njih proglasite nevidljivim. Pri inicijalizaciji programa podesite varijantu koja je potrebna i sakrijete ostale. To se radi sa:

pa onda kada u programu stavite Varijanta1Tab=True, taj tab se pokaže, a kad je False, ne pokaže se. Slično može da se uradi i za pojedinačne komande, menije, yes/no polja...

Za sada je sve dobro, ali je problem u tome što se u trenutku kada stavite Varijanta1Tab=True zapravo ne desi ništa. Potrebno je da izvršite komandu

Posle dosta eksperimentisanja primetio sam da se problem javlja gotovo isključivo na kućnom računaru. Kada isti kod odnesem u Redakciju i tamo ga rekompajliram, sve radi savršeno. Na notebook-u opet ne radi. Razmišljajući o tome u čemu je razlika između tih okruženja, zaključio sam da na redakcijskom računaru imam samo taj jedan probni add-in, dok kod kuće i na notebook-u imam nekoliko add-in-ova koje sam pravio za razne ljudi i razne poslove. Kada deinstaliram sve te „nepotrebne“ add-in-ove, odjednom ni na kućnom računaru nema problema. Vratim samo jedan, opet ima problema.

Daljom analizom sam zaključio da Excel zapravo pozove proceduru iz nekog drugog add-in-a, pošto se kod mene većina tih procedura zove slično (kad radim neki novi posao tog tipa, uzmem stari add-in pa prepravljam, neću valjda da pišem sve od nule). I onda mi sine ideja da sve procedure u tom delu add-in-a koje se bave interfejsom treba deklarisati kao private (a ne public, kako sam ih ja deklarisao misleći da treba da budu public da bi se videle od spolja). Pokazalo se da ne moraju da budu public, a reklo bi se i da ne treba da budu – kada sam ih sve prebacio na private, odjednom je problem – barem u konkretnom add-in-u – nestao, tj. javlja se samo jednom posle novog kompajliranja, a nakon toga „u beskraj“ dobro radi. Samo treba strpljivo proći kroz čitav kod i sve što može da bude private staviti da je private.

I na kraju, treba li praviti promenljivi ribbon? Izbegnite ga ako ikako možete, napravite po potrebi nekoliko raznih ribbon-a za razne primene i u njih rasporedite potrebne komande. Ako zaključite da su promenljive trake neophodne, koristite tehnike o kojima smo govorili, mada bi bilo lepo da u nekoj od sledećih verzija Excel-a budu uvedene deklaracije kao dynamicButton i dynamicCheckBox i da se sredi problem sa osvežavanjem trake posle Traka.invalidate.