Excelov makro snimač ima veliku snagu, ali ima ograničenja. Kao što je opisano u drugom članku, makro rekorder često snima nepotreban kôd i ne može snimati stvari poput logike ili interakcije s drugim programima. Također ga može biti teško koristiti za dulje makroe - možda ćete se prethodno zateći u pisanju priča o svojim radnjama samo kako biste izbjegli skupe pogreške.
Ovaj članak ima za cilj pomoći vam da započnete kodiranje makronaredbi od nule u VBA -i. Naučit ćete gdje su pohranjene makronaredbe, napisat ćete osnovnu makronaredbu i naučit ćete osnove programiranja u VBA -i pomoću varijabli, logike i petlji.
Početak rada
VBA i uređivač Visual Basic
VBA ili Visual Basic for Applications jezik je na kojem su napisane makronaredbe. Sve makronaredbe pohranjene su kao VBA kôd, bilo da su kodirane ručno ili stvorene pomoću makro snimača.
Svom VBA kodu u radnoj knjizi možete pristupiti pomoću uređivača Visual Basic. Ovo je poseban uređivač teksta i alat za ispravljanje pogrešaka koji je ugrađen u sve uredske aplikacije, uključujući Excel. Obično ćete otvoriti ovaj uređivač sa ALT+F11 tipkovni prečac u Excelu, ali možete mu pristupiti i iz programa Excel Programer karticu ako je omogućena.
Istraživač projekta
The Istraživač projekta je prozor unutar VB Editor -a koji vam prikazuje sve stavke koje mogu imati VBA kôd u sebi. Ako ne vidite ovaj prozor, pritisnite F5 kako bi se prikazao ili odabrao Istraživač projekta od Pogled Jelovnik.
Dvoklikom na stavku u Project Exploreru prikazat će se kôd za tu stavku. U Project Exploreru se može pojaviti nekoliko vrsta stavki:
- Radne bilježnice
- Radni listovi
- Korisnički obrasci
- Moduli klase
- Moduli (makroi su pohranjeni u ovim stavkama)
Iako sve ove vrste stavki mogu uključivati VBA kôd, najbolja je praksa kodiranje makronaredbi u Modulima.
Izrada vašeg prvog makronaredbe
Korištenje popisa makronaredbi
Popis makronaredbi prikazuje sve makronaredbe u vašoj radnoj knjizi. S ovog popisa možete urediti postojeću makronaredbu ili stvoriti novu.
Da biste stvorili novu makronaredbu pomoću popisa makronaredbi:
- Odaberite karticu Developer i kliknite Makronaredbe (ili pritisnite ALT+F8)
- Upišite novi naziv svoje makronaredbe, a zatim kliknite "Kreiraj"
Nakon što pritisnete “Create”, pojavit će se VB Editor koji prikazuje novonastalu makronaredbu. Excel će po potrebi izraditi novi modul za makronaredbu.
Ručno u VB Editor -u
Novu makronaredbu možete dodati ručno bez popisa makronaredbi. Ovo je bolja opcija ako želite navesti modul u koji je spremljena makronaredba.
Da biste ručno dodali makronaredbu:
- Otvorite VB Editor (ALT+F11)
- Ili:
- Dodajte novi modul klikom na Umetni> Modul na izborniku (modul će se automatski otvoriti)
-
- ILI, dvaput kliknite na postojeći modul u Project Exploreru da biste ga otvorili
- U modul upišite kôd za novu makronaredbu
Sub MyMacro () Kraj Sub
Ova dva retka označavaju početak i kraj makronaredbe pod nazivom „MyMacro“ (obratite pozornost na zagrade koje su potrebne). To će se pojaviti u dijaloškom okviru "Prikaz makronaredbi" u Excelu i može se dodijeliti gumbu (iako još ništa ne radi).
Dodajte neki kôd u makro
Sada, dodajmo neki kod između redaka "Sub" i "End Sub" kako bi ova makro zapravo učinila nešto:
Raspon pod MyMacro () ("A1"). Vrijednost = "Zdravo Svijete!" Kraj podm
Osnovne strukture koda
Objekt raspona
Excel VBA koristi objekt raspona za predstavljanje ćelija na radnom listu. U gornjem primjeru, objekt Range je kreiran s kodom Raspon ("A1") kako bi se pristupilo vrijednosti ćelije A1.
Objekti raspona prvenstveno se koriste za postavljanje vrijednosti ćelija:
Raspon ("A1"). Vrijednost = 1
Raspon ("A1"). Vrijednost = "Prva ćelija"
Uočite da prilikom definiranja vrijednosti ćelije kao brojeva samo unosite broj, ali pri unosu teksta morate okružiti tekst navodnicima.
Rasponi se također mogu koristiti za pristup mnogim svojstvima ćelija poput njihovog fonta, obruba, formula itd.
Na primjer, font ćelije možete postaviti na podebljano ovako:
Raspon ("A1"). Font.Bold = Tačno
Također možete postaviti formulu ćelije:
Raspon ("A1"). Formula = "= Zbroj (A2: A10)"
U Excelu možete označiti blok ćelija kursorom (recimo, od A1 do D10) i postaviti ih sve podebljano. Objekti raspona mogu pristupiti blokovima ćelija ovako:
Raspon (“A1: D10”). Font.Bold = True
Također se možete uputiti na nekoliko ćelija/blokova odjednom:
Raspon ("A1: D10, A12: D12, G1"). Font.Bold = True
Format za ovo isti je kao format koji biste koristili pri odabiru ćelija za formulu SUM () u Excelu. Svaki je blok odvojen zarezom, a blokovi su označeni gornjom lijevom i donjom desnom ćelijom odvojeni dvotočkom.
Konačno, Range objekti imaju ugrađene metode za izvođenje uobičajenih operacija na radnom listu. Na primjer, možda biste htjeli kopirati neke podatke s jednog mjesta na drugo. Evo primjera:
Raspon ("A1: D10"). Kopiraj raspon ("F1"). PasteSpecial xlPasteValues Raspon ("F1"). PasteSpecial xlPasteFormats
Ovo kopira ćelije A1: D10 u međuspremnik, a zatim izvodi PasteSpecial () počevši od ćelije C1 - baš kao što biste ručno učinili u Excelu. Imajte na umu da ovaj primjer pokazuje kako koristiti PasteSpecial () za lijepljenje samo vrijednosti i formata - postoje parametri za sve opcije koje biste vidjeli u dijaloškom okviru Posebno lijepljenje.
Evo primjera lijepljenja "Sve" na drugi radni list:
Raspon ("A1: D10"). Kopiranje listova ("List 2"). Raspon ("A1"). ZalijepiSpecial xlPasteAll
Ako Izjave
S an Ako izjava, možete učiniti da se dio koda izvodi samo "ako" je određena tvrdnja točna.
Na primjer, možda želite ćeliju podebljati i obojiti crvenom bojom, ali samo ako je vrijednost u ćeliji manja od 100.
Ako raspon ("A4"). Vrijednost <100 Zatim raspon ("A4"). Font.Bold = Pravi raspon ("A4"). Interijer.Boja = vbRed Kraj Ako
Pravilna struktura naredbe If je sljedeća (uglate zagrade označavaju izborne komponente):
Ako tada
[Inače Tada]
[Drugo]
Završi ako
Možete uključiti što više Inače blokira kako želite testirati više uvjeta. Možete dodati i Drugo blok koji se izvodi samo ako nije ispunjen nijedan drugi uvjet u naredbi If.
Evo još jednog primjera temeljenog na prethodnom, gdje je ćelija oblikovana na nekoliko različitih načina, ovisno o vrijednosti:
Ako raspon ("A4"). Vrijednost <100 Zatim raspon ("A4"). Font.Bold = Pravi raspon ("A4"). Interijer.Boja = vbRed Ostali raspon ("A4"). Vrijednost <200 Zatim raspon ( "A4"). Font.Bold = Netačan raspon ("A4"). Interijer.Boja = vbŽuti drugi raspon ("A4"). Font.Bold = Lažni raspon ("A4"). Interijer.Boja = vbZeleni kraj Ako
U gornjem primjeru ćelija nije podebljana u blokovima ElseIf gdje vrijednost nije ispod 100. Možete gnijezdo Ako naredbe za izbjegavanje dupliciranja koda, ovako:
Ako raspon ("A4"). Vrijednost <100 Zatim raspon ("A4"). Font.Bold = Istinski raspon ("A4"). Interijer.Boja = vbRednji raspon ("A4"). Font.Bold = False ' rasklapanje fonta samo jednom Ako raspon ("A4"). Vrijednost <200 Zatim raspon ("A4"). Interijer.Boja = vbJugi raspon žute boje ("A4"). Interijer.Boja = vbZeleni kraj ako je kraj Ako
Varijable
A Promjenjivo je dio memorije koji se koristi za pohranu privremenih informacija dok je makro pokrenut. Često se koriste u petljama kao iteratori ili za zadržavanje rezultata operacije koju želite koristiti nekoliko puta u makronaredbi.
Evo primjera varijable i kako je možete koristiti:
Sub ExtractSerialNumber () Dim strSerial As String 'ovo je deklaracija varijable' 'As String' znači da je ova varijabla namijenjena za držanje teksta 'postavljanjem pretvaračkog serijskog broja: Raspon ("A4"). Vrijednost = "serial# 804567-88 ”'Raščlanite serijski broj iz ćelije A4 i dodijelite ga varijabli strSerial = Mid (Raspon (“ A4 ”). Vrijednost, 9)“ sada koristite varijablu dva puta, umjesto da dvaput morate raščlaniti serijski broj Range (“ B4 ”). Vrijednost = strSerial MsgBox strSerial End Sub
U ovom osnovnom primjeru, varijabla 'strSerial' koristi se za izdvajanje serijskog broja iz ćelije A4 pomoću funkcije Mid (), a zatim se koristi na dva druga mjesta.
Standardni način da se proglasiti varijabla je sljedeća:
Dim bilo koje ime [Kao tip]
- bilo koje ime je ime koje ste odlučili dati svojoj varijabli
- tip je tip podataka varijable
Knjiga „[As tip] ”Dio se može izostaviti - ako je tako, varijabla je deklarirana kao tip varijante koja može sadržavati bilo koju vrstu podataka. Iako su potpuno valjane, vrste varijanti treba izbjegavati jer mogu dovesti do neočekivanih rezultata ako niste oprezni.
Tamo su pravila za nazive varijabli. Moraju započeti slovom ili znakom za podcrtavanje, ne mogu imati razmake, točke, zareze, navodnike ili znakove “! @ & $ #”.
Evo nekoliko primjera deklaracija varijabli:
Dim strFilename As String 'stil dobrog imena - opisno i koristi prefiks Dim i As Long' stil lošeg imena - prihvatljivo samo za neke iteratore Dim SalePrice As Double 'u redu stil imena - opisno, ali ne koristi prefiks Dim iCounter' u redu naziv - nije previše opisan, koristi prefiks, nema vrstu podataka
Svi ovi primjeri koriste neznatno različite sheme imenovanja, ali svi su valjani. Nije loša ideja dati naziv varijable kratkom obliku svoje vrste podataka (prema nekim od ovih primjera), jer to čini vaš kôd čitljivijim na prvi pogled.
VBA uključuje mnogo osnovnih vrste podataka. Najpopularniji su:
- Niz (koristi se za čuvanje tekstualnih podataka)
- Dugo (koristi se za držanje cijelih brojeva, tj. bez decimalnih mjesta)
- Dvostruko (koristi se za držanje brojeva s pomičnim zarezom, tj. decimalnih mjesta)
Cjeloviti popis vlastitih vrsta podataka VBA može se pronaći ovdje: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Varijable objekta raspona
Moguće je stvoriti varijable koje upućuju i na objekte raspona. Ovo je korisno ako se želite pozivati na određeni raspon u svom kodu na nekoliko mjesta - na taj način ako trebate promijeniti raspon, morate ga promijeniti samo na jednom mjestu.
Kada kreirate varijablu objekta Range, morate je "postaviti" na instancu raspona. Na primjer:
Dim rMyRange As Range Set rMyRange = Raspon ("A1: A10; D1: J10")
Izostavljanje izraza "Set" pri dodjeljivanju varijable raspona rezultirat će pogreškom.
Petlje
Petlje su blokovi koji ponavljaju kôd unutar njih određeni broj puta. Korisne su za smanjenje količine koda koji morate napisati i omogućuju vam da napišete jedan dio koda koji izvodi iste radnje na mnogim različitim povezanim stavkama.
Za-Dalje
A Za-Dalje blok je petlja koja se ponavlja određeni broj puta. Koristi varijablu kao iterator za brojanje koliko se puta pokrenulo, a ova se iteratorska varijabla može koristiti unutar petlje. To čini petlje For-Next vrlo korisnima za ponavljanje kroz ćelije ili nizove.
Evo primjera koji se provlači kroz ćelije u retcima 1 do 100, stupcu 1 i postavlja njihove vrijednosti na vrijednost varijable iterator:
Dim i As Long For i = 1 To 100 Cells (i, 1) .Vrijednost = i Next i
Redak "Za i = 1 do 100" znači da petlja počinje od 1, a završava nakon 100. Možete postaviti sve početne i završne brojeve koje želite; za ove brojeve možete koristiti i varijable.
Prema zadanim postavkama petlje For-Next broje se za 1. Ako želite računati s drugim brojem, petlju možete napisati s eksplicitnim Korak klauzula:
Za i = 5 do 100 Korak 5
Ova će petlja započeti u 5, zatim dodajte 5 na 'i' svaki put kad se petlja ponovi (tako da će 'i' biti 10 na drugom ponavljanju, 15 na trećem itd.).
Korištenje Korak, možete napraviti i brojanje petlji unatrag:
Za i = 100 do 1 korak -1
Također možete gnijezdo Petlje For-Next. Svaki blok zahtijeva vlastitu varijablu za brojanje, ali te varijable možete koristiti gdje god želite. Evo primjera kako je to korisno u Excelu VBA:
Dim i As Long, j As Long Za i = 1 To 100 For j = 1 To 100 Cells (i, j) .Vrijednost = i * j Next j Next i
To vam omogućuje da se krećete kroz retke i stupce.
UPOZORENJE: iako je dopušteno, NIKADA nemojte mijenjati varijablu iteratora unutar bloka For-Next, jer ona koristi taj iterator za praćenje petlje. Izmjena iteratora može uzrokovati beskonačnu petlju i objesiti vašu makronaredbu. Na primjer:
Za i = 1 do 100 i = 1 Sljedeće i
U ovoj petlji, 'I' nikada neće prijeći 2 prije nego što se vrati na 1, a petlja će se ponavljati zauvijek.
Za svakoga
Za svakoga blokovi su vrlo slični blokovima For-Next, samo što ne koriste brojač za određivanje koliko puta se petljaju. Umjesto toga, blok For-Each uzima ‘zbirku’ objekata (poput raspona ćelija) i izvodi se onoliko puta koliko ima objekata u toj zbirci.
Evo primjera:
Dim r kao raspon za svaki r u rasponu ("A15: J54") Ako je r.Vrijednost> 0 Tada je r.Font.Bold = True End Ako je sljedeće r
Uočite uporabu varijable objekta 'r' Range. Ovo je iteratorska varijabla koja se koristi u petlji For -Each - svaki put kroz petlju, 'r' dobiva referencu na sljedeću ćeliju u rasponu.
Prednost korištenja petlji For-Each u Excelu VBA je ta što možete proći kroz sve ćelije u rasponu bez petlji za ugniježđenje. To može biti zgodno ako trebate proći kroz sve ćelije u složenom rasponu, na primjer Raspon ("A1: D12, J13, M1: Y12").
Jedan nedostatak petlji Za svaki je to što nemate kontrolu nad redoslijedom obrade ćelija. Iako u praksi Excel će se kretati kroz ćelije redom, u teoriji mogao bi stanice obrađivati potpuno nasumičnim redoslijedom. Ako trebate obraditi ćelije određenim redoslijedom, umjesto toga trebate koristiti petlje For-Next.
Do-Loop
Dok blokovi For-Next koriste brojače kako bi znali kada stati, Do-Loop blokovi se izvode dok se ne ispuni uvjet. Da biste to učinili, koristite an Do klauzulu na početku ili na kraju bloka, koja testira stanje i uzrokuje zaustavljanje petlje kada je taj uvjet ispunjen.
Primjer:
Dim str As String str = "Buffalo" Do Do str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Loop Range ("A1"). Value = str
U ovoj petlji, "Buffalo" se svaki put kroz petlju povezuje sa "str" Sve dok ne odgovara očekivanoj rečenici. U ovom slučaju, test se izvodi na početku petlje - ako je 'str' već bila očekivana rečenica (što nije jer nismo tako započeli, ali ako) petlja se ne bi ni pokrenula .
Petlju možete natjerati da se pokrene barem jednom premještanjem klauzule Until do kraja, na ovaj način:
Do str = str & "" & "Buffalo" petlja do str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Možete koristiti bilo koju verziju koja ima smisla u vašoj makronaredbi.
UPOZORENJE: možete uzrokovati beskonačnu petlju s Do-Loop blokom ako uvjet Until nikad nije ispunjen. Uvijek pišite svoj kôd tako da će uvjet Do definitivno biti ispunjen kada koristite ovu vrstu petlje.
Što je sljedeće?
Nakon što ste shvatili osnove, zašto ne biste pokušali naučiti neke naprednije tehnike? Naš vodič na https://easyexcel.net/excel/learn-vba-tutorial/ nadograđivat će sve ono što ste ovdje naučili i proširiti vaše vještine pomoću Događaja, korisničkih obrazaca, optimizacije koda i još mnogo toga!
