Excel makroinspelare har mycket kraft, men det har sina begränsningar. Som beskrivs i en annan artikel, spelar makroinspelaren ofta onödig kod och kan inte spela in saker som logik eller interaktioner med andra program. Det kan också vara svårt att använda för längre makron - du kan komma på att storyboarda dina handlingar i förväg bara för att undvika att göra dyra misstag.
Denna artikel syftar till att hjälpa dig att börja koda makron från grunden i VBA. Du lär dig var makron lagras, skriver ett grundläggande makro och lär dig grunderna för programmering i VBA med hjälp av variabler, logik och loopar.
Komma igång
VBA och Visual Basic Editor
VBA, eller Visual Basic for Applications, är språket som makron skrivs på. Alla makron lagras som VBA-kod, oavsett om de är handkodade eller skapade med makroinspelaren.
Du kan komma åt alla VBA -koder i en arbetsbok med Visual Basic Editor. Detta är en speciell textredigerare och felsökare som är inbyggd i alla kontorsappar, inklusive Excel. Vanligtvis öppnar du den här redigeraren med ALT+F11 kortkommando i Excel, men du kan också komma åt den från Excel Utvecklare fliken om du har den aktiverad.
Projektutforskaren
De Project Explorer är ett fönster inuti VB Editor som visar dig alla objekt som kan ha VBA -kod i dem. Om du inte ser det här fönstret trycker du på F5 för att få det att visas eller välja Project Explorer från Se meny.
Om du dubbelklickar på ett objekt i Project Explorer visas koden för det objektet. Det finns flera typer av objekt som kan visas i Project Explorer:
- Arbetsböcker
- Arbetsblad
- UserForms
- Klassmoduler
- Moduler (makron lagras i dessa objekt)
Även om alla dessa artikeltyper kan innehålla VBA -kod, är bästa praxis att koda makron i moduler.
Gör ditt första makro
Använda makrolistan
Makrolistan visar alla makron i din arbetsbok. Från den här listan kan du redigera ett befintligt makro eller skapa ett nytt.
Så här skapar du ett nytt makro med hjälp av makrolistan:
- Välj fliken Utvecklare och klicka Makron (eller tryck på ALT+F8)
- Skriv in ett nytt namn för ditt makro och klicka sedan på "Skapa"
Efter att ha klickat på “Skapa” visas VB Editor som visar det nyskapade makrot. Excel kommer att skapa en ny modul för makrot om det behövs.
Manuellt i VB Editor
Du kan lägga till ett nytt makro manuellt utan makrolistan. Detta är det bättre alternativet om du vill ange modulen som makrot sparas i.
Så här lägger du till ett makro manuellt:
- Öppna VB Editor (ALT+F11)
- Antingen:
- Lägg till en ny modul genom att klicka Infoga> Modul på menyn (modulen öppnas automatiskt)
-
- ELLER dubbelklicka på en befintlig modul i Project Explorer för att öppna den
- Skriv in koden för ditt nya makro i modulen
Sub MyMacro () Slut Sub
Dessa två rader indikerar början och slutet av ett makro med namnet "MyMacro" (notera de parenteser som krävs). Detta kommer att visas i dialogrutan "Visa makron" i Excel och kan tilldelas en knapp (även om den inte gör något ännu).
Lägg till lite kod i makrot
Låt oss nu lägga till en kod mellan raderna "Sub" och "End Sub" för att få detta makro att göra något:
Sub MyMacro () Område ("A1"). Värde = "Hej världen!" Avsluta Sub
Grundläggande kodstrukturer
Räckviddsobjektet
Excel VBA använder intervallobjektet för att representera celler i ett kalkylblad. I exemplet ovan skapas ett intervallobjekt med koden Område ("A1") för att komma åt värdet på cell A1.
Områdeobjekt används främst för att ställa in cellvärden:
Område ("A1"). Värde = 1
Område ("A1"). Värde = "Första cellen"
Observera att när du definierar cellvärden som siffror anger du bara numret, men när du anger text måste du omge texten med citat.
Områden kan också användas för att komma åt många egenskaper hos celler som deras teckensnitt, gränser, formler och mer.
Till exempel kan du ställa in teckensnittet för en cell till fet sådan:
Område ("A1"). Font.Bold = True
Du kan också ställa in formeln för en cell:
Område ("A1"). Formel = "= Summa (A2: A10)"
I Excel kan du välja ett cellblock med markören (säg från A1 till D10) och sätta dem alla till fetstil. Områdeobjekt kan komma åt block av celler så här:
Område ("A1: D10"). Font.Bold = True
Du kan också hänvisa till flera celler/block samtidigt:
Område ("A1: D10, A12: D12, G1"). Font.Bold = True
Formatet för detta är detsamma som det format du skulle använda när du markerade celler för formeln SUM () i Excel. Varje block separeras med ett kommatecken, och block markeras med de övre vänstra och nedre högra cellerna separerade med ett kolon.
Slutligen har Range-objekt inbyggda metoder för att utföra vanliga operationer i ett kalkylblad. Till exempel kanske du vill kopiera data från en plats till en annan. Här är ett exempel:
Område ("A1: D10"). Kopieringsintervall ("F1"). PasteSpecial xlPasteValues Range ("F1"). PasteSpecial xlPasteFormats
Detta kopierar cellerna A1: D10 till Urklipp och gör sedan en PasteSpecial () som börjar i cell C1 - precis som du skulle göra manuellt i Excel. Observera att det här exemplet visar hur du använder PasteSpecial () för att klistra in endast värden och format - det finns parametrar för alla alternativ som du ser i dialogrutan Klistra in special.
Här är ett exempel som klistrar in "Allt" i ett annat kalkylblad:
Område ("A1: D10"). Kopieringsark ("blad2"). Område ("A1"). PastaSpecial xlPasteAll
If uttalanden
Med en If uttalande, du kan bara få en koddel att köra "om" ett visst påstående är sant.
Till exempel kanske du vill göra en cell fet och färga den röd, men bara "om" värdet i cellen är mindre än 100.
If Range (“A4”). Värde <100 Then Range (“A4”). Font.Bold = True Range (”A4”). Interior.Color = vbRed End If
Den korrekta strukturen för en If -sats är följande (hakparenteser anger valfria komponenter):
Om då
[Annars om då]
[Annan]
Avsluta om
Du kan inkludera så många Annars om block som du vill testa flera villkor. Du kan också lägga till en Annan block som bara körs om inget av de andra villkoren i if -satsen är uppfyllt.
Här är ett annat exempel baserat på det föregående, där cellen formateras på flera olika sätt beroende på värdet:
If Range ("A4"). Värde <100 Sedan Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed ElseIf Range ("A4"). Value <200 Then Range ( "A4"). Font.Bold = Falskt intervall ("A4"). Interior.Color = vbGult Else Range ("A4"). Font.Bold = Falskt intervall ("A4"). Interior.Color = vbGreen End Om
I exemplet ovan är cellen avfettad i ElseIf-blocken där värdet inte är under 100. Du kan bo Om uttalanden för att undvika kopiering av kod, så här:
If Range ("A4"). Värde <100 Sedan Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed Else Range ("A4"). Font.Bold = False ' tar bara bort teckensnittet en gång om intervall ("A4"). värde <200 sedan intervall ("A4"). interiör.Color = vbGult annat intervall ("A4"). Interior.Color = vbGreen End If End Om
Variabler
A Variabel är ett minne som används för att lagra tillfällig information medan ett makro körs. De används ofta i loopar som iteratorer, eller för att hålla resultatet av en operation som du vill använda flera gånger under ett makro.
Här är ett exempel på en variabel och hur du kan använda den:
Sub ExtractSerialNumber () Dim strSerial As String 'detta är variabeldeklarationen' 'As String' betyder att denna variabel är avsedd att hålla text 'som ställer in ett låtsas serienummer: Range ("A4"). Value = "serial# 804567-88 ”'Analysera serienumret från cell A4 och tilldela variabeln strSerial = Mid (Range (" A4 "). Värde, 9)" använd nu variabeln två gånger, istället för att behöva analysera serienumret två gånger Range (" B4 ”). Value = strSerial MsgBox strSerial End Sub
I detta grundläggande exempel används variabeln 'strSerial' för att extrahera serienumret från cell A4 med funktionen Mid (), och används sedan på två andra platser.
Det vanliga sättet att deklarera en variabel är följande:
Dämpa vilket namn [Som typ]
- vilket namn är namnet du väljer att ge din variabel
- typ är datatypen för variabeln
"[Som typ] ”-Del kan utelämnas - i så fall deklareras variabeln som en varianttyp, som kan innehålla alla typer av data. Även om de är helt giltiga bör varianttyper undvikas eftersom de kan leda till oväntade resultat om du inte är försiktig.
Det finns regler för variabelnamn. De måste börja med antingen en bokstav eller ett understrykningstecken, kan inte ha mellanslag, punkter, kommatecken, citattecken eller tecknen ”! @ & $ #”.
Här är några exempel på variabla deklarationer:
Dim strFilename As String 'bra namnstil - beskrivande och använder prefix Dim i As Long' dåligt namnstil - accepteras endast för vissa iteratorer Dim SalePrice As Double 'okej namnstil - beskrivande, men använder inte prefix Dim iCounter' okej namn - inte för beskrivande, använder prefix, ingen datatyp
Alla dessa exempel använder lite olika namngivningsscheman, men alla är giltiga. Det är inte en dålig idé att prefixa ett variabelnamn med en kort form av dess datatyp (enligt några av dessa exempel), eftersom det gör din kod mer läsbar vid en överblick.
VBA innehåller mycket grundläggande datatyper. De mest populära inkluderar:
- Sträng (används för att hålla textdata)
- Lång (används för att hålla hela tal, dvs inga decimaler)
- Dubbel (används för att hålla flytande siffror, dvs. decimaler)
En fullständig lista över inneboende datatyper för VBA finns här: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Områdeobjektvariabler
Det är också möjligt att skapa variabler som refererar till Range Objects. Detta är användbart om du vill hänvisa till ett specifikt intervall i din kod på flera ställen - så om du behöver ändra intervallet behöver du bara ändra det på ett ställe.
När du skapar en Range -objektvariabel måste du "ställa in" den till en instans av ett Range. Till exempel:
Dim rMyRange As Range Set rMyRange = Range (“A1: A10; D1: J10”)
Om du lämnar "Set" -uttrycket när du tilldelar en intervallvariabel kommer det att resultera i ett fel.
Slingor
Slingor är block som upprepar koden i dem ett visst antal gånger. De är användbara för att minska mängden kod du måste skriva, och låter dig skriva en kodbit som utför samma åtgärder på många olika relaterade objekt.
För nästa
A För nästa block är en loop som upprepas ett visst antal gånger. Den använder en variabel som en iterator att räkna hur många gånger den har körts, och denna iteratorvariabel kan användas inuti slingan. Detta gör For-Next-slingor mycket användbara för iterering genom celler eller matriser.
Här är ett exempel som går igenom cellerna i raderna 1 till 100, kolumn 1 och anger värdena för iteratorvariabeln:
Dim i så länge för i = 1 till 100 celler (i, 1) .Värde = i Nästa i
Raden ”För i = 1 till 100” betyder att slingan börjar från 1 och slutar efter 100. Du kan ställa in alla start- och slutnummer som du vill; Du kan också använda variabler för dessa nummer.
Som standard räknas For-Next-loopar med 1. Om du vill räkna med ett annat nummer kan du skriva loopen med ett uttryckligt Steg klausul:
För i = 5 Till 100 Steg 5
Den här slingan börjar vid 5, lägg sedan till 5 till ‘i’ varje gång slingan upprepas (så ‘i’ blir 10 på den andra repetitionen, 15 på den tredje och så vidare).
Använder sig av Steg, du kan få en loop att räkna bakåt också:
För i = 100 Till 1 Steg -1
Du kan också bo För-Nästa slingor. Varje block kräver sin egen variabel att räkna med, men du kan använda dessa variabler var du vill. Här är ett exempel på hur det är användbart i Excel VBA:
Dim i Så länge, j Så länge för i = 1 till 100 för j = 1 till 100 celler (i, j) .Värde = i * j Nästa j Nästa i
Detta låter dig gå igenom både rader och kolumner.
VARNING: även om det är tillåtet, bör du ALDRIG modifiera iteratorvariabeln inuti ett For-Next-block, eftersom den använder den iteratorn för att hålla reda på slingan. Att ändra iteratorn kan orsaka en oändlig slinga och hänga ditt makro. Till exempel:
För i = 1 Till 100 i = 1 Nästa i
I den här slingan kommer 'jag' aldrig mer än 2 innan den återställs till 1, och slingan kommer att upprepas för alltid.
För varje
För varje block är väldigt lika For-Next-block, förutom att de inte använder en räknare för att ange hur många gånger de ska loopas. I stället tar ett för-varje-block en "samling" av objekt (som ett cellintervall) och körs så många gånger som det finns objekt i den samlingen.
Här är ett exempel:
Dim r som intervall för varje r i intervall ("A15: J54") Om r.Value> 0 Sedan r.Font.Bold = True End If Next r
Lägg märke till användningen av Range -objektvariabeln ‘r’. Detta är iteratorvariabeln som används i For -Each -slingan - varje gång genom loop, får 'r' en referens till nästa cell i intervallet.
En fördel med att använda For-Each-slingor i Excel VBA är att du kan gå igenom alla celler i ett område utan att hälla slingor. Detta kan vara praktiskt om du behöver gå igenom alla celler i ett komplext område som Område (“A1: D12, J13, M1: Y12”).
En nackdel med For-Each loopar är att du inte har någon kontroll över i vilken ordning celler bearbetas. Fastän i praktiken Excel går igenom cellerna i ordning, i teorin det kan bearbeta cellerna i en helt slumpmässig ordning. Om du behöver bearbeta celler i en viss ordning bör du använda For-Next-slingor istället.
Gör-slinga
Medan For-Next-block använder räknare för att veta när de ska sluta, Gör-slinga block körs tills ett villkor är uppfyllt. För att göra detta använder du en Fram tills klausul antingen i början eller slutet av blocket, som testar tillståndet och får slingan att stanna när det villkoret är uppfyllt.
Exempel:
Dim str As String str = "Buffalo" Do Till str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Loop Range ("A1"). Value = str
I denna slinga sammanfogas "Buffalo" till "str" varje gång genom öglan tills den matchar den förväntade meningen. I det här fallet utförs testet i början av slingan - om 'str' redan var den förväntade meningen (vilket det inte är för att vi inte startade det på det sättet, men om) slingan inte ens skulle köras .
Du kan få slingan att köra minst en gång genom att flytta till -klausulen till slutet, så här:
Gör str = str & "" & "Buffalo" Loop Till str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Du kan använda vilken version som är vettig i ditt makro.
VARNING: du kan orsaka en oändlig loop med ett Do-Loop-block om Till-villkoret aldrig är uppfyllt. Skriv alltid din kod så att Till -villkoret definitivt kommer att uppfyllas när du använder denna typ av loop.
Vad kommer härnäst?
När du väl har förstått grunderna, varför inte försöka lära dig mer avancerade tekniker? Vår handledning på https://easyexcel.net/excel/learn-vba-tutorial/ kommer att bygga vidare på allt du har lärt dig här och utöka dina färdigheter med Events, UserForms, kodoptimering och mycket mer!
