Excelov makro snemalnik ima veliko moči, vendar ima svoje omejitve. Kot je opisano v drugem članku, makro snemalnik pogosto beleži nepotrebno kodo in ne more snemati stvari, kot so logika ali interakcije z drugimi programi. Prav tako je lahko težko uporabiti za daljše makre - morda se boste predhodno pogovarjali o svojih dejanjih, samo da se izognete dragim napakam.
Ta članek vam bo pomagal začeti kodiranje makrov iz nič v VBA. Naučili se boste, kje so shranjeni makri, napisali osnovni makro in se naučili osnov programiranja v VBA z uporabo spremenljivk, logike in zank.
Začetek
VBA in urejevalnik Visual Basic
VBA ali Visual Basic for Applications je jezik, v katerem so zapisani makri. Vsi makri so shranjeni kot koda VBA, ne glede na to, ali so ročno kodirani ali ustvarjeni z snemalnikom makrov.
Do vseh kod VBA v delovnem zvezku lahko dostopate z urejevalnikom Visual Basic. To je poseben urejevalnik besedil in razhroščevalnik, ki je vgrajen v vse pisarniške aplikacije, vključno z Excelom. Običajno odprete ta urejevalnik z ALT+F11 bližnjico na tipkovnici v Excelu, do nje pa lahko dostopate tudi iz Excelove Razvijalec zavihek, če je omogočen.
Raziskovalec projektov
The Raziskovalec projektov je okno v urejevalniku VB, ki prikazuje vse elemente, ki imajo lahko kodo VBA. Če tega okna ne vidite, pritisnite F5 da se prikaže ali izbere Raziskovalec projektov Iz Pogled meni.
Dvoklik na element v Raziskovalcu projekta prikaže kodo za ta element. V Raziskovalcu projektov se lahko prikaže več vrst elementov:
- Delovni zvezki
- Delovni listi
- Uporabniški obrazci
- Moduli razreda
- Moduli (makri so shranjeni v teh elementih)
Čeprav lahko vse te vrste elementov vključujejo kodo VBA, je najboljša praksa kodiranje makrov v modulih.
Izdelava prvega makra
Uporaba seznama makrov
Seznam makrov prikazuje vse makre v vašem delovnem zvezku. Na tem seznamu lahko uredite obstoječi makro ali ustvarite novega.
Če želite ustvariti nov makro s seznamom makrov:
- Izberite zavihek Razvijalec in kliknite Makri (ali pritisnite ALT+F8)
- Vnesite novo ime za svoj makro in kliknite »Ustvari«
Ko kliknete »Ustvari«, se prikaže urejevalnik VB, ki prikazuje novo ustvarjen makro. Excel bo po potrebi ustvaril nov modul za makro.
Ročno v urejevalniku VB
Nov makro lahko dodate ročno brez seznama makrov. To je najboljša možnost, če želite določiti modul, v katerem je shranjen makro.
Če želite makro dodati ročno:
- Odprite urejevalnik VB (ALT+F11)
- Ali:
- Dodajte nov modul s klikom Vstavi> Modul v meniju (modul se bo samodejno odprl)
-
- ALI dvokliknite obstoječi modul v Raziskovalcu, da ga odprete
- V modul vnesite kodo za nov makro
Sub MyMacro () Konec Sub
Ti dve vrstici označujeta začetek in konec makra z imenom »MyMacro« (upoštevajte obvezne oklepaje). To bo prikazano v pogovornem oknu »Pogled makrov« v Excelu in ga lahko dodelite gumbu (čeprav še nič ne naredi).
Makru dodajte nekaj kode
Zdaj pa dodajmo kodo med vrstici »Sub« in »End Sub«, da bo ta makro dejansko nekaj naredil:
Področje Sub MyMacro () ("A1"). Vrednost = "Pozdravljeni svet!" End Sub
Osnovne strukture kod
Objekt obsega
Excel VBA uporablja objekt Range za predstavitev celic na delovnem listu. V zgornjem primeru je s kodo ustvarjen objekt Range Razpon ("A1") za dostop do vrednosti celice A1.
Objekti obsega se uporabljajo predvsem za nastavitev vrednosti celic:
Razpon (»A1«). Vrednost = 1
Razpon ("A1"). Vrednost = "Prva celica"
Upoštevajte, da pri določanju vrednosti celic kot številk vnesete samo številko, pri vnosu besedila pa morate besedilo obkrožiti z narekovaji.
Obsege lahko uporabite tudi za dostop do številnih lastnosti celic, kot so njihova pisava, obrobe, formule in drugo.
Pisavo celice lahko na primer nastavite na krepko:
Obseg (“A1”). Font.Bold = True
Nastavite lahko tudi formulo celice:
Razpon ("A1"). Formula = "= Vsota (A2: A10)"
V Excelu lahko s kazalcem izberete blok celic (recimo od A1 do D10) in jih nastavite na krepko. Objekti obsega lahko dostopajo do blokov celic, kot je ta:
Obseg (“A1: D10”). Font.Bold = True
Lahko se sklicujete tudi na več celic/blokov hkrati:
Obseg (“A1: D10, A12: D12, G1”). Font.Bold = True
Oblika za to je enaka obliki, ki bi jo uporabili pri izbiri celic za formulo SUM () v Excelu. Vsak blok je ločen z vejico, bloki pa so označeni z zgornjo levo in spodnjo desno celico, ločeni z dvopičjem.
Končno imajo objekti Range vgrajene metode za izvajanje skupnih operacij na delovnem listu. Na primer, morda želite kopirati nekatere podatke z enega mesta na drugega. Tukaj je primer:
Range (“A1: D10”). Copy Range (“F1”). PasteSpecial xlPasteValues Range (“F1”). PasteSpecial xlPasteFormats
To kopira celice A1: D10 v odložišče in nato naredi PasteSpecial (), ki se začne v celici C1 - tako kot bi to naredili ročno v Excelu. Upoštevajte, da ta primer prikazuje, kako s PasteSpecial () prilepite samo vrednosti in oblike - obstajajo parametri za vse možnosti, ki jih vidite v pogovornem oknu Posebno lepljenje.
Tu je primer lepljenja »Vse« na drug delovni list:
Obseg (»A1: D10«). Kopiraj liste (»List2«). Obseg (»A1«). PrilepiSpecial xlPasteAll
Če Izjave
Z Če izjava, lahko odsek kode zaženete le, če je določena trditev resnična.
Celico lahko na primer nastavite krepko in jo obarvate rdeče, vendar le, če je vrednost v celici manjša od 100.
Če obseg ("A4"). Vrednost <100 Potem obseg ("A4"). Pisava.Bold = True Range ("A4"). Interior.Color = vbRed End If
Pravilna struktura stavka If je naslednja (oglati oklepaji označujejo neobvezne komponente):
Če, potem
[Drugače potem]
[Sicer pa]
Konec Če
Vključite jih lahko toliko Drugače blokira, kot želite preizkusiti več pogojev. Dodate lahko tudi Sicer pa blok, ki se izvaja le, če ni izpolnjen noben od drugih pogojev v stavku If.
Tu je še en primer, ki temelji na prejšnjem, kjer je celica oblikovana na več različnih načinov, odvisno od vrednosti:
Če je obseg ("A4"). Vrednost <100 Potem obseg ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed ElseIf Range ("A4"). Vrednost <200 Potem Range ("A4"). "A4"). Font.Bold = False Range ("A4"). Interior.Color = vbJuto drugo območje ("A4"). Font.Bold = False Range ("A4"). Interior.Color = vbGreen End Če
V zgornjem primeru celica ni odebeljena v blokih ElseIf, kjer vrednost ni manjša od 100. Lahko gnezdo Če se stavki izogibajo podvajanju kode, na primer:
Če je obseg ("A4"). Vrednost <100 Nato obseg ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed Else Range ("A4"). Font.Bold = False ' razveljavitev pisave samo enkrat Če obseg ("A4"). Vrednost <200 Nato obseg ("A4"). Notranjost.Barva = vbRumen razpon ("A4"). Notranjost.Barva = vbZelen konec, če je konec Če
Spremenljivke
A Spremenljivka je del pomnilnika, ki se uporablja za shranjevanje začasnih informacij med izvajanjem makra. Pogosto se uporabljajo v zankah kot ponavljalniki ali za shranjevanje rezultata operacije, ki jo želite uporabiti večkrat v makru.
Tu je primer spremenljivke in kako jo lahko uporabite:
Sub ExtractSerialNumber () Dim strSerial As String "to je deklaracija spremenljivke" "As String" pomeni, da je ta spremenljivka namenjena shranjevanju besedila "nastavitev pretvarjalne serijske številke: Obseg (" A4 "). Vrednost =" serial# 804567-88 ”'Razčlenite serijsko številko iz celice A4 in jo dodelite spremenljivki strSerial = Mid (Range (“ A4 ”). Value, 9)“ Zdaj spremenljivko uporabite dvakrat, namesto da bi morali serijsko številko dvakrat razčleniti Range (“ B4 ”). Vrednost = strSerial MsgBox strSerial End Sub
V tem osnovnem primeru se spremenljivka 'strSerial' uporablja za ekstrahiranje serijske številke iz celice A4 s funkcijo Mid (), nato pa se uporabi na dveh drugih mestih.
Standardni način izjaviti spremenljivka je naslednja:
Dim poljubno ime [As tip]
- poljubno ime je ime, za katerega se odločite dati svoji spremenljivki
- tip je podatkovni tip spremenljivke
Sporočilo »[As tip] ”Del lahko izpustite - če je tako, je spremenljivka deklarirana kot vrsta variante, ki lahko vsebuje vse vrste podatkov. Čeprav so popolnoma veljavne, se je treba vrstam variant izogibati, saj lahko privedejo do nepričakovanih rezultatov, če niste previdni.
Obstajajo pravila za imena spremenljivk. Začeti morajo s črko ali podčrtajem, ne smejo imeti presledkov, pik, vej, narekovajev ali znakov »! @ & $ #”.
Tu je nekaj primerov deklaracij spremenljivk:
Dim strFilename As String 'slog dobrega imena - opisno in uporablja predpono Dim i As Long' slog slabega imena - sprejemljiv samo za nekatere iteratorje Dim SalePrice As Double 'v redu slog imena - opisno, vendar ne uporablja predpone Dim iCounter' v redu ime - ni preveč opisno, uporablja predpono, brez podatkovnega tipa
Vsi ti primeri uporabljajo nekoliko drugačne sheme poimenovanja, vendar so vsi veljavni. Ni slaba ideja, da ime spremenljivke prednastavite s kratko obliko podatkovnega tipa (glede na nekatere od teh primerov), saj naredi vašo kodo na prvi pogled bolj berljivo.
VBA vključuje veliko osnovnih podatkovne vrste. Med najbolj priljubljene spadajo:
- Vrvica (uporablja se za shranjevanje besedilnih podatkov)
- dolga (uporablja se za shranjevanje celih števil, torej brez decimalnih mest)
- Dvojno (uporablja se za shranjevanje številk s plavajočo vejico, tj. decimalnih mest)
Celoten seznam notranjih podatkovnih tipov VBA najdete tukaj: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Objektne spremenljivke obsega
Možno je ustvariti spremenljivke, ki se nanašajo tudi na objekte obsega. To je uporabno, če se želite v svoji kodi sklicevati na določeno območje na več mestih - tako, če morate spremeniti obseg, ga morate spremeniti le na enem mestu.
Ko ustvarite spremenljivko predmeta Range, jo morate »nastaviti« na primerek obsega. Na primer:
Zatemni rMyRange kot nastavljeno območje rMyRange = Razpon (“A1: A10; D1: J10”)
Če izpustite stavek »Set« pri dodeljevanju spremenljivke obsega, bo prišlo do napake.
Zanke
Zanke so bloki, ki kodo v sebi ponavljajo določeno število krat. Koristne so za zmanjšanje količine kode, ki jo morate napisati, in vam omogočajo, da napišete en del kode, ki izvaja enaka dejanja pri številnih različnih sorodnih postavkah.
Za-Naprej
A Za-Naprej blok je zanka, ki se ponovi določeno število krat. Spremenljivko uporablja kot iterator za štetje, kolikokrat se je izvajal, in to spremenljivko ponavljanja lahko uporabite znotraj zanke. Zaradi tega so zanke For-Next zelo uporabne za ponavljanje po celicah ali matrikah.
Tu je primer, ki se vrti skozi celice v vrsticah od 1 do 100, stolpec 1 in njihove vrednosti nastavi na vrednost spremenljivke za ponavljanje:
Dim i As Long For i = 1 To 100 Cells (i, 1) .Vrednost = i Next i
Vrstica »Za i = 1 do 100« pomeni, da se zanka začne od 1 in konča po 100. Nastavite lahko katero koli začetno in končno številko; za te številke lahko uporabite tudi spremenljivke.
Privzeto se zanke For-Next štejejo za 1. Če želite šteti za drugo število, lahko zanko napišete z eksplicitnim Korak klavzula:
Za i = 5 do 100 Korak 5
Ta zanka se bo začela pri 5, nato pa vsakič, ko se zanka ponovi, dodajte 5 (tako da bo 'i' pri drugi ponovitvi 10, pri tretji 15 itd.).
Uporaba Korak, lahko naredite štetje zanke tudi nazaj:
Za i = 100 do 1 korak -1
Lahko tudi gnezdo Zanke For-Next. Vsak blok zahteva svojo spremenljivko, s katero lahko računate, vendar jih lahko uporabite kjer koli želite. Tu je primer, kako je to uporabno v Excelu VBA:
Dim i As Long, j As Long Za i = 1 do 100 Za j = 1 do 100 celic (i, j). Vrednost = i * j Naprej j Naprej i
Tako lahko preklapljate tako po vrsticah kot po stolpcih.
OPOZORILO: čeprav je dovoljeno, spremenljivke iteratorja v bloku For-Next NIKOLI NE MENJAJTE, saj ta iterator uporablja za sledenje zanke. Spreminjanje iteratorja lahko povzroči neskončno zanko in obesi vaš makro. Na primer:
Za i = 1 Za 100 i = 1 Naprej i
V tej zanki 'I' nikoli ne bo presegel 2, preden se ponastavi na 1, zanka pa se bo ponavljala večno.
Za vsakogar
Za vsakogar bloki so zelo podobni blokom For-Next, le da ne uporabljajo števca, da bi določili, kolikokrat se ponovijo. Namesto tega blok For-Each vzame "zbirko" predmetov (na primer obseg celic) in se izvede tolikokrat, kolikor je predmetov v tej zbirki.
Tukaj je primer:
Zatemni r kot obseg za vsak r v razponu ("A15: J54") Če je r.Vrednost> 0 Potem je r.Font.Bold = True End Če je naslednji r
Upoštevajte uporabo spremenljivke predmeta r "r". To je spremenljivka ponavljanja, ki se uporablja v zanki For -Each - vsakič, ko skozi zanko, 'r' dobi sklic na naslednjo celico v obsegu.
Prednost uporabe zank For-Each v Excelu VBA je, da lahko krožite po vseh celicah v obsegu brez gnezdenja. To je lahko priročno, če morate prečkati vse celice v kompleksnem obsegu, na primer Razpon ("A1: D12, J13, M1: Y12").
Ena pomanjkljivost zank For-Each je, da nimate nadzora nad zaporedjem obdelave celic. Čeprav v praksi Excel bo po celicah krožil po celicah, v teoriji celice bi lahko obdelal v povsem naključnem vrstnem redu. Če morate celice obdelati v določenem vrstnem redu, namesto tega uporabite zanke For-Next.
Do-Loop
Medtem ko bloki For-Next uporabljajo števce, da vedo, kdaj se ustaviti, Do-Loop bloki delujejo, dokler ni izpolnjen pogoj. Če želite to narediti, uporabite datoteko Do člen na začetku ali koncu bloka, ki preizkusi stanje in povzroči, da se zanka ustavi, ko je ta pogoj izpolnjen.
Primer:
Dim str As String str = "Buffalo" Do Until str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Loop Range ("A1"). Vrednost = str
V tej zanki se "Buffalo" vsakič poveže z "str" skozi zanko, dokler se ne ujema s pričakovanim stavkom. V tem primeru se test izvede na začetku zanke - če je bil 'str' že pričakovani stavek (kar pa ni zato, ker ga nismo tako začeli, če pa) zanka sploh ne bi delovala .
Zanko lahko vsaj enkrat zaženete tako, da člen Until premaknete do konca, na primer:
Do str = str & "" & "Buffalo" zanka do str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Uporabite lahko katero koli različico, ki je smiselna v vašem makru.
OPOZORILO: z blokom Do-Loop lahko povzroči neskončno zanko, če pogoj Until nikoli ni izpolnjen. Vedno zapišite svojo kodo, tako da bo pri uporabi te vrste zanke zagotovo izpolnjen pogoj Do.
Kaj je naslednje?
Ko ste dojeli osnove, zakaj se ne bi poskusili naučiti nekaterih naprednejših tehnik? Naša vadnica na naslovu https://easyexcel.net/excel/learn-vba-tutorial/ bo gradila na vsem, kar ste se tukaj naučili, in razširila svoje sposobnosti z dogodki, uporabniškimi obrazci, optimizacijo kode in še veliko več!
