Excel makroopptaker har mye kraft, men den har sine begrensninger. Som dekket i en annen artikkel, registrerer makroopptakeren ofte unødvendig kode og kan ikke registrere ting som logikk eller interaksjoner med andre programmer. Det kan også være vanskelig å bruke for lengre makroer - du kan begynne å skrive ned handlingene dine på forhånd for å unngå å gjøre kostbare feil.
Denne artikkelen tar sikte på å hjelpe deg med å begynne å kode makroer fra bunnen av i VBA. Du lærer hvor makroer er lagret, skriver en grunnleggende makro og lærer det grunnleggende om programmering i VBA ved hjelp av variabler, logikk og sløyfer.
Starter
VBA og Visual Basic Editor
VBA, eller Visual Basic for Applications, er språket som makroene skrives på. Alle makroer lagres som VBA-kode, enten de er håndkodet eller laget med makroopptakeren.
Du kan få tilgang til all VBA -koden i en arbeidsbok ved hjelp av Visual Basic Editor. Dette er en spesiell tekstredigerer og feilsøkingsprogram som er innebygd i alle kontorapper, inkludert Excel. Vanligvis åpner du denne redaktøren med ALT+F11 hurtigtast i Excel, men du kan også få tilgang til den fra Excel Utvikler kategorien hvis du har den aktivert.
Prosjektutforsker
De Project Explorer er et vindu inne i VB Editor som viser deg alle elementene som kan ha VBA -kode i dem. Hvis du ikke ser dette vinduet, trykker du på F5 for å få det til å vises eller velge Project Explorer fra Utsikt Meny.
Dobbeltklikk på et element i Project Explorer vil vise koden for det elementet. Det er flere typer elementer som kan vises i Project Explorer:
- Arbeidsbøker
- Arbeidsark
- UserForms
- Klassemoduler
- Moduler (makroer er lagret i disse elementene)
Selv om alle disse elementtypene kan inneholde VBA -kode, er den beste fremgangsmåten å kode makroer i moduler.
Lag din første makro
Bruke makrolisten
Makrolisten viser deg alle makroene i arbeidsboken. Fra denne listen kan du redigere en eksisterende makro, eller opprette en ny.
Slik oppretter du en ny makro ved å bruke makrolisten:
- Velg kategorien Utvikler og klikk Makroer (eller trykk ALT+F8)
- Skriv inn et nytt navn for makroen din, og klikk deretter "Opprett"
Etter å ha klikket "Opprett" vil VB Editor vises, og viser den nyopprettede makroen. Excel vil opprette en ny modul for makroen om nødvendig.
Manuelt i VB Editor
Du kan legge til en ny makro manuelt uten makrolisten. Dette er det bedre alternativet hvis du vil spesifisere modulen makroen er lagret i.
Slik legger du til en makro manuelt:
- Åpne VB Editor (ALT+F11)
- Enten:
- Legg til en ny modul ved å klikke Sett inn> modul på menyen (modulen åpnes automatisk)
-
- ELLER dobbeltklikk på en eksisterende modul i Project Explorer for å åpne den
- Skriv inn koden for den nye makroen i modulen
Sub MyMacro () End Sub
Disse to linjene indikerer begynnelsen og slutten av en makro som heter "MyMacro" (merk parentesene, som er nødvendige). Dette vises i dialogboksen "Vis makroer" i Excel og kan tilordnes en knapp (selv om den ikke gjør noe ennå).
Legg til litt kode i makroen
La oss legge til en kode mellom "Sub" og "End Sub" linjene for å få denne makroen til å gjøre noe:
Sub MyMacro () Område (“A1”). Verdi = “Hei verden!” Slutt Sub
Grunnleggende kodestrukturer
Range Object
Excel VBA bruker Range Object til å representere celler i et regneark. I eksemplet ovenfor opprettes et Range -objekt med koden Rekkevidde ("A1") for å få tilgang til verdien av celle A1.
Områdeobjekter brukes hovedsakelig til å angi celleverdier:
Område ("A1"). Verdi = 1
Område ("A1"). Verdi = "Første celle"
Legg merke til at når du definerer celleverdier som tall, skriver du bare inn tallet, men når du skriver inn tekst, må du omgi teksten med anførselstegn.
Områder kan også brukes til å få tilgang til mange egenskaper til celler som skrifttype, grenser, formler og mer.
For eksempel kan du sette skriften til en celle til fet slik:
Område ("A1"). Font.Bold = True
Du kan også angi formelen til en celle:
Område ("A1"). Formel = "= Sum (A2: A10)"
I Excel kan du velge en blokk med celler med markøren (si fra A1 til D10) og sette dem alle til fet skrift. Områdeobjekter kan få tilgang til blokker av celler som dette:
Område ("A1: D10"). Font.Bold = True
Du kan også referere til flere celler/blokker samtidig:
Område ("A1: D10, A12: D12, G1"). Font.Bold = True
Formatet for dette er det samme som formatet du vil bruke når du velger celler for formelen SUM () i Excel. Hver blokk er atskilt med et komma, og blokker er markert med cellene øverst til venstre og nederst til høyre atskilt med et kolon.
Til slutt har Range-objekter innebygde metoder for å utføre vanlige operasjoner på et regneark. For eksempel kan det være lurt å kopiere data fra ett sted til et annet. Her er et eksempel:
Område ("A1: D10"). Kopier område ("F1"). PasteSpecial xlPasteValues Range ("F1"). PasteSpecial xlPasteFormats
Dette kopierer cellene A1: D10 til utklippstavlen, og gjør deretter en PasteSpecial () som starter i celle C1 - akkurat som du ville gjort manuelt i Excel. Merk at dette eksemplet viser hvordan du bruker PasteSpecial () til å lime inn bare verdier og formater - det er parametere for alle alternativene du vil se i dialogboksen Lim inn spesiell.
Her er et eksempel som limer "Alt" til et annet regneark:
Område ("A1: D10"). Kopieringsark ("ark2"). Område ("A1"). PasteSpecial xlPasteAll
Hvis uttalelser
Med en Hvis uttalelse, kan du bare lage en seksjon med kode "hvis" en bestemt påstand er sann.
For eksempel kan det være lurt å gjøre en celle fet og farge den rød, men bare "hvis" verdien i cellen er mindre enn 100.
If Range (“A4”). Verdi <100 Then Range (“A4”). Font.Bold = True Range (“A4”). Interior.Color = vbRed End If
Den riktige strukturen til en If -setning er som følger (firkantede parenteser angir valgfrie komponenter):
Hvis da
[Ellers hvis da]
[Ellers]
Slutt om
Du kan inkludere så mange Eller hvis blokkerer som du vil teste flere forhold. Du kan også legge til en Ellers blokk som bare kjører hvis ingen av de andre betingelsene i If -setningen er oppfylt.
Her er et annet eksempel basert på den forrige, der cellen er formatert på flere forskjellige måter avhengig av verdien:
Hvis område ("A4"). Verdi <100 Da område ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed ElseIf Range ("A4"). Verdi <200 Deretter Range (200 "A4"). Font.Bold = Falskt område ("A4"). Interiør.Color = vbGult annet område ("A4"). Font.Bold = Falskt område ("A4"). Interior.Color = vbGrønn ende hvis
I eksemplet ovenfor er cellen uten fet skrift i ElseIf-blokkene der verdien ikke er under 100. Du kan rede Hvis uttalelser for å unngå duplisering av kode, slik:
Hvis område ("A4"). Verdi <100 Da område ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed Else Range ("A4"). Font.Bold = False ' opphever skriften bare én gang hvis område ("A4"). verdi <200 deretter område ("A4"). interiør.Color = vbGult annet område ("A4"). Interior.Color = vbGrønn slutt hvis slutt hvis
Variabler
EN Variabel er et minne som brukes til å lagre midlertidig informasjon mens en makro kjører. De brukes ofte i sløyfer som iteratorer, eller for å holde resultatet av en operasjon du vil bruke flere ganger gjennom en makro.
Her er et eksempel på en variabel og hvordan du kan bruke den:
Sub ExtractSerialNumber () Dim strSerial As String 'dette er variabeldeklarasjonen' 'As String' betyr at denne variabelen er ment å holde tekst 'ved å sette opp et late-serienummer: Range ("A4"). Value = "serial# 804567-88 ”'Analyser serienummeret fra celle A4 og tildel det variabelen strSerial = Mid (Range (" A4 "). Verdi, 9)' bruk nå variabelen to ganger, i stedet for å måtte analysere serienummeret to ganger Range (" B4 ”). Verdi = strSerial MsgBox strSerial End Sub
I dette grunneksemplet brukes variabelen 'strSerial' for å trekke ut serienummeret fra celle A4 ved hjelp av Mid () -funksjonen, og deretter bli brukt på to andre steder.
Den vanlige måten å erklære en variabel er som følger:
Dim hvilket navn [Som type]
- hvilket navn er navnet du bestemmer deg for å gi variabelen din
- type er datatypen til variabelen
"[Som type] ”-Del kan utelates - i så fall deklareres variabelen som en varianttype, som kan inneholde alle slags data. Selv om de er helt gyldige, bør varianttyper unngås siden de kan føre til uventede resultater hvis du ikke er forsiktig.
Det er regler for variabelnavn. De må begynne med enten en bokstav eller et understreketegn, kan ikke ha mellomrom, punktum, komma, anførselstegn eller tegnene "! @ & $ #”.
Her er noen eksempler på variable deklarasjoner:
Dim strFilename As String 'god navnestil - beskrivende og bruker prefiks Dim i As Long' dårlig navnestil - bare akseptabelt for noen iteratorer Dim SalePrice As Double 'ok navnestil - beskrivende, men bruker ikke prefiks Dim iCounter' ok navn - ikke for beskrivende, bruker prefiks, ingen datatype
Alle disse eksemplene bruker litt forskjellige navnesystemer, men alle er gyldige. Det er ikke en dårlig idé å prefiks et variabelnavn med en kort form for datatype (i henhold til noen av disse eksemplene), siden det gjør koden din mer lesbar med et øyeblikk.
VBA inneholder mye grunnleggende datatyper. De mest populære inkluderer:
- String (brukes til å holde tekstdata)
- Lang (brukes til å holde hele tall, dvs. ingen desimaler)
- Dobbelt (brukes til å holde flytende tall, dvs. desimaler)
En fullstendig oversikt over VBA-innebygde datatyper finner du her: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Range Object Variables
Det er også mulig å lage variabler som refererer til Range Objects. Dette er nyttig hvis du vil referere til et bestemt område i koden din flere steder - på den måten hvis du trenger å endre området, trenger du bare å endre det på ett sted.
Når du oppretter en Range -objektvariabel, må du "sette" den til en forekomst av et Range. For eksempel:
Dim rMyRange As Range Set rMyRange = Range (“A1: A10; D1: J10”)
Hvis du lar "Set" -setningen stå ute når du tildeler en variabel for område, vil det resultere i en feil.
Sløyfer
Loops er blokker som gjentar koden inne i dem et visst antall ganger. De er nyttige for å redusere mengden kode du må skrive, og lar deg skrive ett stykke kode som utfører de samme handlingene på mange forskjellige relaterte elementer.
For-Neste
EN For-Neste blokk er en sløyfe som gjentas et visst antall ganger. Den bruker en variabel som en iterator å telle hvor mange ganger den har kjørt, og denne iteratorvariabelen kan brukes inne i løkken. Dette gjør For-Next-løkker veldig nyttige for iterering gjennom celler eller matriser.
Her er et eksempel som går gjennom celler i rad 1 til 100, kolonne 1, og setter verdiene til verdien av iteratorvariabelen:
Dim i så lenge for i = 1 til 100 celler (i, 1) .Værdi = i Neste i
Linjen “For i = 1 til 100” betyr at løkken starter fra 1 og slutter etter 100. Du kan angi alle start- og sluttnumre du liker; Du kan også bruke variabler for disse tallene.
Som standard teller For-Next-løkker med 1. Hvis du vil telle med et annet tall, kan du skrive løkken med en eksplisitt Steg klausul:
For i = 5 Til 100 Trinn 5
Denne sløyfen starter med 5, og legger deretter 5 til ‘i’ hver gang løkken gjentas (så ‘i’ vil være 10 på den andre repetisjonen, 15 på den tredje og så videre).
Ved hjelp av Steg, du kan også få en løkke til å telle bakover:
For i = 100 Til 1 Trinn -1
Du kan også rede For-Next sløyfer. Hver blokk krever sin egen variabel å telle med, men du kan bruke disse variablene hvor du vil. Her er et eksempel på hvordan det er nyttig i Excel VBA:
Dim i så lenge, j så lenge for i = 1 til 100 for j = 1 til 100 celler (i, j) .Værdi = i * j Neste j Neste i
Dette lar deg gå gjennom både rader og kolonner.
ADVARSEL: selv om det er tillatt, bør du ALDRI MODIFIERE iterator-variabelen inne i en For-Next-blokk, siden den bruker den iteratoren for å holde oversikt over løkken. Hvis du endrer iteratoren, kan det føre til en uendelig sløyfe og henge makroen. For eksempel:
For i = 1 Til 100 i = 1 Neste i
I denne sløyfen kommer ‘jeg’ aldri over 2 før den blir tilbakestilt til 1, og sløyfen vil gjenta seg for alltid.
For hver
For hver blokker ligner veldig på For-Next-blokker, bortsett fra at de ikke bruker en teller til å angi hvor mange ganger de skal sløyfes. I stedet tar en For-hver-blokk en "samling" av objekter (som et celleområde) og kjører så mange ganger som det er objekter i den samlingen.
Her er et eksempel:
Dim r som område for hver r i område ("A15: J54") Hvis r.Value> 0 Så r.Font.Bold = True End If Next r
Legg merke til bruken av Range -objektvariabelen ‘r’. Dette er iteratorvariabelen som brukes i For -Each -sløyfen - hver gang gjennom sløyfen får ‘r’ en referanse til den neste cellen i området.
En fordel med å bruke For-Each-løkker i Excel VBA er at du kan gå gjennom alle cellene i et område uten å hekke sløyfer. Dette kan være nyttig hvis du trenger å gå gjennom alle cellene i et komplekst område som Område (“A1: D12, J13, M1: Y12”).
En ulempe med For-Each-løkker er at du ikke har kontroll over rekkefølgen der cellene behandles. Selv om i praksis Excel vil gå gjennom cellene i rekkefølge, i teorien det kan behandle cellene i en helt tilfeldig rekkefølge. Hvis du trenger å behandle celler i en bestemt rekkefølge, bør du bruke For-Next-løkker i stedet.
Gjør-sløyfe
Mens For-Next-blokker bruker tellere for å vite når de skal stoppe, Gjør-sløyfe blokker kjøres til en betingelse er oppfylt. For å gjøre dette, bruker du en Før klausul på enten begynnelsen eller slutten av blokken, som tester tilstanden og får sløyfen til å stoppe når den betingelsen er oppfylt.
Eksempel:
Dim str As String str = "Buffalo" Do Til str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Loop Range ("A1"). Value = str
I denne sløyfen er "Buffalo" sammenkoblet til "str" hver gang gjennom løkken til den samsvarer med den forventede setningen. I dette tilfellet utføres testen i begynnelsen av løkken - hvis 'str' allerede var den forventede setningen (som det ikke er fordi vi ikke startet den på den måten, men hvis) løkken ikke engang ville kjøre .
Du kan få løkken til å løpe minst én gang ved å flytte Til -klausulen til slutten, slik:
Gjør str = str & "" & "Buffalo" Loop Til str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Du kan bruke hvilken versjon som er fornuftig i makroen.
ADVARSEL: Du kan forårsake en uendelig sløyfe med en Do-Loop-blokk hvis Til-betingelsen aldri er oppfylt. Skriv alltid koden din slik at Til -betingelsen definitivt vil bli oppfylt når du bruker denne typen løkker.
Hva blir det neste?
Når du har forstått det grunnleggende, hvorfor ikke prøve å lære noen mer avanserte teknikker? Opplæringen vår på https://easyexcel.net/excel/learn-vba-tutorial/ vil bygge på alt du har lært her og utvide dine ferdigheter med Events, UserForms, kodeoptimalisering og mye mer!
