Microsoft Office Excel
uređuje Dragan Grbić

Kako napraviti programski dodatak za Excel

Nivo:
NIVO 3 - Prilozi na ovom nivou su namenjeni naprednim korisnicima, onima koji se već dobro snalaze u programima i onima koji već imaju iskustva u naprednim tehnikama. Ovom nivou pripadaju preporuke i trikovi koje bi trebalo da savlada svako ko teži dugotrajnom i efikasnom radu u Office programima.

Dokazaćemo vam da izrada programskog dodatka (add-in) nije nikakav bauk. Napravićemo jedan prosti primer, pa ćemo se malo poigrati opcijama dok ga ne dovedemo do željenog oblika.

Ovog puta idemo redom, uz pretpostavku da ne znate mnogo o tehnologiji koja stoji iza programskih dodataka. Svakako, većina će prepoznati da se taj proces malo razlikuje od pripreme nekog uobičajenog VBA rešenja - razlika je uglavnom u tome što programski dodatak ne sadrži u sebi ništa osim programskog jezgra, a ubičajeno je da se napravi tako da rezultujuća datoteka angažuje tako da ne bude menjana radom korisnika. Ovde postoje moguće varijacije, ali one su veoma retke i ovog puta se njima nećemo baviti. Primer će biti trivijalan: angažovaćemo nekoliko prostih funkcija koje ćemo na kraju izložiti korisniku na redovno korišćenje.

Jedna od poenti ovakvog pristupa je u tome što možete prikupiti sve namenske funkcije koje koristite u više rešenja - nešto poput programske biblioteke koja funkcioniše sa jednog mesta. Naime, kada programski dodatak bude jednom angažovan, možete ga smatrati punim delom Excela na platformi na kojoj funkcioniše.

NAPOMENA: u ovom primeru ćemo koristiit Excel 2003 i uobičajene tipove datoteka koji važe u "tradicionalnim" verzijama programa. Na kraju ćete vedeti koje specifičnosti važe za Excel 2007, pa ćete moći da probate istu tehniku i u toj verziji programa.

Prvi korak - pisanje jezgra

Programski dodatak ćemo napraviti kao kolekciju funkcija; to znači da na radnim listovima neće biti nikakvog sadržaja. Otvorite novu radn usvesku, odmah otvorite VBE (Alt+F11), pa komandom menija Insert / Module otvorite novi modul. Tamo iskopirajte sledeći sadržaj:

Function saberi(A As Long, B As Long) As Long
  saberi = A + B
End Function

Function oduzmi(A As Long, B As Long) As Long
  oduzmi = A - B
End Function

Function pomnozi(A As Long, B As Long) As Long
  pomnozi = A * B
End Function

Function podeli(A As Long, B As Long) As Long
  podeli = A / B
End Function

Function zapetljaj(A As Long, B As Long) As Long
  C = Abs(Int((2 * A + B + 19) + B / A))
  zapetljaj = Abs(((A + B + C) * (C / (Abs(A) + Abs(B) + 1)) - A - B) Mod 23)
End Function

