vsebina
Tudi če ne znate programirati, obstaja veliko mest (knjige, spletna mesta, forumi), kjer lahko najdete že pripravljeno makro kodo VBA za ogromno tipičnih opravil v Excelu. Po mojih izkušnjah večina uporabnikov prej ali slej zbere svojo osebno zbirko makrov za avtomatizacijo rutinskih procesov, pa naj gre za prevajanje formul v vrednosti, prikaz vsot z besedami ali seštevanje celic po barvah. In tu nastopi problem – kodo makra v Visual Basicu je treba nekje shraniti, da jo lahko kasneje uporabimo pri delu.
Najlažja možnost je, da kodo makra shranite neposredno v delovno datoteko, tako da obiščete urejevalnik Visual Basic z bližnjico na tipkovnici druga+F11 in dodajanje novega praznega modula prek menija Vstavi – Modul:
Vendar pa ima ta metoda več pomanjkljivosti:
- Če je delovnih datotek veliko in je povsod potreben makro, na primer makro za pretvorbo formul v vrednosti, boste morali kodo kopirati v vsaki knjigi.
- Ne sme se pozabiti shranite datoteko v formatu, ki podpira makro (xlsm) ali v binarni knjižni obliki (xlsb).
- Ko odprete takšno datoteko makro zaščita bo vsakič izdal opozorilo, ki ga je treba potrditi (no, ali popolnoma onemogočiti zaščito, kar morda ni vedno zaželeno).
Elegantnejša rešitev bi bila ustvarjanje svoj dodatek (Excel dodatek) – ločena datoteka posebnega formata (xlam), ki vsebuje vse vaše »priljubljene« makre. Prednosti tega pristopa:
- Dovolj bo povežite dodatek enkrat v Excelu – in njegove postopke in funkcije VBA lahko uporabite v kateri koli datoteki v tem računalniku. Ponovno shranjevanje delovnih datotek v formatih xlsm in xlsb torej ni potrebno, ker. izvorna koda ne bo shranjena v njih, ampak v datoteki dodatka.
- Zaščita tudi makri vas ne bodo motili. dodatki so po definiciji zaupanja vredni viri.
- Lahko ločen zavihek na Excelovem traku z lepimi gumbi za zagon makrov dodatkov.
- Dodatek je ločena datoteka. Njegovo enostaven za prenašanje iz računalnika v računalnik, delite s sodelavci ali celo prodajate 😉
Oglejmo si korak za korakom skozi celoten postopek ustvarjanja lastnega dodatka Microsoft Excel.
Korak 1. Ustvarite datoteko dodatka
Odprite Microsoft Excel s praznim delovnim zvezkom in ga shranite pod poljubnim ustreznim imenom (npr MyExcelAddin) v obliki dodatka z ukazom Datoteka – Shrani kot ali ključi F12, ki določa vrsto datoteke Dodatek za Excel:
Upoštevajte, da Excel privzeto shrani dodatke v mapo C:UsersYour_nameAppDataRoamingMicrosoftAddIns, vendar lahko načeloma določite katero koli drugo mapo, ki vam ustreza.
Korak 2. Povezujemo ustvarjeni dodatek
Zdaj dodatek, ki smo ga ustvarili v zadnjem koraku MyExcelAddin mora biti povezan z Excelom. Če želite to narediti, pojdite v meni Datoteka – Možnosti – Dodatki (Datoteka — Možnosti — Dodatki), kliknite na gumb O meni (pojdi) na dnu okna. V oknu, ki se odpre, kliknite gumb pregled (Prebrskaj) in določite lokacijo naše datoteke dodatka.
Če ste naredili vse prav, potem naš MyExcelAddin mora biti prikazan na seznamu razpoložljivih dodatkov:
3. korak. V dodatek dodajte makre
Naš dodatek je povezan z Excelom in uspešno deluje, vendar v njem še ni niti enega makra. Napolnimo ga. Če želite to narediti, odprite urejevalnik Visual Basic z bližnjico na tipkovnici druga+F11 ali z gumbom Visual Basic tab razvijalec (Razvijalec). Če zavihki razvijalec ni viden, lahko se prikaže skozi Datoteka – Možnosti – Nastavitev traku (Datoteka — Možnosti — Prilagodi trak).
V zgornjem levem kotu urejevalnika bi moralo biti okno Projekt (če ni viden, ga vklopite prek menija Pogled — Raziskovalec projektov):
To okno prikazuje vse odprte delovne zvezke in delujoče dodatke Microsoft Excel, vključno z našimi. VBAProject (MyExcelAddin.xlam) Izberite ga z miško in mu preko menija dodajte nov modul Vstavi – Modul. V tem modulu bomo shranili kodo VBA naših makrov dodatkov.
Kodo lahko vtipkate od začetka (če znate programirati) ali pa jo kopirate od nekje že pripravljeno (kar je veliko lažje). Za testiranje vnesimo kodo preprostega, a uporabnega makra v dodan prazen modul:
Po vnosu kode ne pozabite klikniti na gumb za shranjevanje (disketo) v zgornjem levem kotu.
Naš makro FormuleVVrednosti, kot si zlahka predstavljate, pretvori formule v vrednosti v vnaprej izbranem obsegu. Včasih se ti makri tudi kličejo Postopki. Če ga želite zagnati, morate izbrati celice s formulami in odpreti posebno pogovorno okno Makri iz zavihka razvijalec (Razvijalec — Makri) ali bližnjico na tipkovnici druga+F8. Običajno to okno prikazuje razpoložljive makre iz vseh odprtih delovnih zvezkov, vendar makri dodatkov tukaj niso vidni. Kljub temu lahko v polje vpišemo ime našega postopka ime makra (Ime makra)in nato kliknite gumb Run (teči) – in naš makro bo deloval:
| |
Tukaj lahko dodelite tudi bližnjico na tipkovnici za hiter zagon makra - za to je odgovoren gumb parametri (Opcije) v prejšnjem oknu Makro:
Pri dodeljevanju tipk ne pozabite, da so občutljive na velike in male črke ter na razporeditev tipkovnice. Torej, če dodelite kombinacijo, kot je Ctrl+Й, potem boste pravzaprav v prihodnje morali poskrbeti, da imate vklopljeno postavitev in pritisniti dodatno Shiftda bi dobili veliko začetnico.
Za udobje lahko dodamo tudi gumb za naš makro v orodno vrstico za hitri dostop v zgornjem levem kotu okna. Če želite to narediti, izberite Datoteka – Možnosti – Orodna vrstica za hitri dostop (Datoteka — Možnosti — Prilagodi orodno vrstico za hitri dostop), nato pa na spustnem seznamu na vrhu okna možnost Makri. Po tem naš makro FormuleVVrednosti lahko postavite na ploščo z gumbom Dodaj (Add) in z gumbom izberite ikono zanj Spreminjanje (Uredi):
4. korak. Dodajte funkcije v dodatek
Ampak makropostopki, tukaj so tudi funkcijski makri ali kakor se že imenujejo UDF (Uporabniško določena funkcija = uporabniško določena funkcija). Ustvarimo ločen modul v našem dodatku (menijski ukaz Vstavi – Modul) in tja prilepite kodo naslednje funkcije:
Preprosto je videti, da je ta funkcija potrebna za pridobivanje DDV iz zneska, vključno z DDV. Seveda ne Newtonov binom, vendar nam bo služil kot primer za prikaz osnovnih principov.
Upoštevajte, da se sintaksa funkcije razlikuje od sintakse postopka:
- uporablja se konstrukcija Funkcija …. Končna funkcija Namesto Sub … End Sub
- za imenom funkcije so njeni argumenti navedeni v oklepajih
- v telesu funkcije se izvedejo potrebni izračuni, nato pa se rezultat dodeli spremenljivki z imenom funkcije
Upoštevajte tudi, da ta funkcija ni potrebna in je nemogoče zagnati kot prejšnji postopek makra skozi pogovorno okno Makri in gumb Run. Takšno makro funkcijo je treba uporabiti kot standardno funkcijo delovnega lista (SUM, IF, VLOOKUP…), torej samo vnesti v poljubno celico in kot argument navesti vrednost zneska z DDV:
… ali vstopite skozi standardno pogovorno okno za vstavljanje funkcije (gumb fx v vrstici s formulami), izbiro kategorije Uporabnik definiran (Uporabnik definiran):
Edini neprijeten trenutek tukaj je odsotnost običajnega opisa funkcije na dnu okna. Če ga želite dodati, boste morali narediti naslednje:
- Odprite urejevalnik Visual Basic z bližnjico na tipkovnici druga+F11
- Izberite dodatek na plošči Projekt in pritisnite tipko F2da odprete okno Object Browser
- Na spustnem seznamu na vrhu okna izberite svoj projekt dodatka
- Z desno miškino tipko kliknite funkcijo, ki se prikaže, in izberite ukaz Nepremičnine.
- V okno vnesite opis funkcije Opis
- Shranite datoteko dodatka in znova zaženi excel.
Po ponovnem zagonu bi morala funkcija prikazati opis, ki smo ga vnesli:
5. korak. V vmesniku ustvarite zavihek z dodatki
Zadnji, čeprav ne obvezen, a prijeten dotik bo ustvarjanje ločenega zavihka z gumbom za zagon našega makra, ki se bo pojavil v vmesniku Excel po povezavi našega dodatka.
Informacije o zavihkih, ki so privzeto prikazani, so vsebovane v knjigi in morajo biti oblikovane v posebni kodi XML. Najlažji način za pisanje in urejanje takšne kode je s pomočjo posebnih programov - urejevalnikov XML. Eden najbolj priročnih (in brezplačnih) je program Maxima Novikova Urejevalnik XML traku.
Algoritem za delo z njim je naslednji:
- Zaprite vsa Excelova okna, da ne pride do konflikta datotek, ko urejamo kodo XML dodatka.
- Zaženite program Ribbon XML Editor in v njem odprite našo datoteko MyExcelAddin.xlam
- Z gumbom Kartice v zgornjem levem kotu dodajte delček kode za nov zavihek:
- Vstaviti morate prazne narekovaje id naš zavihek in skupina (poljubni edinstveni identifikatorji) in v nalepka – imena našega zavihka in skupine gumbov na njem:
- Z gumbom Gumb na levi plošči dodajte prazno kodo za gumb in mu dodajte oznake:
— etiketa je besedilo na gumbu
— slikaMso — to je pogojno ime slike na gumbu. Uporabil sem ikono rdečega gumba z imenom AnimationCustomAddExitDialog. Imena vseh razpoložljivih gumbov (in teh je nekaj sto!) lahko najdete na velikem številu spletnih mest na internetu, če iščete ključne besede "imageMso". Za začetek lahko greš sem.
- onAction – to je ime postopka povratnega klica – posebnega kratkega makra, ki bo zagnal naš glavni makro FormuleVVrednosti. Ta postopek lahko imenujete kakor koli želite. Dodali ga bomo malo kasneje.
- Pravilnost vsega opravljenega lahko preverite z gumbom z zeleno kljukico na vrhu orodne vrstice. Na istem mestu kliknite gumb z disketo, da shranite vse spremembe.
- Zaprite urejevalnik XML traku
- Odprite Excel, pojdite v urejevalnik Visual Basic in našemu makru dodajte postopek povratnega klica KillFormulastako da zažene naš glavni makro za zamenjavo formul z vrednostmi.
- Shranimo spremembe in po vrnitvi v Excel preverimo rezultat:
To je vse – dodatek je pripravljen za uporabo. Napolnite ga s svojimi lastnimi postopki in funkcijami, dodajte čudovite gumbe – in uporaba makrov pri vašem delu bo postala veliko lažja.
- Kaj so makri, kako jih uporabiti pri svojem delu, kje dobiti kodo makra v Visual Basicu.
- Kako narediti pozdravni zaslon pri odpiranju delovnega zvezka v Excelu
- Kaj je osebna makro knjiga in kako jo uporabljati