Exceli makrosalvestajal on palju võimsust, kuid sellel on oma piirangud. Nagu ühes teises artiklis käsitletud, salvestab makrosalvesti sageli tarbetut koodi ega saa salvestada selliseid asju nagu loogika või suhtlus teiste programmidega. Seda võib olla raske kasutada ka pikemate makrode puhul - võib juhtuda, et leiate oma tegevustest eelnevalt stsenaariumi, et vältida kulukaid vigu.
Selle artikli eesmärk on aidata teil alustada VBA makrode kodeerimist nullist. Saate teada, kuhu makrod on salvestatud, kirjutada põhiline makro ja õppida programmeerimise põhitõdesid VBA -s muutujate, loogika ja silmuste abil.
Alustamine
VBA ja Visual Basic Editor
VBA ehk Visual Basic for Applications on keel, milles makrosid kirjutatakse. Kõik makrod salvestatakse VBA-koodina, olenemata sellest, kas need on käsitsi kodeeritud või loodud makrosalvestiga.
Kõigile töövihiku VBA -koodidele pääsete juurde Visual Basic Editori abil. See on spetsiaalne tekstiredaktor ja silur, mis on sisse ehitatud kõikidesse kontorirakendustesse, sealhulgas Excelisse. Tavaliselt avate selle redaktori nupuga ALT+F11 klaviatuuri otsetee Excelis, kuid sellele pääsete juurde ka Exceli kaudu Arendaja vahekaart, kui see on lubatud.
Projekti uurija
The Projektiuurija on aken VB redaktoris, mis näitab teile kõiki üksusi, millel võib olla VBA -kood. Kui te seda akent ei näe, vajutage F5 selle kuvamiseks või valimiseks Projektiuurija alates Vaade menüü.
Project Exploreris üksusel topeltklõpsates kuvatakse selle üksuse kood. Project Exploreris võidakse kuvada mitut tüüpi üksusi:
- Töövihikud
- Töölehed
- Kasutajavormid
- Klassi moodulid
- Moodulid (makrosid hoitakse nendes üksustes)
Kuigi kõik need üksustüübid võivad sisaldada VBA -koodi, on parim tava makrod moodulites kodeerida.
Esimese makro tegemine
Makroloendi kasutamine
Makrode loend näitab kõiki teie töövihiku makrosid. Sellest loendist saate muuta olemasolevat makro või luua uue.
Makro loendi abil uue makro loomiseks tehke järgmist.
- Valige vahekaart Arendaja ja klõpsake nuppu Makrod (või vajutage ALT+F8)
- Sisestage oma makrole uus nimi ja seejärel klõpsake „Loo“
Pärast nupu „Loo” klõpsamist ilmub VB redaktor, mis näitab äsja loodud makro. Excel loob vajadusel makro jaoks uue mooduli.
Käsitsi VB redaktoris
Saate uue makro lisada käsitsi ilma loendita Makrod. See on parem valik, kui soovite määrata mooduli, millesse makro on salvestatud.
Makro käsitsi lisamiseks tehke järgmist.
- Avage VB redaktor (ALT+F11)
- Kas:
- Lisage uus moodul klõpsates Lisa> Moodul menüüs (moodul avaneb automaatselt)
-
- VÕI topeltklõpsake Project Exploreris olemasolevat moodulit selle avamiseks
- Sisestage moodulis oma uue makro kood
Sub MyMacro () End Sub
Need kaks rida tähistavad makro nimega „MyMacro” (algus ja lõpp) (pange tähele, et sulud on kohustuslikud). See kuvatakse Exceli dialoogis „View Macros” ja selle saab määrata nupule (kuigi see ei tee veel midagi).
Lisage makrole kood
Nüüd lisame ridade „Sub” ja „End Sub” vahele koodi, et see makro tegelikult midagi teeks:
Vahemik MyMacro () vahemik (“A1”). Väärtus = “Tere maailm!” End Sub
Põhilised koodistruktuurid
Vahemiku objekt
Excel VBA kasutab töölehe lahtrite esitamiseks vahemiku objekti. Ülaltoodud näites luuakse koodiga Range objekt Vahemik ("A1") lahtri A1 väärtusele juurdepääsemiseks.
Vahemikuobjekte kasutatakse peamiselt lahtrite väärtuste määramiseks:
Vahemik („A1”). Väärtus = 1
Vahemik ("A1"). Väärtus = "Esimene lahter"
Pange tähele, et lahtriväärtuste numbrina määratlemisel sisestate lihtsalt numbri, kuid teksti sisestamisel peate teksti ümbritsema jutumärkidega.
Vahemikke saab kasutada ka lahtrite paljude omaduste, näiteks nende fondi, ääriste, valemite ja muu juurde pääsemiseks.
Näiteks saate lahtri fondi paksus määrata järgmiselt:
Vahemik (“A1”). Font.Bold = True
Samuti saate määrata lahtri valemi:
Vahemik („A1”). Valem = “= summa (A2: A10)”
Excelis võite valida kursoriga lahtrite ploki (näiteks A1 kuni D10) ja määrata need kõik paksus kirjas. Vahemiku objektid pääsevad juurde sellistele lahtrite plokkidele:
Vahemik (“A1: D10”). Font.Bold = True
Võite viidata ka mitmele lahtrile/plokile korraga:
Vahemik („A1: D10, A12: D12, G1”). Font.Bold = True
Selle vorming on sama, mida kasutate Exceli valemi SUM () lahtrite valimisel. Iga plokk on eraldatud komaga ja plokke tähistavad vasakpoolsed üla- ja parempoolsed lahtrid, mis on eraldatud kooloniga.
Lõpuks on vahemiku objektidel sisseehitatud meetodid töölehel tavaliste toimingute tegemiseks. Näiteks võite kopeerida mõned andmed ühest kohast teise. Siin on näide:
Vahemik (“A1: D10”). Kopeeri vahemik (“F1”). PasteSpecial xlPasteValues Range (“F1”). PasteSpecial xlPasteFormats
See kopeerib lahtrid A1: D10 lõikepuhvrisse ja teeb seejärel lahtrist C1 algava PasteSpecial () - täpselt nagu Excelis käsitsi. Pange tähele, et see näide näitab, kuidas kasutada PasteSpeciali () ainult väärtuste ja vormingute kleepimiseks - kõigi suvandite jaoks on parameetrid, mida näeksite dialoogiboksis Kleebi.
Siin on näide „Kõik” kleepimiseks teisele töölehele:
Vahemik („A1: D10”). Lehtede kopeerimine („Leht2”). Vahemik („A1”). KleebiSpecial xlPasteAll
Kui avaldused
Koos Kui avaldus, saate koodiosa käivitada ainult siis, kui teatud väide on tõene.
Näiteks võite teha lahtri paksuks ja värvida selle punaseks, kuid ainult juhul, kui lahtri väärtus on väiksem kui 100.
Kui vahemik ("A4"). Väärtus <100 Siis vahemik ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed End If
If -lause õige struktuur on järgmine (nurksulgudes on märgitud valikulised komponendid):
Kui siis
[Muidu kui siis]
[Muidu]
Lõpp Kui
Saate lisada nii palju Muidu plokke, kui soovite katsetada mitut tingimust. Võite lisada ka Muidu plokk, mis töötab ainult siis, kui ükski muudest avaldise tingimustest ei ole täidetud.
Siin on veel üks näide, mis põhineb eelmisel, kus lahtrit vormindatakse sõltuvalt väärtusest mitmel erineval viisil:
Kui vahemik ("A4"). Väärtus <100 Siis vahemik ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed ElseIf Range ("A4"). Väärtus <200 Then Range ( "A4"). Font.Bold = vale vahemik ("A4"). Interior.Color = vbKollane muu vahemik ("A4"). Font.Bold = vale vahemik ("A4"). Interior.Color = vbGreen End If
Ülaltoodud näites on lahtri paksus kirjas ElseIf plokkides, kus väärtus ei ole alla 100. Saate pesa Kui avaldused koodi dubleerimise vältimiseks toimige järgmiselt.
Kui vahemik ("A4"). Väärtus <100 Siis vahemik ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed Else Range ("A4"). Font.Bold = False ' fondi lahtivõtmine ainult üks kord If Range ("A4"). Väärtus <200 Then Range ("A4"). Interior.Color = vbKollane muu vahemik ("A4"). Interior.Color = vbGreen End If End If
Muutujad
A Muutuja on mälu, mida kasutatakse ajutise teabe salvestamiseks makro töötamise ajal. Neid kasutatakse sageli tsüklites iteraatoritena või toimingu tulemuse hoidmiseks, mida soovite makro jooksul mitu korda kasutada.
Siin on näide muutujast ja selle kasutamisest:
Sub ExtractSerialNumber () Dim strSerial As String 'see on muutuja deklaratsioon' 'Stringina' tähendab, et see muutuja on mõeldud teksti hoidmiseks 'seades teeseldud seerianumbri: Vahemik ("A4"). Väärtus = "seerianumber 804567-88 '' Parsige seerianumber lahtrist A4 ja määrake see muutujale strSerial = Mid (vahemik ("A4"). Väärtus 9) 'kasutage nüüd muutujat kaks korda, selle asemel, et seerianumbrit kaks korda sõeluda. B4 ”). Väärtus = strSerial MsgBox strSerial End Sub
Selles põhinäites kasutatakse muutujat „strSerial”, et eraldada lahtrist A4 seerianumber, kasutades funktsiooni Mid (), ja seejärel kasutatakse seda veel kahes kohas.
Standardne viis kuulutama muutuja on järgmine:
Dim ükskõik mis nimega [Nagu tüüpi]
- ükskõik mis nimega on nimi, mille otsustate oma muutujale anda
- tüüpi on muutuja andmetüüp
„[As tüüpi] ”Osa võib välja jätta - kui jah, siis deklareeritakse muutuja varianditüübiks, mis võib sisaldada mis tahes andmeid. Kuigi varianditüübid on täiesti kehtivad, tuleks neid vältida, sest kui te pole ettevaatlik, võivad need põhjustada ootamatuid tulemusi.
Seal on reeglid muutujate nimede jaoks. Need peavad algama kas tähe või alajoonega, neil ei tohi olla tühikuid, punkte, komasid, jutumärke ega märke “! @ & $ #”.
Siin on mõned näited muutuvatest deklaratsioonidest:
Dim strFilename As String "hea nime stiil - kirjeldav ja kasutab eesliidet Dim i As Long" halb nime stiil - vastuvõetav ainult mõne iteraatori jaoks Dim Sale - mitte liiga kirjeldav, kasutab eesliidet, andmetüüpi pole
Kõik need näited kasutavad veidi erinevaid nimetamisskeeme, kuid kõik kehtivad. Pole paha mõte lisada muutuja nimele selle andmetüübi lühike vorm (nagu mõned neist näidetest), kuna see muudab teie koodi lühidalt loetavamaks.
VBA sisaldab palju põhitõdesid andmetüübid. Kõige populaarsemate hulka kuuluvad:
- String (kasutatakse tekstiandmete hoidmiseks)
- Pikk (kasutatakse täisarvude hoidmiseks, st ilma kümnendkohata)
- Kahekordne (kasutatakse ujukomaarvude, st kümnendkohtade hoidmiseks)
VBA olemuslike andmetüüpide täieliku loendi leiate siit: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Vahemiku objektide muutujad
Võimalik on luua ka muutujaid, mis viitavad vahemiku objektidele. See on kasulik, kui soovite oma koodis teatud vahemikule viidata mitmes kohas - nii kui teil on vaja vahemikku muuta, peate seda muutma ainult ühes kohas.
Objekti muutuja Range loomisel peate selle seadistama vahemiku eksemplarile. Näiteks:
Dim rMyRange kui vahemik Määra rMyRange = Vahemik (“A1: A10; D1: J10”)
Kui jätta vahemiku muutuja määramisel avaldus „Set” välja, tekib viga.
Silmused
Silmused on plokid, mis kordavad nende sees olevat koodi teatud arvu kordi. Need on kasulikud, et vähendada kirjutatava koodi hulka ja võimaldavad teil kirjutada ühe koodiosa, mis sooritab samu toiminguid paljude erinevate seotud üksuste jaoks.
Edasi
A Edasi plokk on tsükkel, mis kordub teatud arv kordi. See kasutab muutujat kui iteraator et lugeda, mitu korda see on jooksnud ja seda iteraatori muutujat saab kasutada tsükli sees. See muudab For-Next silmused lahtrite või massiivide kaudu itereerimiseks väga kasulikuks.
Siin on näide, mis rullib 1 kuni 100 veeru 1 lahtreid ja seab nende väärtused iteraatori muutuja väärtusele:
Dim i nii kaua, kui i = 1 kuni 100 lahtrit (i, 1). Väärtus = i Järgmine i
Rida “i = 1 kuni 100” tähendab, et tsükkel algab 1 -st ja lõpeb pärast 100. Saate määrata mis tahes algus- ja lõpparvu, mis teile meeldivad; nende numbrite jaoks saate kasutada ka muutujaid.
Vaikimisi loeb For-Next silmuseid 1 võrra. Kui soovite loendada mõne muu numbri järgi, võite kirjutada tsükli selgesõnalise Samm klausel:
I = 5 kuni 100 5. samm
See tsükkel algab kell 5, seejärel lisatakse i -le iga kord, kui tsükkel kordub (seega on „i” teisel kordusel 10, kolmandal 15 ja nii edasi).
Kasutades Samm, saate ka tsüklit tagurpidi lugeda:
I = 100 kuni 1 samm -1
Sa saad ka pesa Järgmiste silmuste jaoks. Iga ploki jaoks tuleb arvestada oma muutujaga, kuid neid muutujaid saate kasutada kõikjal, kus soovite. Siin on näide sellest, kuidas see Excel VBA -s kasulik on:
Dim i nii pikk, j kui pikk i = 1 kuni 100 j = 1 kuni 100 lahtrit (i, j). Väärtus = i * j Järgmine j Järgmine i
See võimaldab sulgeda nii ridade kui ka veergude kaudu.
HOIATUS: kuigi see on lubatud, ei tohiks KUNAGI MUUDA iteraatori muutujat järgmise ploki sees, kuna see kasutab seda iteraatorit silmuse jälgimiseks. Iteraatori muutmine võib põhjustada lõpmatu ahela ja teie makro riputada. Näiteks:
I = 1 kuni 100 i = 1 Järgmine i
Selles silmus ei jõua „mina” kunagi enne 2 -st kaugemale, enne kui see taastatakse väärtusele 1, ja tsükkel kordub igavesti.
Igaühele
Igaühele plokid on väga sarnased For-Next plokkidega, välja arvatud juhul, kui nad ei kasuta loendurit, et määrata, mitu korda nad korduvad. Selle asemel võtab iga plokk objektide kogumi (nagu lahtrite vahemik) ja käivitatakse nii palju kordi, kui selles kollektsioonis on objekte.
Siin on näide:
Dim r kui vahemik iga r vahemikus ("A15: J54") Kui r.Value> 0 Siis r.Font.Bold = True End If Next r
Pange tähele objekti muutuja Range kasutamist r. See on iteraatori muutuja, mida kasutatakse iga silmuse jaoks - iga kord läbi tsükli saab „r” viite vahemiku järgmisele lahtrile.
Exceli VBA silmuste kasutamise eelis Excel VBA-s on see, et saate loopida kõiki vahemiku lahtreid ilma pesasilmuseid. See võib olla mugav, kui peate läbi vaatama kõik lahtrid sellises keerulises vahemikus nagu Vahemik („A1: D12, J13, M1: Y12”).
For-Every silmuste üks puudus on see, et teil pole kontrolli lahtrite töötlemise järjekorra üle. Kuigi praktikas Excel liigub lahtritesse järjekorras, teoorias see võiks rakke töödelda täiesti juhuslikus järjekorras. Kui teil on vaja lahtreid teatud järjekorras töödelda, peaksite selle asemel kasutama For-Next ahelaid.
Tee silmust
Kui For-Next plokid kasutavad loendureid, et teada saada, millal peatuda, Tee silmust plokid töötavad seni, kuni mõni tingimus on täidetud. Selleks kasutate Kuni klausel ploki alguses või lõpus, mis testib tingimust ja põhjustab tsükli peatumise, kui see tingimus on täidetud.
Näide:
Dim str As String str = "Buffalo" Tehke kuni str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" silmuste vahemik ("A1"). Väärtus = str
Selles silmus ühendatakse “Buffalo” iga kord silmuse kaudu ‘str’, kuni see vastab oodatud lausele. Sel juhul tehakse test tsükli alguses - kui 'str' oli juba oodatud lause (mida see pole, sest me pole seda nii alustanud, aga kui), siis tsükkel isegi ei käivitu .
Tsükli saate vähemalt korra käivitada, liigutades klausli Kuni lõppu järgmiselt.
Kas str = str & "" & "Buffalo" Loop kuni str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Makros saate kasutada ükskõik millist versiooni.
HOIATUS: saate Do-Loop plokiga põhjustada lõpmatu tsükli, kui tingimus Kuni pole kunagi täidetud. Kirjutage oma kood alati nii, et seda tüüpi tsüklit kasutades on tingimuseks Kuni kindlasti täidetud.
Mis järgmiseks?
Kui olete põhitõedest aru saanud, siis miks mitte proovida õppida mõnda arenenumat tehnikat? Meie õpetus aadressil https://easyexcel.net/excel/learn-vba-tutorial/ põhineb sellel, mida olete siin õppinud, ja laiendab oma oskusi sündmuste, kasutajavormide, koodi optimeerimise ja palju muu abil!
