Sadržaj
Čak i ako ne znate programirati, postoji mnogo mjesta (knjige, web stranice, forumi) gdje možete pronaći gotov VBA makro kod za veliki broj tipičnih zadataka u Excelu. Prema mom iskustvu, većina korisnika prije ili kasnije prikupi svoju osobnu kolekciju makronaredbi za automatizaciju rutinskih procesa, bilo da se radi o prevođenju formula u vrijednosti, prikazu zbrojeva riječima ili zbrajanju ćelija prema boji. I tu nastaje problem – makro kod u Visual Basicu treba negdje pohraniti kako bi se kasnije koristio u radu.
Najlakša opcija je spremiti kod makronaredbe izravno u radnu datoteku odlaskom u uređivač Visual Basic pomoću prečaca na tipkovnici drugo+F11 i dodavanje novog praznog modula putem izbornika Umetak – Modul:
Međutim, ova metoda ima nekoliko nedostataka:
- Ako ima puno radnih datoteka, a makronaredba je potrebna posvuda, poput makronaredbe za pretvaranje formula u vrijednosti, tada ćete morati kopirati kod u svakoj knjizi.
- Ne smije se zaboraviti spremite datoteku u formatu s omogućenim makronaredbama (xlsm) ili u formatu binarne knjige (xlsb).
- Prilikom otvaranja takve datoteke makro zaštita će svaki put izdati upozorenje koje treba potvrditi (dobro, ili potpuno onemogućiti zaštitu, što možda nije uvijek poželjno).
Elegantnije rješenje bilo bi stvoriti Vaš vlastiti dodatak (Excel dodatak) – zasebna datoteka posebnog formata (xlam) koja sadrži sve vaše “omiljene” makronaredbe. Prednosti ovog pristupa:
- Bit će dosta spojite dodatak jednom u Excelu – i možete koristiti njegove VBA procedure i funkcije u bilo kojoj datoteci na ovom računalu. Ponovno spremanje vaših radnih datoteka u xlsm- i xlsb-formatima stoga nije potrebno, jer. izvorni kod neće biti pohranjen u njima, već u datoteci dodatka.
- Zaštita nećete smetati ni makronaredbe. dodaci su, po definiciji, pouzdani izvori.
- Mogu zasebna kartica na vrpci programa Excel s zgodnim gumbima za pokretanje makronaredbi dodataka.
- Dodatak je zasebna datoteka. Njegovo lako nositi s računala na računalo, podijelite s kolegama ili čak prodajte 😉
Prođimo korak po korak kroz cijeli proces stvaranja vlastitog Microsoft Excel dodatka.
Korak 1. Stvorite datoteku dodatka
Otvorite Microsoft Excel s praznom radnom knjigom i spremite je pod bilo kojim prikladnim imenom (npr MyExcelAddin) u obliku dodatka s naredbom Datoteka – Spremi kao ili ključeve F12, navodeći vrstu datoteke Excel dodatak:
Imajte na umu da Excel prema zadanim postavkama pohranjuje dodatke u mapu C:UsersYour_nameAppDataRoamingMicrosoftAddIns, ali u načelu možete odrediti bilo koju drugu mapu koja vam odgovara.
Korak 2. Povezujemo stvoreni dodatak
Sada dodatak koji smo izradili u zadnjem koraku MyExcelAddin moraju biti povezani s Excelom. Da biste to učinili, idite na izbornik Datoteka – Opcije – Dodaci (Datoteka — Opcije — Dodaci), kliknite na gumb O nama (Ići) na dnu prozora. U prozoru koji se otvori kliknite gumb pregled (Pretraživati) i odredite mjesto naše datoteke dodatka.
Ako ste sve učinili kako treba, onda naš MyExcelAddin trebao bi se pojaviti na popisu dostupnih dodataka:
Korak 3. Dodajte makronaredbe dodatku
Naš dodatak je povezan s Excelom i uspješno radi, ali u njemu još nema niti jednog makroa. Napunimo ga. Da biste to učinili, otvorite uređivač Visual Basic pomoću prečaca na tipkovnici drugo+F11 ili gumbom Visual Basic kartica razvijač (Programer). Ako su kartice razvijač nije vidljivo, može se prikazati kroz Datoteka – Opcije – Postavljanje vrpce (Datoteka — Opcije — Prilagodi vrpcu).
U gornjem lijevom kutu uređivača trebao bi biti prozor Projekt (ako se ne vidi, uključite ga kroz izbornik Pogled — Istraživač projekta):
Ovaj prozor prikazuje sve otvorene radne knjige i pokrenute Microsoft Excel dodatke, uključujući naše. VBAProject (MyExcelAddin.xlam) Odaberite ga mišem i dodajte mu novi modul putem izbornika Umetak – Modul. U ovom modulu pohranit ćemo VBA kod naših makronaredbi dodataka.
Možete utipkati kôd ispočetka (ako znate programirati) ili ga kopirati s nekog već spremnog mjesta (što je puno lakše). Hajde, radi testiranja, unesite kod jednostavne, ali korisne makronaredbe u dodani prazan modul:
Nakon unosa koda ne zaboravite kliknuti na tipku za spremanje (disketa) u gornjem lijevom kutu.
Naš makro FormuleDoVrijednosti, kao što lako možete zamisliti, pretvara formule u vrijednosti u unaprijed odabranom rasponu. Ponekad se te makronaredbe također pozivaju Postupci. Da biste ga pokrenuli, morate odabrati ćelije s formulama i otvoriti poseban dijaloški okvir makronaredbe s kartice razvijač (Razvojni programer — Makronaredbe) ili tipkovni prečac drugo+F8. Ovaj prozor obično prikazuje dostupne makronaredbe iz svih otvorenih radnih knjiga, ali makronaredbe dodataka ovdje nisu vidljive. Unatoč tome, u polje možemo unijeti naziv naše procedure naziv makronaredbe (Naziv makronaredbe)a zatim pritisnite gumb trčanje (trčanje) – i naš će makro raditi:
| |
Ovdje također možete dodijeliti prečac na tipkovnici za brzo pokretanje makronaredbe - gumb je odgovoran za to Parametri (Opcije) u prethodnom prozoru Makro:
Prilikom dodjele tipki, imajte na umu da su osjetljive na velika i mala slova i raspored tipkovnice. Dakle, ako dodijelite kombinaciju kao što je Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally smjenada dobije veliko slovo.
Radi praktičnosti, također možemo dodati gumb za našu makronaredbu na alatnu traku za brzi pristup u gornjem lijevom kutu prozora. Da biste to učinili, odaberite Datoteka – Opcije – Alatna traka za brzi pristup (Datoteka — Opcije — Prilagodite alatnu traku za brzi pristup), a zatim na padajućem popisu na vrhu prozora opciju makronaredbe. Nakon toga naš makro FormuleDoVrijednosti može se postaviti na ploču pomoću gumba dodati (Dodaj) i odaberite ikonu za to pomoću gumba Promijeniti (Uredi):
Korak 4. Dodajte funkcije dodatku
Ali makropostupci, postoje i makronaredbe funkcije ili kako se već zovu UDF (User Defined Function = korisnički definirana funkcija). Kreirajmo zaseban modul u našem dodatku (naredba izbornika Umetak – Modul) i tamo zalijepite kod sljedeće funkcije:
Lako je vidjeti da je ova funkcija potrebna za izdvajanje PDV-a iz iznosa koji uključuje PDV. Nije Newtonov binom, naravno, ali poslužit će nam kao primjer da pokažemo osnovne principe.
Imajte na umu da se sintaksa funkcije razlikuje od sintakse procedure:
- koristi se konstrukcija Funkcija…. Završna funkcija umjesto toga Sub … End Sub
- iza naziva funkcije, njeni argumenti su navedeni u zagradama
- u tijelu funkcije izvode se potrebni izračuni, a zatim se rezultat dodjeljuje varijabli s imenom funkcije
Također imajte na umu da ova funkcija nije potrebna i da je nemoguće pokrenuti proceduru prethodne makronaredbe kroz dijaloški okvir makronaredbe i gumb trčanje. Takvu makro funkciju treba koristiti kao standardnu funkciju radnog lista (SUM, IF, VLOOKUP…), tj. samo unesite u bilo koju ćeliju, navodeći vrijednost iznosa s PDV-om kao argument:
… ili unesite kroz standardni dijaloški okvir za umetanje funkcije (gumb fx na traci formule), odabirom kategorije Korisnik definiran (Korisnik definiran):
Jedini neugodan trenutak ovdje je odsutnost uobičajenog opisa funkcije na dnu prozora. Da biste ga dodali, morat ćete učiniti sljedeće:
- Otvorite Visual Basic Editor tipkovničkim prečacem drugo+F11
- Odaberite dodatak na ploči Projekt i pritisnite tipku F2za otvaranje prozora Object Browser
- Odaberite svoj projekt dodatka s padajućeg popisa na vrhu prozora
- Kliknite desnom tipkom miša na funkciju koja se pojavi i odaberite naredbu Nekretnine.
- Unesite opis funkcije u prozor Opis
- Spremite datoteku dodatka i ponovno pokrenite excel.
Nakon ponovnog pokretanja, funkcija bi trebala prikazati opis koji smo unijeli:
Korak 5. Napravite karticu s dodacima u sučelju
Posljednji, iako ne obavezan, ali ugodan dodir bit će stvaranje zasebne kartice s gumbom za pokretanje naše makronaredbe, koja će se pojaviti u Excel sučelju nakon povezivanja našeg dodatka.
Informacije o karticama koje se prikazuju prema zadanim postavkama nalaze se unutar knjige i moraju biti oblikovane u posebnom XML kodu. Najlakši način za pisanje i uređivanje takvog koda je uz pomoć posebnih programa - XML uređivača. Jedan od najprikladnijih (i besplatnih) je program Maxima Novikova Ribbon XML uređivač.
Algoritam za rad s njim je sljedeći:
- Zatvorite sve prozore programa Excel kako ne bi došlo do sukoba datoteka kada uređujemo XML kod dodatka.
- Pokrenite program Ribbon XML Editor i u njemu otvorite našu datoteku MyExcelAddin.xlam
- Sa gumbom kartice u gornjem lijevom kutu dodajte isječak koda za novu karticu:
- Morate staviti prazne navodnike id našu karticu i grupu (bilo koji jedinstveni identifikator) i in oznaka – nazive naše kartice i grupe gumba na njoj:
- Sa gumbom dugme na lijevoj ploči dodajte prazan kod za gumb i dodajte mu oznake:
— oznaka je tekst na gumbu
— slikaMso — ovo je uvjetni naziv slike na gumbu. Koristio sam ikonu crvenog gumba pod nazivom AnimationCustomAddExitDialog. Imena svih dostupnih gumba (a ima ih nekoliko stotina!) mogu se pronaći na velikom broju stranica na Internetu ako tražite ključnu riječ “imageMso”. Za početak, možete otići ovdje.
- onAction – ovo je naziv procedure povratnog poziva – posebna kratka makronaredba koja će pokrenuti naš glavni makronaredbu FormuleDoVrijednosti. Ovaj postupak možete nazvati kako god želite. Dodat ćemo ga malo kasnije.
- Ispravnost svega učinjenog možete provjeriti pomoću gumba sa zelenom kvačicom na vrhu alatne trake. Na istom mjestu kliknite na gumb s disketom za spremanje svih promjena.
- Zatvorite XML uređivač trake
- Otvorite Excel, idite na Visual Basic editor i dodajte proceduru povratnog poziva našoj makronaredbi UbijFormuletako da pokreće naš glavni makro za zamjenu formula s vrijednostima.
- Spremamo promjene i, vraćajući se u Excel, provjeravamo rezultat:
To je sve – dodatak je spreman za korištenje. Napunite ga vlastitim postupcima i funkcijama, dodajte prekrasne gumbe – i postat će mnogo lakše koristiti makronaredbe u svom radu.
- Što su makronaredbe, kako ih koristiti u svom radu, gdje pronaći makro kod u Visual Basicu.
- Kako napraviti početni ekran prilikom otvaranja radne knjige u Excelu
- Što je Personal Macro Book i kako se njime služiti