„Excel“ makro įrašymo įrenginys turi daug galios, tačiau turi savo apribojimų. Kaip aprašyta kitame straipsnyje, makro įrašymo įrenginys dažnai įrašo nereikalingą kodą ir negali įrašyti tokių dalykų kaip logika ar sąveika su kitomis programomis. Tai taip pat gali būti sunku naudoti ilgesnėms makrokomandoms - gali tekti iš anksto sukurti savo veiksmų scenarijų, kad išvengtumėte brangiai kainuojančių klaidų.
Šio straipsnio tikslas - padėti jums pradėti koduoti makrokomandas nuo nulio VBA. Sužinosite, kur saugomos makrokomandos, parašysite pagrindinę makrokomandą ir sužinosite programavimo pagrindus VBA, naudodami kintamuosius, logiką ir kilpas.
Darbo pradžia
VBA ir „Visual Basic“ redaktorius
VBA arba „Visual Basic for Applications“ yra ta kalba, kuria rašomos makrokomandos. Visos makrokomandos saugomos kaip VBA kodas, nesvarbu, ar jos yra ranka koduotos, ar sukurtos naudojant makro įrašymo įrenginį.
Visą VBA kodą galite pasiekti darbaknygėje naudodami „Visual Basic“ redaktorių. Tai specialus teksto redaktorius ir derintuvas, įmontuotas visose biuro programose, įskaitant „Excel“. Paprastai šį redaktorių atidarysite naudodami ALT+F11 spartusis klavišas „Excel“, bet taip pat galite jį pasiekti iš „Excel“ Programuotojas skirtuką, jei jį įjungėte.
Projektų tyrinėtojas
The Projektų tyrinėtojas yra langas VB redaktoriuje, kuriame rodomi visi elementai, kuriuose gali būti VBA kodas. Jei nematote šio lango, paspauskite F5 kad jis būtų rodomas arba pasirinktas Projektų tyrinėtojas nuo Peržiūrėti Meniu.
Dukart spustelėjus elementą „Project Explorer“, bus parodytas to elemento kodas. „Project Explorer“ gali būti rodomi kelių tipų elementai:
- Darbo knygos
- Darbo lapai
- Vartotojo formos
- Klasės moduliai
- Moduliai (šiuose elementuose saugomos makrokomandos)
Nors visi šie elementų tipai gali apimti VBA kodą, geriausia praktika yra koduoti makrokomandas moduliuose.
Pirmosios makrokomandos kūrimas
Makrokomandų sąrašo naudojimas
Makrokomandų sąraše rodomos visos jūsų darbaknygės makrokomandos. Šiame sąraše galite redaguoti esamą makrokomandą arba sukurti naują.
Norėdami sukurti naują makrokomandą naudodami makrokomandų sąrašą:
- Pasirinkite skirtuką Kūrėjas ir spustelėkite Makrokomandos (arba paspauskite ALT+F8)
- Įveskite naują makrokomandos pavadinimą, tada spustelėkite „Sukurti“
Spustelėjus „Sukurti“, pasirodys VB redaktorius, rodantis naujai sukurtą makrokomandą. Jei reikia, „Excel“ sukurs naują makrokomandos modulį.
Rankiniu būdu VB redaktoriuje
Galite pridėti naują makrokomandą rankiniu būdu be makrokomandų sąrašo. Tai yra geresnis pasirinkimas, jei norite nurodyti modulį, kuriame išsaugota makrokomanda.
Norėdami pridėti makrokomandą rankiniu būdu:
- Atidarykite VB redaktorių (ALT+F11)
- Arba:
- Pridėkite naują modulį spustelėdami Įterpti> Modulis meniu (modulis bus automatiškai atidarytas)
-
- ARBA dukart spustelėkite esamą „Project Explorer“ modulį, kad jį atidarytumėte
- Į modulį įveskite naujos makrokomandos kodą
Sub MyMacro () Pabaigos poskyris
Šios dvi eilutės nurodo makrokomandos, pavadintos „MyMacro“, pradžią ir pabaigą (atkreipkite dėmesį į skliaustelius, kurie yra būtini). Tai bus rodoma „Excel“ dialogo lange „Peržiūrėti makrokomandas“ ir gali būti priskirtas mygtukui (nors jis dar nieko nedaro).
Pridėkite kodą prie makrokomandos
Dabar pridėkime kodą tarp „Sub“ ir „End Sub“ eilučių, kad ši makrokomanda iš tikrųjų kažką padarytų:
Sub MyMacro () diapazonas („A1“). Vertė = „Labas pasaulis!“ Pabaiga Sub
Pagrindinės kodo struktūros
Diapazono objektas
„Excel VBA“ naudoja diapazono objektą, kad atvaizduotų darbalapio ląsteles. Anksčiau pateiktame pavyzdyje su kodu sukuriamas diapazono objektas Diapazonas („A1“) kad galėtumėte pasiekti langelio A1 reikšmę.
Diapazono objektai pirmiausia naudojami ląstelių reikšmėms nustatyti:
Diapazonas („A1“). Vertė = 1
Diapazonas („A1“). Vertė = „Pirmasis langelis“
Atkreipkite dėmesį, kad apibrėžiant langelių reikšmes kaip skaičius, jūs tiesiog įvedate skaičių, bet įvesdami tekstą turite jį supa kabutėmis.
Diapazonus taip pat galima naudoti norint pasiekti daugelį langelių savybių, tokių kaip jų šriftas, kraštinės, formulės ir kt.
Pvz., Galite nustatyti langelio šriftą kaip paryškintą taip:
Diapazonas („A1“). Šriftas. Paryškintas = tiesa
Taip pat galite nustatyti langelio formulę:
Diapazonas („A1“). Formulė = „= suma (A2: A10)“
„Excel“ galite pasirinkti langelių bloką su žymekliu (tarkime, nuo A1 iki D10) ir nustatyti juos visus paryškintus. Diapazono objektai gali pasiekti tokius ląstelių blokus:
Diapazonas („A1: D10“). Šriftas. Paryškintas = tiesa
Taip pat galite nurodyti kelias langelius/blokus vienu metu:
Diapazonas („A1: D10, A12: D12, G1“). Font.Bold = tiesa
Formatas yra toks pat, koks būtų naudojamas pasirinkus langelius SUM () formulei „Excel“. Kiekvienas blokas yra atskirtas kableliu, o blokai žymimi viršutine ir apatine dešine ląstelėmis, atskirtomis dvitaškiu.
Galiausiai „Range“ objektai turi integruotus metodus, kaip atlikti įprastas darbalapio operacijas. Pavyzdžiui, galbūt norėsite nukopijuoti kai kuriuos duomenis iš vienos vietos į kitą. Štai pavyzdys:
Diapazonas („A1: D10“). Kopijuoti diapazoną („F1“). PasteSpecial xlPasteValues Range („F1“). PasteSpecial xlPasteFormats
Tai nukopijuoja langelius A1: D10 į mainų sritį, o tada atlieka PasteSpecial (), prasidedantį C1 langelyje - kaip ir rankiniu būdu „Excel“. Atkreipkite dėmesį, kad šiame pavyzdyje parodyta, kaip naudojant „PasteSpecial“ () įklijuoti tik vertes ir formatus - yra visų parinkčių, kurias matysite dialogo lange „Įklijuoti“, parametrai.
Štai pavyzdys, kaip įklijuoti „Visi“ į kitą darbalapį:
Diapazonas („A1: D10“). Kopijuoti lapus („Sheet2“). Diapazonas („A1“). PasteSpecial xlPasteAll
Jei teiginiai
Su an Jei pareiškimas, galite priversti kodo skiltį vykdyti tik „jei“ tam tikras teiginys yra teisingas.
Pvz., Galbūt norėsite langelį paryškinti ir nuspalvinti raudonai, bet tik „jei“ langelio reikšmė yra mažesnė nei 100.
Jei diapazonas („A4“). Reikšmė <100 Tada diapazonas („A4“). Šriftas. Boldas = tikras diapazonas („A4“). Interjeras. Spalva = vb
Tinkama „If“ teiginio struktūra yra tokia (skliausteliuose nurodomi pasirenkami komponentai):
Jei tada
[Priešingu atveju]
[Kitas]
Pabaiga Jei
Galite įtraukti tiek Kitu atveju blokus, kaip jums patinka išbandyti kelias sąlygas. Taip pat galite pridėti Kitas blokas, kuris veikia tik tuo atveju, jei nėra įvykdytos jokios kitos „If“ teiginio sąlygos.
Štai dar vienas pavyzdys, pagrįstas ankstesniu, kai langelis suformatuotas keliais skirtingais būdais, priklausomai nuo vertės:
Jei diapazonas („A4“). Reikšmė <100 Tada diapazonas („A4“). Šriftas. Šviesus = tikras diapazonas („A4“). Interjeras. Spalva = vbRed ElseIf diapazonas („A4“). Reikšmė <200 Tada diapazonas ( "A4"). Šriftas. Šviesus = klaidingas diapazonas ("A4"). Interjeras. Spalva = vb
Anksčiau pateiktame pavyzdyje langelis „ElseIf“ blokuose, kuriuose reikšmė yra mažesnė nei 100, yra paryškintas. Galite lizdas Jei teiginiai padės išvengti kodo dubliavimo, atlikite šiuos veiksmus:
Jei diapazonas ("A4"). Reikšmė <100 Tada diapazonas ("A4"). Šriftas. Šviesus = tikras diapazonas ("A4"). Interjeras. Spalva = vbRed Else Range ("A4"). Font.Bold = False ' atlenkiant šriftą tik vieną kartą If Range ("A4"). Reikšmė <200 Tada Range ("A4"). Interior.Color = vbGeltonos spalvos diapazonas ("A4"). Interior.Color = vbGreen End If End If
Kintamieji
A Kintamasis yra atmintis, naudojama laikinai informacijai saugoti, kol veikia makrokomanda. Jie dažnai naudojami cikluose kaip iteratoriai arba norint išsaugoti operacijos, kurią norite kelis kartus naudoti makrokomandoje, rezultatą.
Štai kintamojo pavyzdys ir kaip jį naudoti:
Sub ExtractSerialNumber () Dim strSerial As String 'tai kintamojo deklaracija' 'As String' reiškia, kad šis kintamasis skirtas laikyti tekstą 'nustatant apsimestinį serijos numerį: Diapazonas ("A4"). Value = "serial# 804567-88 “„ Išanalizuokite serijos numerį iš langelio A4 ir priskirkite jį kintamajam „strSerial = Mid“ (diapazonas („A4“). Vertė, 9) “dabar naudokite kintamąjį du kartus, užuot turėję du kartus išanalizuoti serijos numerį. B4 ”). Vertė = strSerial MsgBox strSerial End Sub
Šiame pagrindiniame pavyzdyje kintamasis „strSerial“ naudojamas norint išgauti serijos numerį iš A4 langelio naudojant funkciją „Mid ()“, o tada naudojamas dar dviejose vietose.
Standartinis būdas skelbti kintamasis yra toks:
Mat bet koks vardas [Kaip tipo]
- bet koks vardas yra vardas, kurį nusprendėte suteikti savo kintamajam
- tipo yra kintamojo duomenų tipas
„[As tipo] “Dalies galima praleisti - jei taip, kintamasis deklaruojamas kaip„ Variant “tipas, kuriame gali būti bet kokios rūšies duomenų. Nors variantai yra visiškai galiojantys, jų reikėtų vengti, nes jei nesate atsargūs, jie gali sukelti netikėtų rezultatų.
Yra taisykles kintamiesiems pavadinimams. Jie turi prasidėti raide arba pabraukimu, negali būti tarpų, taškų, kablelių, kabučių ar simbolių “! @ & $ #“.
Štai keletas kintamų deklaracijų pavyzdžių:
Dim strFilename As String „gero vardo stilius - aprašomasis ir naudojamas priešdėlis Dim i As Long“ blogo vardo stilius - priimtinas tik kai kuriems iteratoriams Dim Sale - ne per daug aprašomas, naudojamas priešdėlis, nėra duomenų tipo
Visuose šiuose pavyzdžiuose naudojamos šiek tiek skirtingos pavadinimo schemos, tačiau visos yra tinkamos. Tai nėra bloga idėja prieš kintamojo pavadinimą pridėti trumpą jo tipo duomenų formą (kaip aprašyta kai kuriuose iš šių pavyzdžių), nes dėl to jūsų kodas iš pirmo žvilgsnio tampa lengviau skaitomas.
VBA apima daug pagrindinių duomenų tipai. Tarp populiariausių yra:
- Styginė (naudojamas teksto duomenims laikyti)
- Ilgas (naudojamas sveikiems skaičiams laikyti, t. y. be kablelių)
- Dvigubas (naudojamas slankiojo kablelio skaičiams laikyti, t. y.
Visą VBA vidinių duomenų tipų sąrašą galite rasti čia: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Diapazono objekto kintamieji
Taip pat galima sukurti kintamuosius, nurodančius diapazono objektus. Tai naudinga, jei norite nurodyti konkretų kodo diapazoną keliose vietose - tokiu būdu, jei reikia keisti diapazoną, jį reikia pakeisti tik vienoje vietoje.
Kai sukuriate diapazono objekto kintamąjį, turite jį „nustatyti“ į diapazono egzempliorių. Pavyzdžiui:
Dim rMyRange as Range Set rMyRange = Diapazonas („A1: A10; D1: J10“)
Palikus teiginį „Nustatyti“ priskiriant diapazono kintamąjį, atsiras klaida.
Kilpos
Kilpos yra blokai, kurie tam tikrą skaičių kartų pakartoja jų viduje esantį kodą. Jie yra naudingi siekiant sumažinti kodo, kurį turite parašyti, skaičių ir leidžia parašyti vieną kodo dalį, kuri atlieka tuos pačius veiksmus su daugeliu skirtingų susijusių elementų.
Už-Kitas
A Už-Kitas blokas yra ciklas, kuris kartojasi tam tikrą skaičių kartų. Jis naudoja kintamąjį kaip iteratorius suskaičiuoti, kiek kartų jis buvo paleistas, ir šį iteratoriaus kintamąjį galima naudoti ciklo viduje. Dėl to „For-Next“ kilpos yra labai naudingos kartojant langelius ar masyvus.
Štai pavyzdys, kuris eina per 1–100 eilučių 1 stulpelio langelius ir nustato jų reikšmes iteratoriaus kintamojo reikšmei:
Dim i kaip ilgai, kai i = nuo 1 iki 100 langelių (i, 1). Vertė = i Kitas
Eilutė „Dėl i = 1 iki 100“ reiškia, kad ciklas prasideda nuo 1 ir baigiasi po 100. Galite nustatyti bet kokius jums patinkančius pradžios ir pabaigos skaičius; šiems skaičiams taip pat galite naudoti kintamuosius.
Pagal numatytuosius nustatymus „For-Next“ kilpos skaičiuojamos iš 1. Jei norite skaičiuoti kitu skaičiumi, ciklą galite parašyti aiškiai Žingsnis išlyga:
Jei i = 5 iki 100 5 žingsnis
Ši kilpa prasidės nuo 5, tada kiekvieną kartą, kai ciklas kartosis, pridėkite 5 prie „i“ (taigi antrą kartojimą „i“ bus 10, trečią - 15 ir pan.).
Naudojant Žingsnis, taip pat galite suskaičiuoti kilpas atgal:
Jei i = 100 iki 1 1 žingsnis
Tu taip pat gali lizdas „For-Next“ kilpos. Kiekvienam blokui reikia skaičiuoti savo kintamąjį, tačiau tuos kintamuosius galite naudoti bet kur, kur norite. Štai pavyzdys, kaip tai naudinga „Excel VBA“:
Dim i Kaip ilgai, j Kaip ilgai, kai i = 1 iki 100, jei j = 1 iki 100 langelių (i, j). Vertė = i * j Kitas j Kitas i
Tai leidžia jums pereiti per eilutes ir stulpelius.
ĮSPĖJIMAS: nors tai leidžiama, NIEKADA nekeiskite iteratoriaus kintamojo „For-Next“ bloke, nes jis naudoja tą iteratorių, kad galėtų sekti kilpą. Pakeitus iteratorių gali kilti begalinė kilpa ir pakabinti makrokomandą. Pavyzdžiui:
Jei i = 1 iki 100 i = 1 Kitas i
Šioje cikle „aš“ niekada nepasieks daugiau nei 2, kol nebus atkurtas 1, ir kilpa kartosis amžinai.
Kiekvienam
Kiekvienam blokai yra labai panašūs į „For-Next“ blokus, išskyrus tai, kad jie nenaudoja skaitiklio norėdami nurodyti, kiek kartų jie kartojasi. Vietoj to blokas „Už kiekvieną“ paima objektų „kolekciją“ (pvz., Ląstelių diapazoną) ir paleidžia tiek kartų, kiek toje kolekcijoje yra objektų.
Štai pavyzdys:
Dim r kaip diapazonas kiekvienam r diapazone ("A15: J54") Jei r.Value> 0 Tada r.Font.Bold = Tikroji pabaiga, jei kita
Atkreipkite dėmesį į „Range“ objekto kintamojo „r“ naudojimą. Tai iteratoriaus kintamasis, naudojamas cikle „For -Every“ - kiekvieną kartą per ciklą „r“ gauna nuorodą į kitą diapazono langelį.
Naudojant „For-Every“ kilpas „Excel VBA“ privalumas yra tas, kad galite perjungti visas diapazono ląsteles be lizdų. Tai gali būti patogu, jei reikia peržvelgti visas sudėtingo diapazono ląsteles, pvz Diapazonas („A1: D12, J13, M1: Y12“).
Vienas „For-Every“ kilpų trūkumas yra tas, kad jūs negalite kontroliuoti ląstelių apdorojimo tvarkos. Nors praktikoje „Excel“ eis per langelius eilės tvarka, teoriškai jis galėtų apdoroti ląsteles visiškai atsitiktine tvarka. Jei reikia apdoroti langelius tam tikra tvarka, turėtumėte naudoti „For-Next“ kilpas.
„Do-Loop“
Nors „For-Next“ blokai naudoja skaitiklius, kad žinotų, kada sustoti, „Do-Loop“ blokai veikia tol, kol įvykdoma sąlyga. Norėdami tai padaryti, naudokite Kol bloko pradžioje arba pabaigoje esanti sąlyga, kuri tikrina būklę ir priverčia ciklą sustoti, kai ši sąlyga yra įvykdyta.
Pavyzdys:
Dim str As String str = "Buffalo" Daryti Iki str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" kilpų diapazonas ("A1"). Reikšmė = str
Šioje kilpoje „Buffalo“ kiekvieną kartą per ciklą sujungiamas į „str“, kol neatitinka tikėtino sakinio. Šiuo atveju bandymas atliekamas ciklo pradžioje - jei „str“ jau buvo numatytas sakinys (o taip nėra todėl, kad ne taip pradėjome, bet jei), ciklas net neveiktų .
Galite padaryti ciklą bent vieną kartą, perkeldami sąlygą iki galo į pabaigą, taip:
Ar str = str & "" & "Buffalo" kilpa kol str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Savo makrokomandoje galite naudoti bet kurią versiją.
ĮSPĖJIMAS: galite sukelti begalinę kilpą su „Do-Loop“ bloku, jei sąlyga „Iki“ niekada nebus įvykdyta. Visada rašykite savo kodą, kad, naudojant šio tipo kilpą, tikrai būtų įvykdyta sąlyga Iki.
Kas toliau?
Kai supratote pagrindus, kodėl gi nepabandžius išmokti pažangesnių metodų? Mūsų pamoka adresu https://easyexcel.net/excel/learn-vba-tutorial/ bus paremta viskuo, ko čia sužinojote, ir išplės savo įgūdžius naudodami renginius, „UserForms“, kodo optimizavimą ir daug daugiau!
