sadržaj
Čak i ako ne znate programirati, postoji mnogo mjesta (knjige, web stranice, forumi) na kojima možete pronaći gotov VBA makro kod za ogroman broj tipičnih zadataka u Excelu. Prema mom iskustvu, većina korisnika prije ili kasnije prikupi svoju ličnu kolekciju makroa za automatizaciju rutinskih procesa, bilo da se radi o prevođenju formula u vrijednosti, prikazivanju zbroja u riječima ili sabiranju ćelija po boji. I tu nastaje problem – makro kod u Visual Basicu mora biti pohranjen negdje da bi se kasnije koristio u radu.
Najlakša opcija je da sačuvate makro kod direktno u radnoj datoteci tako što ćete otići u Visual Basic editor koristeći prečicu na tastaturi alt+F11 i dodavanje novog praznog modula preko menija Umetak – Modul:
Međutim, ova metoda ima nekoliko nedostataka:
- Ako ima puno radnih datoteka, a makro je potreban posvuda, kao što je makro za pretvaranje formula u vrijednosti, tada ćete morati kopirati kod u svakoj knjizi.
- Ne smije se zaboraviti sačuvajte datoteku u formatu omogućenom za makroe (xlsm) ili u binarnom formatu knjige (xlsb).
- Prilikom otvaranja takve datoteke makro zaštita će svaki put izdati upozorenje koje treba potvrditi (pa, ili potpuno onemogućiti zaštitu, što možda nije uvijek poželjno).
Elegantnije rješenje bi bilo stvaranje vaš vlastiti dodatak (Excel dodatak) – zasebna datoteka posebnog formata (xlam) koja sadrži sve vaše „omiljene“ makroe. Prednosti ovog pristupa:
- Biće dovoljno povežite dodatak jednom u Excelu – i možete koristiti njegove VBA procedure i funkcije u bilo kojoj datoteci na ovom računaru. 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će vam smetati ni makroi. dodaci su, po definiciji, pouzdani izvori.
- Mogu odvojena kartica na Excel vrpci sa lijepim dugmadima za pokretanje makronaredbi dodataka.
- Dodatak je zasebna datoteka. Njegovo lako za nošenje od kompjutera do kompjutera, podijelite sa kolegama ili čak prodajte 😉
Prođimo kroz cijeli proces kreiranja vlastitog Microsoft Excel dodatka korak po korak.
Korak 1. Kreirajte datoteku dodatka
Otvorite Microsoft Excel sa praznom radnom sveskom i sačuvajte je pod bilo kojim odgovarajućim imenom (npr MyExcelAddin) u formatu dodataka s naredbom Datoteka – Sačuvaj kao ili ključeve F12, navodeći tip datoteke Excel dodatak:
Imajte na umu da Excel po defaultu pohranjuje dodatke u mapu C:UsersYour_nameAppDataRoamingMicrosoftAddIns, ali, u principu, možete odrediti bilo koju drugu mapu koja vam odgovara.
Korak 2. Povezujemo kreirani dodatak
Sada dodatak koji smo kreirali u zadnjem koraku MyExcelAddin moraju biti povezani na Excel. Da biste to učinili, idite na meni Datoteka – Opcije – Dodaci (Datoteka — Opcije — Dodaci), kliknite na dugme Oko (idi) na dnu prozora. U prozoru koji se otvori kliknite na dugme pregled (Pregledaj) i odredite lokaciju naše datoteke dodatka.
Ako ste uradili sve kako treba, onda naše MyExcelAddin bi se trebao pojaviti na listi dostupnih dodataka:
Korak 3. Dodajte makroe u dodatak
Naš dodatak je povezan s Excelom i uspješno radi, ali u njemu još nema ni jednog makroa. Hajde da ga napunimo. Da biste to uradili, otvorite uređivač Visual Basic pomoću prečice na tastaturi alt+F11 ili dugmetom Visual Basic tabulator Developer (programer). Ako tabs Developer nije vidljiv, može se prikazati kroz Datoteka – Opcije – Podešavanje trake (Datoteka — Opcije — Prilagodi traku).
U gornjem lijevom uglu uređivača trebao bi biti prozor Projekat (ako se ne vidi, uključite ga kroz meni Pogled — Project Explorer):
Ovaj prozor prikazuje sve otvorene radne knjige i pokrenute Microsoft Excel dodatke, uključujući i naše. VBAProject (MyExcelAddin.xlam) Odaberite ga mišem i dodajte mu novi modul preko izbornika Umetak – Modul. U ovom modulu ćemo pohraniti VBA kod naših dodataka makroa.
Možete ili otkucati kod od nule (ako znate programirati), ili ga kopirati odnekud gotov (što je mnogo lakše). Hajde da, za testiranje, unesemo kod jednostavnog, ali korisnog makroa u dodani prazan modul:
Nakon unosa koda, ne zaboravite kliknuti na dugme za spremanje (disketu) u gornjem lijevom kutu.
Naš makro FormulasToValues, kao što možete lako zamisliti, pretvara formule u vrijednosti u unaprijed odabranom rasponu. Ponekad se ovi makroi takođe nazivaju Procedure. Da biste ga pokrenuli, trebate odabrati ćelije s formulama i otvoriti poseban dijaloški okvir Macros sa kartice Developer (Programer — Makroi) ili prečica na tastaturi alt+F8. Obično ovaj prozor prikazuje dostupne makroe iz svih otvorenih radnih knjiga, ali makroi dodataka ovdje nisu vidljivi. Unatoč tome, u polje možemo unijeti naziv naše procedure ime makroa (ime makroa)a zatim kliknite na dugme trčanje (trčati) – i naš makro će raditi:
| |
Ovdje također možete dodijeliti prečicu na tastaturi za brzo pokretanje makroa – za to je odgovorno dugme parametri (Opcije) u prethodnom prozoru makro:
Kada dodjeljujete tipke, imajte na umu da su osjetljivi na velika i mala slova i raspored tipkovnice. Dakle, ako dodijelite kombinaciju like Ctrl+Й, tada ćete, zapravo, u budućnosti morati da se uverite da imate uključen izgled i pritisnete dodatno smjenada dobijem veliko slovo.
Radi praktičnosti, takođe možemo dodati dugme za naš makro na traku sa alatkama za brzi pristup u gornjem levom uglu prozora. Da biste to učinili, odaberite Datoteka – Opcije – Traka sa alatkama za brzi pristup (Datoteka — Opcije — Prilagodite alatnu traku za brzi pristup), a zatim na padajućoj listi na vrhu prozora opciju Macros. Nakon toga naš makro FormulasToValues može se postaviti na ploču pomoću dugmeta dodati (Dodaj) i odaberite ikonu za to pomoću dugmeta promjena (Edit):
Korak 4. Dodajte funkcije dodatku
ali makroprocedure, postoje i makronaredbe funkcija ili kako ih zovu UDF (Korisnički definirana funkcija = korisnički definirana funkcija). Kreirajmo poseban modul u našem dodatku (naredba menija 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 će nam poslužiti kao primjer da pokažemo osnovne principe.
Imajte na umu da se sintaksa funkcije razlikuje od procedure:
- koristi se konstrukcija Funkcija…. End Funkcija umjesto toga Sub … Kraj Sub
- nakon naziva funkcije, njeni argumenti su naznačeni u zagradama
- u tijelu funkcije izvode se potrebni prorač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 kao prethodnu makro proceduru kroz dijaloški okvir Macros i dugme trčanje. Ovakvu makro funkciju treba koristiti kao standardnu funkciju radnog lista (SUM, IF, VLOOKUP...), tj. samo unesite u bilo koju ćeliju, navodeći vrijednost iznosa sa PDV-om kao argument:
… ili uđite kroz standardni dijaloški okvir za umetanje funkcije (dugme fx u traci formule), odabirom kategorije Definisano od strane korisnika (Definisano od strane korisnika):
Jedini neugodan trenutak ovdje je odsustvo uobičajenog opisa funkcije na dnu prozora. Da biste ga dodali, moraćete da uradite sledeće:
- Otvorite Visual Basic Editor pomoću prečice na tastaturi alt+F11
- Odaberite dodatak u Project panelu i pritisnite tipku F2da otvorite prozor Object Browser
- Izaberite svoj projekat dodatka sa padajuće liste na vrhu prozora
- Desnom tipkom miša kliknite na funkciju koja se pojavi i odaberite naredbu svojstva.
- Unesite opis funkcije u prozor Opis
- Sačuvajte datoteku dodatka i ponovo pokrenite excel.
Nakon ponovnog pokretanja, funkcija bi trebala prikazati opis koji smo unijeli:
Korak 5. Kreirajte karticu dodataka u interfejsu
Posljednji, iako ne obavezan, ali prijatan dodir bit će kreiranje zasebne kartice s gumbom za pokretanje našeg makroa, koji će se pojaviti u Excel sučelju nakon povezivanja našeg dodatka.
Informacije o karticama koje su podrazumevano prikazane sadržane su u knjizi 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 najpovoljnijih (i besplatnih) je program Maksima Novikova Ribbon XML Editor.
Algoritam za rad s njim je sljedeći:
- Zatvorite sve Excel prozore kako ne bi došlo do sukoba datoteka kada uređujemo XML kod dodatka.
- Pokrenite program Ribbon XML Editor i otvorite našu MyExcelAddin.xlam datoteku u njemu
- Sa dugmetom karticama u gornjem lijevom uglu dodajte isječak koda za novu karticu:
- Morate staviti prazne navodnike id našu karticu i grupu (bilo koji jedinstveni identifikator) i u etiketa – nazive naše kartice i grupe dugmadi na njoj:
- Sa dugmetom dugme na lijevoj ploči dodajte prazan kod za dugme i dodajte mu oznake:
— etiketa je tekst na dugmetu
— imageMso — ovo je uslovni naziv slike na dugmetu. Koristio sam ikonu crvenog dugmeta pod nazivom AnimationCustomAddExitDialog. Nazivi svih dostupnih dugmadi (a ima ih nekoliko stotina!) mogu se pronaći na velikom broju stranica na Internetu ako tražite ključne riječi “imageMso”. Za početak, možete otići ovdje.
- onAction – ovo je naziv procedure povratnog poziva – specijalni kratki makro koji će pokrenuti naš glavni makro FormulasToValues. Ovu proceduru možete nazvati kako god želite. Dodaćemo je malo kasnije.
- Ispravnost svega urađenog možete provjeriti pomoću dugmeta sa zelenom kvačicom na vrhu alatne trake. Na istom mjestu kliknite na dugme sa disketom da sačuvate sve promene.
- Zatvorite Ribbon XML Editor
- Otvorite Excel, idite na Visual Basic editor i dodajte proceduru povratnog poziva našem makrou KillFormulastako da pokreće naš glavni makro za zamjenu formula vrijednostima.
- Spremamo promjene i, vraćajući se u Excel, provjeravamo rezultat:
To je sve – dodatak je spreman za upotrebu. Ispunite ga svojim vlastitim procedurama i funkcijama, dodajte lijepe gumbe – i bit će vam mnogo lakše koristiti makroe u svom radu.
- Šta su makroi, kako ih koristiti u svom radu, gdje dobiti makro kod u Visual Basicu.
- Kako napraviti početni ekran prilikom otvaranja radne knjige u Excelu
- Šta je Personal Macro Book i kako je koristiti