Excelin makrotallentimella on paljon virtaa, mutta sillä on rajoituksensa. Kuten toisessa artikkelissa käsitellään, makrotallennin tallentaa usein tarpeetonta koodia eikä voi tallentaa esimerkiksi logiikkaa tai vuorovaikutusta muiden ohjelmien kanssa. Sitä voi myös olla vaikea käyttää pidemmille makroille - saatat joutua kuvailemaan toimintojasi etukäteen vain välttääksesi kalliita virheitä.
Tämän artikkelin tarkoituksena on auttaa sinua aloittamaan makrojen koodaus tyhjästä VBA: ssa. Opit, mihin makrot on tallennettu, kirjoitat perusmakron ja opit VBA: n ohjelmoinnin perusteet muuttujien, logiikan ja silmukoiden avulla.
Päästä alkuun
VBA ja Visual Basic Editor
VBA tai Visual Basic for Applications on kieli, jolla makrot kirjoitetaan. Kaikki makrot tallennetaan VBA-koodina riippumatta siitä, onko ne koodattu käsin tai luotu makrotallentimella.
Voit käyttää kaikkia työkirjan VBA -koodeja Visual Basic Editorin avulla. Tämä on erityinen tekstieditori ja virheenkorjaaja, joka on sisäänrakennettu kaikkiin toimistosovelluksiin, mukaan lukien Excel. Yleensä avaat tämän editorin ALT+F11 pikanäppäintä Excelissä, mutta voit käyttää sitä myös Excelin kautta Kehittäjä välilehti, jos se on käytössä.
Project Explorer
The Project Explorer on ikkuna VB Editorissa, joka näyttää kaikki kohteet, joissa voi olla VBA -koodia. Jos et näe tätä ikkunaa, paina F5 saadaksesi sen näkyviin tai valitsemaan Project Explorer alkaen Näytä valikko.
Kaksoisnapsauttamalla kohdetta Project Explorerissa näet kyseisen kohteen koodin. Project Explorerissa voi näkyä useita kohteita:
- Työkirjat
- Laskentataulukot
- Käyttäjämuodot
- Luokan moduulit
- Moduulit (makroja tallennetaan näihin kohteisiin)
Vaikka kaikki nämä kohteetyypit voivat sisältää VBA -koodin, paras käytäntö on koodata makroja moduuleissa.
Ensimmäisen makron tekeminen
Makroluettelon käyttäminen
Makroluettelo näyttää kaikki työkirjasi makrot. Tästä luettelosta voit muokata olemassa olevaa makroa tai luoda uuden.
Uuden makron luominen Makrot -luettelon avulla:
- Valitse Kehittäjä -välilehti ja napsauta Makrot (tai paina ALT+F8)
- Kirjoita makrollesi uusi nimi ja napsauta "Luo"
Kun olet napsauttanut "Luo", VB -editori tulee näkyviin ja näyttää juuri luodun makron. Excel luo tarvittaessa uuden moduulin makroa varten.
Manuaalisesti VB -editorissa
Voit lisätä uuden makron manuaalisesti ilman makroluetteloa. Tämä on parempi vaihtoehto, jos haluat määrittää moduulin, johon makro on tallennettu.
Makron lisääminen manuaalisesti:
- Avaa VB Editor (ALT+F11)
- Jompikumpi:
- Lisää uusi moduuli napsauttamalla Lisää> Moduuli valikosta (moduuli avautuu automaattisesti)
-
- TAI kaksoisnapsauta olemassa olevaa moduulia Project Explorerissa avataksesi sen
- Kirjoita moduulissa uuden makron koodi
Sub MyMacro () End Sub
Nämä kaksi riviä osoittavat "MyMacro" -nimisen makron alun ja lopun (huomioi pakolliset sulut). Tämä näkyy Excelin "Näytä makrot" -valintaikkunassa, ja se voidaan määrittää painikkeelle (vaikka se ei vielä tee mitään).
Lisää koodi Makroon
Lisätään nyt koodi "Sub" - ja "End Sub" -rivien väliin, jotta tämä makro todella tekee jotain:
Sub MyMacro () -alue (“A1”). Arvo = “Hello World!” End Sub
Peruskoodirakenteet
Alueobjekti
Excel VBA käyttää alueobjektia laskentataulukon solujen esittämiseen. Yllä olevassa esimerkissä Range -objekti luodaan koodilla Alue ("A1") päästäksesi solun A1 arvoon.
Alueobjekteja käytetään ensisijaisesti soluarvojen asettamiseen:
Alue ("A1"). Arvo = 1
Alue ("A1"). Arvo = "Ensimmäinen solu"
Huomaa, että kun määrität soluarvoja numeroiksi, kirjoita vain numero, mutta kun kirjoitat tekstiä, sinun on ympäröitä teksti lainausmerkeillä.
Alueilla voidaan myös käyttää monia solujen ominaisuuksia, kuten niiden fonttia, reunuksia, kaavoja ja paljon muuta.
Voit esimerkiksi asettaa solun fontin lihavoiduksi seuraavasti:
Alue (“A1”) Fontti.Lihavoitu = Tosi
Voit myös asettaa solun kaavan:
Alue ("A1"). Kaava = "= summa (A2: A10)"
Excelissä voit valita solulohkon kohdistimella (esimerkiksi A1 - D10) ja asettaa ne kaikki lihavoituiksi. Alueobjektit voivat käyttää soluryhmiä seuraavasti:
Alue (“A1: D10”). Font.Bold = Tosi
Voit myös viitata useisiin soluihin/lohkoihin kerralla:
Alue ("A1: D10, A12: D12, G1"). Font.Bold = Tosi
Tämän muoto on sama kuin muoto, jota käytät valitessasi soluja SUM () -kaavaan Excelissä. Jokainen lohko on erotettu pilkulla ja lohkot on merkitty vasemman ylä- ja alaoikean soluilla, jotka on erotettu kaksoispisteellä.
Lopuksi Range-objekteissa on sisäänrakennettuja tapoja suorittaa laskentataulukon yleisiä toimintoja. Haluat esimerkiksi kopioida joitain tietoja paikasta toiseen. Tässä on esimerkki:
Alue ("A1: D10"). Kopioi alue ("F1"). PasteSpecial xlPasteValues Range ("F1"). PasteSpecial xlPasteFormats
Tämä kopioi solut A1: D10 leikepöydälle ja tekee sitten PasteSpecial (): n, joka alkaa solusta C1 - aivan kuten teet manuaalisesti Excelissä. Huomaa, että tämä esimerkki näyttää, kuinka PasteSpecial (): n avulla voidaan liittää vain arvot ja muodot - kaikille liitäntäikkunassa näkyville asetuksille on parametrit.
Tässä on esimerkki "Kaikki" liittämisestä toiseen laskentataulukkoon:
Alue ("A1: D10"). Kopioi taulukot ("Sheet2"). Alue ("A1"). LiitäSpecial xlPasteAll
Jos lausunnot
Kanssa Jos lausunto, voit tehdä koodiosan suoritettavaksi vain "jos" tietty lause on totta.
Voit esimerkiksi tehdä solusta lihavoidun ja värittää sen punaiseksi, mutta vain "jos" solun arvo on alle 100.
If Range ("A4"). Arvo <100 then Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbPunainen loppu Jos
If -lauseen oikea rakenne on seuraava (hakasulkeet osoittavat valinnaisia komponentteja):
Jos sitten
[Muuten Jos sitten]
[Muu]
Loppu Jos
Voit sisällyttää yhtä monta Muuten jos lohkoja, kun haluat testata useita ehtoja. Voit myös lisätä Muu lohko, joka suoritetaan vain, jos mikään If -lauseen muista ehdoista ei täyty.
Tässä on toinen esimerkki, joka perustuu edelliseen, jossa solu on muotoiltu usealla eri tavalla arvosta riippuen:
If Range ("A4"). Arvo <100 then Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed ElseIf Range ("A4"). Arvo <200 then Range ( "A4"). Font.Bold = Väärä alue ("A4"). Interior.Color = vbKeltainen muu alue ("A4"). Font.Bold = False Range ("A4"). Interior.Color = vbVihreä loppu Jos
Yllä olevassa esimerkissä solun lihavointi poistetaan ElseIf-lohkoissa, joissa arvo ei ole alle 100. Voit pesä Jos lausuntoja koodin päällekkäisyyden välttämiseksi, toimi seuraavasti:
If Range ("A4"). Arvo <100 then Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed Else Range ("A4"). Font.Bold = False ' kirjasimen purkaminen vain kerran If Range ("A4"). Arvo <200 Then Range ("A4"). Interior.Color = vbKeltainen muu alue ("A4"). Interior.Color = vbVihreä loppu Jos loppu Jos
Muuttujat
A Muuttuva on muistikappale, jota käytetään väliaikaisten tietojen tallentamiseen makron ollessa käynnissä. Niitä käytetään usein silmukoissa iteraattoreina tai niiden toimintojen tulosten säilyttämiseksi, joita haluat käyttää useita kertoja makron aikana.
Tässä on esimerkki muuttujasta ja sen käyttö:
Sub ExtractSerialNumber () Dim strSerial As String 'tämä on muuttujan ilmoitus' 'As String' tarkoittaa, että tämän muuttujan on tarkoitus pitää tekstiä 'asettaa teeskennellyn sarjanumeron: Range ("A4"). Value = “serial# 804567-88 '' Jäsennä sarjanumero solusta A4 ja määritä se muuttujalle strSerial = Mid (alue ("A4"). Arvo, 9) 'käytä nyt muuttujaa kahdesti sen sijaan, että sinun pitäisi jäsentää sarjanumero kahdesti Range (" B4 ”). Arvo = strSerial MsgBox strSerial End Sub
Tässä perusesimerkissä muuttujaa ”strSerial” käytetään sarjanumeron poimimiseen solusta A4 Mid () -funktiolla ja sitä käytetään sitten kahdessa muussa paikassa.
Vakio tapa julistaa muuttuja on seuraava:
Himmeä mikä tahansa nimi [Kuten tyyppi]
- mikä tahansa nimi on nimi, jonka päätät antaa muuttujalle
- tyyppi on muuttujan tietotyyppi
"[Kuten tyyppi] ”Osa voidaan jättää pois - jos on, muuttuja ilmoitetaan Variant -tyypiksi, joka voi sisältää kaikenlaisia tietoja. Vaikka varianttityypit ovat täysin päteviä, niitä tulisi välttää, koska ne voivat johtaa odottamattomiin tuloksiin, jos et ole varovainen.
Siellä on säännöt muuttujien nimille. Niiden on aloitettava joko kirjaimella tai alaviivalla, eikä niissä saa olla välilyöntejä, pisteitä, pilkkuja, lainausmerkkejä tai merkkejä ”! @ & $ #”.
Tässä muutamia esimerkkejä muuttuvista ilmoituksista:
Dim strFilename As String 'hyvän nimen tyyli - kuvaileva ja käyttää etuliitettä Dim i As Long' huono nimen tyyli - hyväksyttävä vain joillekin iteraattoreille Dim Sale - ei liian kuvaava, käyttää etuliitettä, ei tietotyyppiä
Kaikissa näissä esimerkeissä käytetään hieman erilaisia nimitysmenetelmiä, mutta kaikki ovat päteviä. Ei ole huono idea liittää muuttujan nimeen sen tietotyypin lyhyt muoto (joidenkin esimerkkien mukaan), koska se tekee koodistasi luettavamman yhdellä silmäyksellä.
VBA sisältää paljon perusasioita tietotyypit. Suosituimpia ovat:
- Jousisoitin (käytetään tekstitietojen säilyttämiseen)
- Pitkä (käytetään kokonaisten numeroiden säilyttämiseen, eli ei desimaaleja)
- Kaksinkertainen (käytetään liukulukujen, eli desimaalien pitämiseen)
Täydellinen luettelo VBA: n luontaisista tietotyypeistä löytyy täältä: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Alueobjektimuuttujat
On mahdollista luoda myös muuttujia, jotka viittaavat alueobjekteihin. Tästä on hyötyä, jos haluat viitata tiettyyn alueeseen koodissasi useissa paikoissa - tällä tavalla, jos haluat muuttaa aluetta, sinun on muutettava se vain yhdessä paikassa.
Kun luot Range -objektimuuttujan, sinun on "asetettava" se alueen ilmentymäksi. Esimerkiksi:
Dim rMyRange as Range Set rMyRange = Range (“A1: A10; D1: J10”)
Jos jätät "Set" -lausekkeen pois, kun määrität Range -muuttujan, seurauksena on virhe.
Silmukat
Silmukat ovat lohkoja, jotka toistavat sisällä olevan koodin tietyn määrän kertoja. Niistä on hyötyä, kun vähennät kirjoittamasi koodin määrää ja voit kirjoittaa yhden koodin, joka suorittaa samat toiminnot monille eri aiheisiin liittyville kohteille.
Seuraavaksi
A Seuraavaksi lohko on silmukka, joka toistuu tietyn määrän kertoja. Se käyttää muuttujaa iteraattori laskea, kuinka monta kertaa se on suoritettu, ja tätä iteraattomuuttujaa voidaan käyttää silmukan sisällä. Tämä tekee For-Next-silmukoista erittäin hyödyllisiä iteroinnissa solujen tai matriisien läpi.
Tässä on esimerkki, joka kulkee rivien 1-100 sarakkeen 1 solujen läpi ja asettaa niiden arvot iteraattomuuttujan arvoon:
Dim i Niin kauan i = 1 - 100 solua (i, 1). Arvo = i Seuraava i
Rivi "i = 1 - 100" tarkoittaa, että silmukka alkaa 1: stä ja päättyy 100: n jälkeen. Voit asettaa haluamasi aloitus- ja lopetusnumerot; Voit myös käyttää muuttujia näille numeroille.
Oletusarvoisesti For-Next-silmukat lasketaan yhdellä. Jos haluat laskea toisella numerolla, voit kirjoittaa silmukan nimenomaisella Vaihe lauseke:
I = 5 - 100 Vaihe 5
Tämä silmukka alkaa 5: stä ja lisää sitten "i": hen 5 aina, kun silmukka toistuu (joten "i" on 10 toisella toistolla, 15 kolmannella ja niin edelleen).
Käyttämällä Vaihe, voit myös laskea silmukat taaksepäin:
Jos i = 100 To 1 Vaihe -1
Voit myös pesä For-Next-silmukat. Jokainen lohko vaatii oman muuttujansa laskemiseen, mutta voit käyttää näitä muuttujia missä haluat. Tässä on esimerkki siitä, miten se on hyödyllistä Excel VBA: ssa:
Dim i Niin pitkä, j Niin kauan I = 1-100 J = 1-100 solua (i, j) Arvo = i * j Seuraava j Seuraava i
Tämän avulla voit selata sekä rivejä että sarakkeita.
VAROITUS: vaikka se on sallittua, ÄLÄ KOSKAAN MUOKKAA iteraattorimuuttujaa For-Next-lohkon sisällä, koska se käyttää kyseistä iteraattoria silmukan seurantaan. Iteraattorin muokkaaminen voi aiheuttaa äärettömän silmukan ja ripustaa makron. Esimerkiksi:
Sillä i = 1 - 100 i = 1 Seuraava i
Tässä silmukassa "minä" ei koskaan ylitä 2: ta ennen kuin se palautetaan 1: ksi, ja silmukka toistuu ikuisesti.
Jokaiselle
Jokaiselle lohkot ovat hyvin samankaltaisia For-Next-lohkojen kanssa, paitsi että ne eivät käytä laskuria määrittämään, kuinka monta kertaa ne silmukoivat. Sen sijaan For-Each-lohko ottaa "kokoelman" esineitä (kuten solualue) ja suoritetaan niin monta kertaa kuin kokoelmassa on objekteja.
Tässä on esimerkki:
Dim r Kuten alue jokaiselle alueen alueelle ("A15: J54") Jos r.arvo> 0 Sitten r.Font.Bold = Todellinen loppu jos seuraava r
Huomaa Range -objektimuuttujan "r" käyttö. Tämä on For -Every -silmukassa käytetty iteraattomuuttuja - joka kerta silmukan läpi, r saa viittauksen alueen seuraavaan soluun.
Etuna For-Every-silmukoiden käyttämisessä Excel VBA: ssa on, että voit silmukoida alueen kaikki solut ilman sisäkkäisiä silmukoita. Tämä voi olla kätevää, jos sinun täytyy käydä läpi kaikki solut monimutkaisella alueella, kuten Alue (“A1: D12, J13, M1: Y12”).
Yksi For-Every-silmukoiden haittapuoli on se, että et voi hallita solujen käsittelyjärjestystä. Siitä huolimatta käytännössä Excel kiertää soluja järjestyksessä, teoriassa se voisi käsitellä soluja täysin satunnaisessa järjestyksessä. Jos sinun on käsiteltävä soluja tietyssä järjestyksessä, käytä sen sijaan For-Next-silmukoita.
Tee silmukka
Vaikka For-Next-lohkot käyttävät laskureita tietääkseen milloin pysähtyä, Tee silmukka lohkot toimivat, kunnes ehto täyttyy. Voit tehdä tämän käyttämällä Siihen asti kun lauseen alussa tai lopussa oleva lauseke, joka testaa tilan ja saa silmukan pysähtymään, kun ehto täyttyy.
Esimerkki:
Dim str Kuten String str = "Buffalo" Tee kunnes str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Loop Range ("A1"). Arvo = str
Tässä silmukassa "Buffalo" yhdistetään joka kerta silmukan kautta "str" -kenttään, kunnes se vastaa odotettua virkettä. Tässä tapauksessa testi suoritetaan silmukan alussa - jos 'str' oli jo odotettu lause (jota se ei ole, koska emme aloittaneet sitä tällä tavalla, mutta jos) silmukka ei edes toimisi .
Voit saada silmukan toimimaan ainakin kerran siirtämällä kunnes -lausekkeen loppuun, näin:
Tee str = str & "" & "Buffalo" Loop kunnes str = “Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo”
Voit käyttää makrossa järkevää versiota.
VAROITUS: voit aiheuttaa äärettömän silmukan Do-Loop-lohkolla, jos Kunnes-ehto ei koskaan täyty. Kirjoita aina koodisi niin, että Kunnes -ehto täyttyy varmasti, kun käytät tällaista silmukkaa.
Mitä seuraavaksi?
Kun olet ymmärtänyt perusasiat, miksi et yritä oppia kehittyneempiä tekniikoita? Opetusohjelmamme osoitteessa https://easyexcel.net/excel/learn-vba-tutorial/ perustuu kaikkeen täällä oppimaasi ja laajentaa taitojasi tapahtumilla, käyttäjämuodoilla, koodin optimoinnilla ja paljon muulla!
