Rad s Excel VBA makronaredbama
Makronaredbe u Excelu pohranjene su kao VBA kôd, a ponekad ćete htjeti izravno urediti ovaj kôd. Ovaj vodič će pokriti kako pregledavati i uređivati makronaredbe, opisati neke tehnike otklanjanja pogrešaka u makroima i dati neke uobičajene primjere uređivanja.
Prikaz makronaredbi
Popis makronaredbi može se prikazati u dijalogu Makroi. Za pregled ovog dijaloga odaberite karticu Developer na vrpci i kliknite gumb Macros.
Ako je otvoreno više radnih knjiga, makronaredbe iz svih radnih knjiga bit će prikazane na popisu. Makronaredbe u aktivnoj radnoj knjizi pojavit će se samo po imenu, dok će makronaredbe u drugim radnim knjigama imati prefiks naziva radne knjige i uskličnika (tj. "Knjiga2! OstaliMakro").
Otvorite makro za uređivanje
Možete upotrijebiti dijalog Makro za otvaranje koda za makronaredbu odabirom naziva makronaredbe i klikom na gumb Uredi. Ovo će otvoriti makro u VB Editoru.
Alternativno, VB Editor možete otvoriti izravno klikom na gumb Visual Basic na kartici Developer ili pritiskom tipke ALT+F11 na tipkovnici.
Pomoću ove metode morat ćete otići do željene makronaredbe (koja se naziva i "procedura"). Preći ćemo na izgled VBA Editor -a:
Pregled VB Editor -a
VB Editor ima nekoliko prozora; u ovom ćemo vodiču pokriti Project Window, prozor Properties i Code Window.
Prozor projekta
Prozor projekta prikazuje svaku Excel datoteku kao vlastiti projekt, sa svim objektima u tom projektu kategoriziranim prema vrsti. Snimljeni makroi pojavit će se u kategoriji "Moduli", obično u objektu “Modul1”. (Ako vaš projekt ima više modula i niste sigurni gdje je pohranjena vaša makronaredba, jednostavno je otvorite iz gore spomenutog dijaloga Makroni.)
Prozor svojstava
Prozor Svojstva prikazuje svojstva i pridružene vrijednosti objekta - na primjer, klikom na objekt radnog lista u prozoru projekta prikazat će se popis svojstava radnog lista. Imena svojstava nalaze se s lijeve strane, a vrijednosti svojstava s desne strane.
Odabirom modula u prozoru Project pokazat će se da ima samo jedno svojstvo, “(Name)”. Možete promijeniti naziv modula dvostrukim klikom na vrijednost svojstva, upisivanjem novog naziva i pritiskom na Enter. Promjenom naziva modula preimenovat ćete ga u Project Window, što je korisno ako imate puno modula.
Kodovi prozora
Prozori kodova posebni su uređivači teksta u kojima možete urediti VBA kôd svoje makronaredbe. Ako želite vidjeti kôd za makronaredbu koja se nalazi u Modulu1, dvaput ćete kliknuti na 'Modul1' u prozoru projekta.
Pokretanje makronaredbi u VB Editoru
Makronaredbe se mogu pokrenuti izravno iz VB uređivača, što je korisno za testiranje i ispravljanje pogrešaka.
Pokretanje makronaredbe
- U prozoru projekta dvaput kliknite na modul koji sadrži makronaredbu koju želite testirati (za otvaranje prozora koda)
- U prozoru Code postavite kursor bilo gdje na kodu makronaredbe između "Sub" i "End Sub"
- Kliknite na Trčanje gumb na alatnoj traci ili pritisnite tipkovni prečac F5
Makronaredba "Korak"
Umjesto pokretanja makronaredbe odjednom, možete pokrenuti makro po jedan redak, koristeći tipkovnički prečac za „prolaz“ kroz kôd. Makro će se pauzirati u svakom retku, omogućujući vam da svaki redak koda učini ono što očekujete u Excelu. Također možete zaustaviti nastavak makronaredbi u bilo kojem trenutku pomoću ove metode.
Da biste "prešli" makronaredbu:
- U prozoru projekta dvaput kliknite modul koji sadrži makronaredbu koju želite testirati (za otvaranje prozora koda)
- U prozoru Code postavite kursor bilo gdje na kodu makronaredbe
- Pritisnite prečicu na tipkovnici F8 da biste započeli postupak „koraka“
- Pritisnite F8 više puta za unaprjeđenje izvršenja koda, što je označeno žutom oznakom u prozoru Code
- Da biste spriječili nastavak makronaredbe, pritisnite Poništi dugme
Zašto uređivati VBA makronaredbe?
Makro snimač - iako učinkovit - također je vrlo ograničen. U nekim slučajevima proizvodi spore makroe, bilježi radnje koje niste namjeravali ponoviti ili bilježi stvari za koje niste mislili da radite. Učenje uređivanja vaših makronaredbi pomoći će im da rade brže, učinkovitije i predvidljivije.
Osim što ćete riješiti te probleme, dobit ćete i veliko povećanje produktivnosti kada iskoristite punu snagu makronaredbi. Makronaredbe ne moraju biti samo snimke zadataka - makronaredbe mogu uključivati logiku pa izvršavaju zadatke samo pod određenim uvjetima. U samo nekoliko minuta možete kodirati petlje koje ponavljaju zadatak stotinama ili tisućama puta u jednom potezu!
U nastavku ćete pronaći neke korisne savjete koji će vam pomoći optimizirati vaš makro kôd, kao i alate kako bi vaše makronaredbe radile teže i pametnije.
Uobičajeni primjeri uređivanja makronaredbi
Ubrzajte makronaredbe
Ako imate makro za čije je pokretanje potrebno duže vrijeme, postoji nekoliko razloga zašto se sporo izvodi.
Kao prvo: kada se pokrene makronaredba, Excel će prikazati sve kako se događa u stvarnom vremenu - dok može izgled brzo vam, zapravopokazujući djelo je značajan hit izvedbe. Jedan od načina da Excel učinite znatno bržim je da mu to kažete prestanite ažurirati zaslon:
'Onemogući ažuriranje zaslona Application.ScreenUpdating = False' Omogući ažuriranje zaslona Application.ScreenUpdating = True
Redak "Application.ScreenUpdating = False" znači da nećete vidjeti da makro radi, ali će se izvoditi mnogo brže. Imajte na umu da biste na kraju makronaredbe uvijek trebali postaviti ScreenUpdating na True, inače se Excel možda neće ponašati kako ste očekivali kasnije!
Još jedan način za ubrzanje makronaredbi:isključite automatsko izračunavanje u makronaredbi. Ako ste radili sa složenim proračunskim tablicama, znat ćete da male promjene mogu pokrenuti tisuće izračuna za koje je potrebno vrijeme da se dovrše, zbog čega mnogi ljudi isključuju automatski izračun u Excelovim opcijama. To možete promijeniti i pomoću VBA koda, tako da će vaša makronaredba i dalje brzo raditi na drugim računalima. To pomaže u slučajevima kada kopirate i lijepite mnogo ćelija formule ili uzrokujete da se pokreće mnogo izračuna dok lijepite podatke u raspon:
"Onemogući aplikaciju automatskog izračuna. Izračun = xlCalculationManual" Omogući aplikaciju automatskog izračuna. Izračun = xlIzračunAutomatski
Dodavanje petlji i logike (ako su izrazi)
Makro snimač sprema sve vaše radnje kao kôd na jeziku koji se zove VBA. VBA nije samo način bilježenja radnji u Excelu - to je programski jezik, što znači da može sadržavati kôd za donošenje odluka o radnjama koje treba izvesti ili ponavljanje radnji dok se ne ispuni uvjet.
Petlja
Recimo da ste htjeli napraviti makronaredbu koja je pripremila izvješće, a kao dio te makronaredbe morali ste dodati devetnaest listova u radnu knjigu, ukupno dvadeset. Mogli biste se snimiti klikom na (+) gumb uvijek iznova ili možete napisati petlju koja za vas ponavlja radnju, ovako:
PodizvješćePrep () Dim i As Long For i = 1 To 19 Sheets.Add Next i End Sub
U ovom primjeru koristimo a Za petlju, što je vrsta petlje koja se ponavlja kroz niz stavki. Ovdje je naš raspon brojevi od 1 do 19, koristeći varijablu pod nazivom 'i' kako bi se petlja mogla pratiti. Unutar naše petlje, samo se jedna radnja ponavlja između za iSljedeći redaka (dodavanje lista), ali možete dodati onoliko koda unutar petlje koliko želite učiniti, primjerice oblikovati list ili kopirati i zalijepiti podatke u svaki list - što god želite ponoviti.
Ako Izjave
An Ako izjava koristi se za odlučivanje hoće li se neki kod pokrenuti ili ne, pomoću logičkog testa za donošenje odluke. Evo jednostavnog primjera:
Sub ClearIfSmall () If Selection.Value <100 Then Selection.Clear End If End Sub
Ovaj jednostavan primjer pokazuje kako iskaz If radi - testirate neki uvjet koji je ili True ili False (je li vrijednost odabrane ćelije manja od 100?), a ako test vrati True, kôd iznutra će se pokrenuti.
Nedostatak ovog koda je što testira samo jednu ćeliju odjednom (i neće uspjeti ako odaberete više ćelija). Ovo bi bilo korisnije ako biste mogli … proći kroz svaku odabranu ćeliju i testirati svaku …
Sub ClearIfSmall () Dim c As Range for each c In Selection.Cells If c.Value <100 then c.Clear End If Next c End Sub
U ovom primjeru postoji nešto drugačija petlja For - ova ne kruži nizom brojeva, već se petlja kroz sve ćelije u odabiru, koristeći varijablu pod nazivom "c" za praćenje. Unutar petlje, vrijednost "c" se koristi za određivanje treba li ćeliju očistiti ili ne.
Petlje i If izrazi mogu se kombinirati na bilo koji način - petlje možete staviti unutar petlji, ili jedan If unutar druge, ili upotrijebiti If da odlučite treba li se petlja uopće pokrenuti.
<<>>
Uklonite efekte pomicanja
Uobičajeni razlog za uređivanje koda makroa je uklanjanje pomicanja zaslona. Prilikom snimanja makronaredbe možda ćete morati doći do drugih područja radnog lista pomicanjem, ali makronaredbe se ne moraju pomicati za pristup podacima.
Pomicanje može zatrpati vaš kôd stotinama ili čak tisućama redaka nepotrebnog koda. Evo primjera koda koji se bilježi klikom i povlačenjem po traci za pomicanje:
Ova vrsta koda potpuno je nepotrebna i može se izbrisati bez utjecaja na bilo koju drugu funkcionalnost. Čak i ako želite zadržati pomicanje, ovaj bi se kôd ipak mogao sažeti u petlju.
Uklonite suvišni kôd
Snimljeni makronaredbi imaju tendenciju dodavati mnogo suvišnog koda koji ne odražava nužno ono što želite da makro radi. Uzmimo za primjer sljedeći snimljeni kôd koji bilježi promjenu naziva fonta u ćeliji:
Iako je promijenjen samo naziv fonta, zabilježeno je jedanaest (11) promjena fonta poput veličine fonta, tekstualnih efekata itd. Ako je namjera makronaredbe bila samo promijeniti naziv fonta (ostavljajući sva ostala svojstva na miru) snimljeni makro ne bi radio!
Ovu makronaredbu moguće je promijeniti tako da mijenja samo naziv fonta:
Ne samo da će ovaj makro sada raditi kako je predviđeno, već ga je i mnogo lakše čitati.
Uklonite pomake kursora
Još jedna stvar koja se bilježi u makronaredbama su odabir radnog lista i ćelija. To je problem jer korisnik može lako izgubiti trag na onome na čemu je upravo radio ako se pokazivač pomakne na drugi položaj nakon pokretanja makronaredbe.
Kao i kod pomicanja, vas možda će trebati pomaknuti pokazivač i odabrati različite ćelije za izvršavanje zadatka, ali makronaredbe ne moraju koristiti pokazivač za pristup podacima. Razmotrite sljedeći kôd koji kopira raspon i zatim ga zalijepi u tri druga lista:
Postoji nekoliko problema s ovim kodom:
- Korisnik će izgubiti svoje prethodno mjesto u radnoj knjizi
- Makronaredba ne navodi koji list kopiramoiz - ovo bi mogao biti problem ako je makronaredba pokrenuta na pogrešnom listu
Osim toga, kôd je teško čitati i rasipan je. Ova se pitanja mogu riješiti dovoljno jednostavno:
U ovom je kodu jasno vidjeti da kopiramo s Sheet1 i da se niti aktivni radni list niti odabrani raspon ne moraju mijenjati da bi se zalijepili podaci. (Jedna značajna promjena je upotreba "PasteSpecial" umjesto "Paste" - Objekti raspona, poput "Raspon (" C4 ″) ", imaju pristup samo naredbi PasteSpecial.)
Kad god kôd postane pun referenci na ".Select" i "Selection", to znači da postoji prostor za optimiziranje tog koda i njegovu učinkovitost.