Kao što vidite, reč je o "vrlo sofisticiranim" funkcijama koje predstavljaju vrhunsko dostignuće matematike (: (ako ne verujete u to, pogledajte mali zadatak pri kraju ovog priloga). Da biste ispitali tačnost rad afunkcija, pređite na radni list ove radne sveske, u ćelije A1 i A2 unesite neke brojeve, a onda u neke susedne ćelije unesite ove formule:

=SABERI(A1;A2)
=ODUZMI(A1;A2)
=POMNOZI(A1;A2)
=PODELI(A1;A2)
=ZAPETLJAJ(A1;A2)

Ukoliko ste sve izveli kako valja, dobićete neke rezultate. Varirajte brojeve u A1 i A2 da biste se uverili da sve funkcioniše kako treba. Na kraju, obavezno izbrišite celokupan sadržaj radnog lista. Sačuvajte radnu svesku kao datoteku pod imenom operacije.xls. Ovo će biti radna varijanta rešenja: korisna je praksa da polazište za programski dodatak uvek čuvate kao prostu radnu svesku, što će biti posebno korisno ukoliko planirate da zaštitite programski dodatak i takvog ga isporučite drugim korisnicima. Pre čuvanja, učinite još jedan koristan potez: otvorite dijalog File / Properties (Datoteka / Svojstva), pređite na drugu karticu Summary (Rezime), pa u polje komentara upišite neki opis sadržaja budućeg programskog dodatka.

Drugi korak - izrada prototipa

Prvi potez u izradi programskog dodatka nije obavezan, ali je svakako veoma poželjno da ga izvedete; objašnjenje razloga je predugo i dosadno. Uglavnom, skoknite u VBE, pa pokrenite komandu menija Debug / Compile VBAProject.

Nakon što ste sačuvali osnovnu radnu svesku, vreme je za prototip: ponovo otvorite dijalog File / Save As (Datoteka / Sačuvaj kao), zadržite ime, ali odmah pređite u padajuću listu Save as type (Sačuvaj kao tip), pa pri dnu spiska pronađite opciju Microsoft Office Excel Add-In (*.xla). Čim odaberete tu opciju, primetićete da vas je program uputio u folder AddIns u vašem profilu (to je lokacija \Documents and Settings\(korisnik)\Application Data\Microsoft\AddIns\), što je veoma korisna stvar - kasnije ćete videti zašto je to bitno. Uglavnom, reč je o podrazumevanoj lokaciji za smeštanje programskoh dodataka i treba da prihvatite to. Najzad, potvrdite čuvanje, a zatim zatvorite Excel, pa ga ponovo otvorite. Došlo je vreme za angažovanje programskog dodatka: otvorite dijalog Tools / Add-Ins (Alatke / Programski dodaci).

Izvinite zbog ovog "zatvorite, pa otvorite Excel": razlog za to ću objasniti odmah. Reč je o tome da će program ovako sigurno "užljebiti" prisustvo novog programskog dodatka. Strogo formalno, ovaj potez nije bio neophodan: pronašli biste programski dodatak na spisku pretragom ispod dugmeta Browse (Potraži). Legendarnim i visoko sofisticiranim tehnološkim postupkom poznatim kao "izađi, pa uđi" se služimo u cilju simulacije na računaru neukog korisnika.

Ako ste sve uradili kako treba, na spisku ćete videti i stavku Operacije; ako obeležite stavku, ispod okna ćete videti opis koji ste ranije uneli u polje komentara u dijalogu svojstava; vašim korisnicima će to svakako biti od koristi! Najzad, uključite polje za potvrdu pored imena vašeg dodatka i zatvorite dijalog potvrdom na OK. Preostalo je samo još da ponovite test koji ste izveli nakon upisa funkcija. Problema ne bi trebalo da bude: završili ste izradu prototipa programskog dodatka.

Takođe, primetite jednu važnu osobinu. Mesto na kome se nalaze datoteke programskih dodataka je tzv. pouzdana lokacija, što znači da će svi VBA makroi (i procedure i funkcije) biti izvršavani bez upozorenja o prisustvu makroa, kao što se to uobičejeno dešava ako ste definisali pravo rada makroa Medium (više o tome u posebnom prilogu). Ovde može da se postavi pitanje poverenja koje korisnik treba da ukaže programskom dodatku - ali to uglavnom nije tehničko pitanje, nego stvar organizacije i dogovora u procesu angažovanja nekog rešenja.

Treći korak - zaštita sadržaja

Korisnici su veoma "inventivni" po prirodi i veoma su skloni da čeprkaju po delovima struktura koje ne razumeju. Niko nikad nije uspeo da objasni zašto je to tako, osim što možemo da primetimo da je sklonost ka izmeni delova aplikacije koje ne bi trebalo dirati obrnuto proporcionalna iskustvu i poznavanju tehničkih detalja. Drugim rečima, potrebno je da zaštitite svoj programski dodatak. Problem je u tome što je nezaštićen programski dodatak izložen pogledima (što je uglavnom manji problem) i izmenama (što je uvek veliki problem). Korisnik može da otvori VBE i da pristupi modulu programskog dodatka navigacijom u oknu Project. Promenom programskog koda ili čak brisanjem delova ili celog modula, korisnik će narušiti funkcionalnost koju ste projektovali. Ako baš hoćete, ne da je potrebno, nego morate izvesti zaštitu.

Vratite se u polaznu XLS verziju vašeg rešenja, uđite u VBE, u oknu Project obeležite (ako već nije obeležena) stavku koja se odnosi na datoteku operacije.xls, pa otvorite dijalog Tools / VBAProject Properties. Pređite na karticu Protection, uključite polje za potvrdu Lock project for viewing, a zatim dvaput unesite odabranu lozinku u polja na dnu dijaloga. Sačuvajte promene u radnoj svesci, pa ponovite postupak izrade programskog dodatka. Restartujte Excel, proverite da li je programski dodatak angažovan, pa opet idite u VBE. Pokušajte da navigacijom u oknu project pristupite sadržaju modula programskog dodatka. Videćete da program zahteva upis lozinke kako biste mogli da vidite sadržaj projekta. To je to: vaš programski dodatak je sada zaštićen.

Zapitaćete se koliki je stepen sigurnosti ovakve zaštite. Pošteno: slab je. Dostupne su divlje alatke kojima se ova zaštita može razbiti i tu nećete moći da uradite bogzna šta. Ali, hajde da se okanemo paranoičnih ideja: glavni predmet zaštite je očuvanje funkcionalnosti vašeg programskog dodatka. Pritom govorimo o dovoljnoj zaštiti: ova zaštita je praktično idealna spram onih koji su u VBE dospeli nemajući pametnija posla.

Četvrti korak - isporuka programskog dodatka

Da se odmah razumemo: ovo nije lak proces. Korisnicima nije podrazumevano dostupna strktura u kojoj se programski dodaci čuvaju, a manipulacija već angažovanim sadržajima nije nešto čemu bi neuki korisnici trebalo da pribegavaju. Nekakav opšti scenario, izvesno najbezazleniji od svih, jeste isporuka osnovne XLS datoteke, uz preporuku korisniku da izvede sve one korake koje sam opisao u odeljku o izradi prototipa. Stvar se unekoliko komplikuje zato što se obični programski dodaci angažuju u profilu korisnika, a ne na nivou celog programa; zato će se možda desiti da, ako više korisnika koristi isti računar u posebnim profilima, jedan korisnik ima, a drugi nema angažovan dodatak. Upravo to je i razlog što vam savetujemo da pribegnete prostom vođenju korisnika kroz korake čuvanja radne sveske kao add-in, nakon čega treba sami da uključe njegovo dejstvo u dijalogu za kontrolu programskih dodataka.

Postoje, pak, neke tehnike koje biste mogli da angažujete u samom jezgru VBA rešenja, ali to prilično komplikuje celo rešenje i trebalo bi da bude predmet posebne diskusije. Čak, pitanje je da li bi takav pristup bio celishodan. Bez ljutnje: to ćemo ostaviti za neku drugu priliku.

Programski dodaci u Excelu 2007

Kada je reč o kreiranju programskih dodataka u Excelu 2007, nema principijelnih razlika u postupku. Polaznu radnu svesku sa sadržajem rešenja ćete angažovati na isti način kao što je opisano. Jedina prava razlika je u tome što je poželjno da radnu svesku sačuvate kao tip XLSM, što je novi rezervisani tip za radne sveske sa makroima. Dakako, to ne morate da radite, jer ceo proces je i dalje raspoloživ u starim formatima. Poenta je u tome da radnu svesku sa makroima ne možete sačuvati kao tip XLSX.

Jedina principijelna razlika je u mestu angažovanja programskog dodatka: da biste pristupili dijalogu za uključenje programskog dodatka, otvorite dijalog Office > Excel Options (Office > Excel opcije). U levom oknu birajte stavku Add-Ins (Programski dodaci), pa ćete videti listu raspoloživih dodataka svih vrsta; posebno su izlistani aktivni programski dodaci, a vaš budući dodatak bi trebalo da vidite u listi neaktivnih. Kliknite na dugme Go (Idi) i najzad ćete pristupiti sad već poznatom dijalogu, odakle se sve radi na isti način kao i u Excelu 2003.

Kuriozitet, čisto uzgred

Postoji detalj za koji mogu da se opkladim da ga ne zna više od jednog procenta onih koji su već sposobni da naprave programski dodatak. To nisam znao ni ja, dok pre par godina nisam naišao na jedan prilično zastareli članak na Internetu (odnosio se na Excel 97). Elem, da li ste znali da XLS datoteka može da se ponaša u potpunosti kao programski dodatak? Pritom datoteka ostaje u svom prirodnom formatu, ali kad je otvorite, radna sveska neće biti vidljiva, ali sve VBA komponente će se ponašati kao da je reč o instaliranom programskom dodatku!

Evo, probajte i sami: otvorite XLS datoteku od koje želite da napravite programski dodatak; pređite u VBE, u oknu Project obeležite objekat ThisWorkbook. Ako se ne vidi okno Properties, otvorite ga prečicom F4; u oknu nađite svojstvo IsAddIn i promenite mu vrednost sa False na True. Hop-la: kad se vratite u prozor Excela, nigde radne sveske! Zapravo - tu je, ali sada je skrivena, što je podrazumevana karakteristika za programske dodatke. Zatvorite Excel: program će vas pitati da li želite da sačuvate izmene u radnoj svesci; potvrdite to. Opet otvorite Excel, učitajte tu datoteku - opet se ništa ne vidi; idite u VBE i ugledaćete "programski dodatak" u oknu Project...

Sad samo ostaje ključno pitanje - zašto biste, pod kapom nebeskom, koristili ovakav pristup u gradnji programskih dodataka? Odgovor je najprostiji na svetu: takav "programski dodatak" je aktivan samo dok otvorena sesija Excela, nemate potrebe da uključujete i isključujete aktivnost dodatka. To može da posluži zgodno u toku testiranja više verzija istog programskog dodatka.

A, da: obećao sam vam mali matematički zadatak. Posvećujem ga svima onima koji misle da su četiri osnovne računske operacije suviše trivijalne za njihov um.
Upotrebite brojeve 1, 3, 4 i 6 po jednom i tačno jednom (kao brojeve, a ne kao cifre!) i kombinujte ih pomoću četiri osnovne računske operacije u proizvoljnoj upotrebi, sa ili bez upotrebe zagrada, prema uobičajenim normama aritmetike, da biste dobili tačan rezultat 24.
Malo upozorenje za one sklone potpitanjima. Prvo, ovo nije nikakav kafansko-šibicarski trik, nego pravi zadatak: rešenje postoji. Drugo, kad dođete do rešenja, nikad više nećete osnovne računske operacije nazvati banalnim. I treće, slobodni ste da nas kontaktirate kada nađete rešenje (kako bismo vam čestitali) ili ako budete tvrdili da ono ne postoji (kako bismo vas razuverili u to).