När du väl har lärt dig att deklarera en variabel med Dim och välja rätt datatyp kommer du snart till en gräns. En vanlig variabel rymmer bara ett värde åt gången. Ska du hålla reda på tolv månadsbelopp eller en lista med kundnamn blir det snabbt rörigt med en variabel per värde. Då är det dags för en array.

En array i VBA är en variabel som lagrar flera värden under ett och samma namn. Varje värde ligger på en egen plats, och du når platsen med ett index, ett nummer inom parentes efter variabelnamnet. Det gör koden kortare och lättare att underhålla. Det öppnar också för snabba lösningar där du läser in ett helt cellområde på en gång i stället för att arbeta cell för cell.

I den här artikeln går vi igenom hur du arbetar med array VBA-kod steg för steg:

  • hur du deklarerar en array med Dim
  • hur index fungerar och var numreringen börjar
  • hur du fyller arrayen, läser ut värden och loopar igenom den
  • hur du läser in ett cellområde till en array
  • hur du ändrar storlek med ReDim och ReDim Preserve

Om du är ny på VBA och inte riktigt har koll på Dim och datatyper ännu, läs först om hur du deklarera variabler i Excel VBA. Den här artikeln bygger vidare på just de grunderna.

Vad är en array och när ska du använda den?

En vanlig variabel rymmer ett enda värde. Ska du hålla reda på fem namn får du deklarera fem variabler, och det blir snabbt klumpigt:

Dim namn1 As String, namn2 As String, namn3 As String, namn4 As String, namn5 As String

En array löser samma sak med en enda variabel som har flera platser. I stället för namn1 till namn5 har du en variabel namn där varje plats nås med ett nummer, ett index: namn(0), namn(1) och så vidare. Tänk på arrayen som en kolumn i ett kalkylblad, där variabelnamnet är kolumnen och indexet är radnumret.

Fördelen märks direkt när du ska göra något med alla värden. Med fem separata variabler måste du skriva en kodrad per variabel. Med en array kan en loop gå igenom samtliga platser, oavsett om de är fem eller femtusen.

Typiska användningsfall

  • Listor av namn eller tal – till exempel säljare, månader, artikelnummer eller mätvärden som ska hanteras på samma sätt.
  • Läsa in ett cellområde – ett helt område i bladet kan läsas in till en array med en enda kodrad. Sedan arbetar du med värdena i minnet i stället för direkt mot cellerna.
  • Snabbare bearbetning – varje gång makrot läser eller skriver en enskild cell sker ett anrop mellan VBA och Excel, och det tar tid. Att läsa in området till en array, bearbeta värdena där och skriva tillbaka allt på en gång går oftast betydligt snabbare än att arbeta cell för cell, särskilt när det gäller många rader.
  • Samla in värden under körningen – när du inte vet i förväg hur många värden som ska sparas, till exempel alla rader som uppfyller ett villkor, kan arrayen växa efter behov.

Om du bara behöver ett eller ett par värden räcker vanliga variabler gott. Arrayen gör nytta när du har flera värden av samma slag som ska behandlas tillsammans.

Deklarera en array med Dim

Du deklarerar en array med Dim precis som en vanlig variabel. Skillnaden är att du anger storleken inom parentes efter namnet. Om du behöver repetera grunderna kan du läsa mer om att deklarera variabler i Excel VBA.

Fast storlek med index från 0

Den enklaste formen ser ut så här:

Dim namn(4) As String

Siffran 4 anger inte antalet platser. Den anger det högsta indexet. Eftersom VBA som standard börjar räkna på 0 får arrayen indexen 0, 1, 2, 3 och 4, alltså fem platser. Många nybörjare väntar sig fyra platser här, så det är värt att lägga på minnet.

Datatypen efter As gäller för alla platser i arrayen. En array av typen String innehåller alltså bara text, och varje plats är en tom sträng tills du fyller den. En array av typen Long eller Double startar med 0 på alla platser.

Explicita gränser med To

Ofta är det tydligare att själv bestämma både nedre och övre gräns:

Dim tal(1 To 5) As Long

Arrayen har nu indexen 1 till 5, alltså fem platser, och ingen plats 0. Det passar bra när arrayen ska motsvara rader eller numrerade poster, där det känns naturligt att börja på 1. Du kan välja vilka gränser du vill, till exempel Dim år(2020 To 2025) As Double.

Storleken måste vara ett fast värde när du använder Dim på det här sättet. Du kan inte skriva en vanlig variabel inom parentesen, men det går bra med en konstant:

Const ANTAL As Long = 5

Dim namn(1 To ANTAL) As String

Då behöver du bara ändra värdet på ett ställe om storleken ändras. Läs mer om hur du deklarerar konstanter i Excel VBA. Behöver storleken bestämmas medan koden körs använder du i stället en dynamisk array med ReDim, som tas upp längre ner i artikeln.

Option Base 1

Skriver du Option Base 1 högst upp i modulen, före alla procedurer, börjar arrayer som saknar angiven nedre gräns på 1 i stället för 0. Då ger Dim namn(4) As String indexen 1–4, alltså fyra platser. Inställningen gäller bara den modul där den står. Därför är det oftast säkrare att skriva gränserna med To, så att koden betyder samma sak var den än hamnar.

Var arrayen kan användas

En array följer samma regler för räckvidd som andra variabler. Deklarerar du den med Dim inne i en procedur finns den bara där. Deklarerar du den överst i modulen kan alla procedurer i modulen använda den. Mer om detta finns i artikeln om variablers omfattning och livslängd.

Fylla och läsa värden i en array

När arrayen är deklarerad fyller du den på samma sätt som en vanlig variabel. Skillnaden är att du anger vilken plats värdet ska hamna på, inom parentes direkt efter namnet. Platsnumret kallas index.

Tilldela värden via index

Här skapas en array med fem platser (index 0–4) som fylls med fem namn:

Sub FyllArray()

Dim namn(4) As String

namn(0) = "Anna"

namn(1) = "Erik"

namn(2) = "Sara"

namn(3) = "Johan"

namn(4) = "Lisa"

End Sub

Varje rad skriver ett värde till en egen plats i arrayen. Om du skriver till samma index två gånger ersätts det gamla värdet, precis som när du tilldelar en vanlig variabel ett nytt värde.

Läsa ut värden med MsgBox och Debug.Print

Du läser ett värde genom att skriva arrayens namn och index. Lägg till de här raderna före End Sub i exemplet ovan:

MsgBox namn(2)

Debug.Print namn(0), namn(4)

MsgBox visar en dialogruta med texten Sara, eftersom index 2 är den tredje platsen. Debug.Print skriver i stället Anna och Lisa till direktfönstret (Immediate) i VBA-redigeraren. Visas inte fönstret öppnar du det med Ctrl+G. Debug.Print är ofta smidigare när du testar, eftersom du slipper klicka bort en dialogruta för varje värde.

Skriva värden från arrayen till celler

För att få ut innehållet i bladet tilldelar du cellernas Value ett värde ur arrayen:

Range("A1").Value = "Namn"

Range("A2").Value = namn(0)

Range("A3").Value = namn(1)

Range("A4").Value = namn(2)

Range("A5").Value = namn(3)

Range("A6").Value = namn(4)

I exemplet nedan ser du resultatet: en rubrik i A1 och de fem namnen i kolumnen under.

Excelblad där kolumn A har rubriken Namn i A1 och namnen Anna, Erik, Sara, Johan och Lisa i cellerna A2 till A6, utskrivna från en array med VBA.
Arrayens fem värden har skrivits ut i kolumn A.

Att skriva en rad per cell fungerar, men blir snabbt omständligt. I nästa avsnitt gör en loop samma jobb oavsett hur många värden arrayen har. Vill du skriva hela arrayen på en gång kan du också använda Range("A2:A6").Value = Application.Transpose(namn). En endimensionell array läggs annars ut som en rad, och Transpose vänder den så att värdena hamnar i en kolumn.

Loopa igenom en array

När arrayen innehåller fler än ett par värden vill du inte skriva en rad per index. Använd i stället en loop som går igenom platserna i tur och ordning. Det vanligaste är en For-loop där räknaren fungerar som index.

For-loop med LBound och UBound

Du kan skriva gränserna direkt i loopen, till exempel For i = 0 To 4. Då måste du ändra loopen varje gång arrayen får en annan storlek. Om du missar det får du antingen ett fel eller värden som aldrig behandlas. Låt hellre VBA ta fram gränserna åt dig:

  • LBound(array) ger arrayens lägsta index, till exempel 0 eller 1.
  • UBound(array) ger arrayens högsta index.

Med de två funktionerna fungerar loopen oavsett om arrayen börjar på 0 eller 1 och oavsett hur många platser den har.

Sub SummeraTal()
Dim tal(1 To 5) As Long
Dim i As Long
Dim summa As Long

tal(1) = 120
tal(2) = 85
tal(3) = 240
tal(4) = 60
tal(5) = 95

For i = LBound(tal) To UBound(tal)
summa = summa + tal(i)
Next i

Range(”D2”).Value = summa
End Sub

Loopen lägger ihop talen ett i taget i variabeln summa. När loopen är klar skrivs resultatet till cell D2, i det här fallet 600. Om du senare gör arrayen större, till exempel Dim tal(1 To 10) As Long, behöver du inte ändra något i själva loopen.

Räknaren i är deklarerad som Long. Det är ett bra standardval för index, eftersom Integer bara räcker upp till 32 767.

For Each som alternativ

Om du bara ska läsa värdena och inte behöver veta vilket index de har kan du använda For Each. Loopvariabeln måste då vara av typen Variant, även om arrayen innehåller tal eller text.

Sub SummeraMedForEach()
Dim tal(1 To 5) As Long
Dim varde As Variant
Dim summa As Long

tal(1) = 120
tal(2) = 85
tal(3) = 240
tal(4) = 60
tal(5) = 95

For Each varde In tal
summa = summa + varde
Next varde

Range(”D2”).Value = summa
End Sub

For Each ger kortare kod, men du har ingen tillgång till index. Variabeln varde är dessutom en kopia av elementet. Om du skriver varde = 0 inne i loopen ändras alltså inte innehållet i arrayen. Ska du ändra värden, eller behöver du index till exempel för att skriva till rätt rad, använder du den vanliga For-loopen med LBound och UBound.

Läsa in ett cellområde till en array

Det mest praktiska med en array i VBA är att du kan hämta ett helt cellområde i ett enda steg. I stället för att läsa cell för cell deklarerar du en variabel av typen Variant och tilldelar den områdets .Value:

Dim data As Variant

data = Range("A2:B6").Value

Efter den raden innehåller data alla tio värden från A2:B6. Två saker skiljer sig från arrayerna i de tidigare avsnitten:

  • Arrayen är tvådimensionell. Den första dimensionen är rader och den andra är kolumner. Du når ett värde med data(rad, kolumn). Det gäller även om du bara läser in en enda kolumn.
  • Index börjar på 1, inte 0. data(1, 1) är värdet från A2, alltså det första i området, och data(5, 2) är värdet från B6. Detta gäller oavsett Option Base.

Antalet rader får du med UBound(data, 1) och antalet kolumner med UBound(data, 2). Det andra argumentet anger vilken dimension du frågar efter.

Loopa över rader och kolumner

För att gå igenom varje värde använder du två loopar i varandra: den yttre går över raderna och den inre över kolumnerna.

Sub VisaOmrade()

Dim data As Variant

Dim r As Long, k As Long

data = Range("A2:B6").Value

For r = 1 To UBound(data, 1)

For k = 1 To UBound(data, 2)

Debug.Print data(r, k)

Next k

Next r

End Sub

Resultatet hamnar i direktfönstret, som du öppnar med Ctrl+G i VBA-redigeraren.

Bearbeta och skriva tillbaka

Du kan också gå åt andra hållet och skriva en hel array till bladet med en enda tilldelning. Ett enkelt sätt att få plats för resultatet är att läsa in en kolumn extra, här den tomma kolumnen C, fylla den i arrayen och sedan skriva tillbaka hela området:

Sub RaknaMedMoms()

Dim data As Variant

Dim r As Long

data = Range("A2:C6").Value

For r = 1 To UBound(data, 1)

data(r, 3) = data(r, 2) * 1.25

Next r

Range("A2:C6").Value = data

End Sub

Makrot läser namn och belopp, räknar fram beloppet inklusive 25 % moms i arrayen och skriver tillbaka allt på en gång. Exemplet nedan visar bladet efter körningen: kolumn A och B är källdata och kolumn C är det makrot har fyllt i.

Excelblad med namn i kolumn A och belopp i kolumn B på rad 2–6. Kolumn C, Inkl. moms, innehåller värden som makrot har skrivit tillbaka från arrayen, till exempel 1 500,00 för Anna.
Källområdet A2:B6 och kolumn C som makrot fyllt i från arrayen.

Bladet läses alltså en gång och skrivs en gång, oavsett hur många rader det är. Allt däremellan sker i minnet. Därför går det här mönstret betydligt snabbare än att läsa och skriva celler i en loop, särskilt när området är stort.

Tänk på att målområdet måste ha samma storlek som arrayen. Om du skriver till ett större område fylls de överskjutande cellerna med #N/A. Om området är mindre skrivs bara den del av arrayen som får plats.

Ändra storlek med ReDim och ReDim Preserve

Ibland vet du inte i förväg hur många värden arrayen ska rymma. Det kan bero på hur många rader som finns i bladet eller hur många värden som uppfyller ett villkor. Då använder du en dynamisk array. Den deklareras utan storlek och får sin storlek senare med ReDim.

Deklarera utan storlek och sätt storleken med ReDim

En dynamisk array deklareras med tomma parenteser:

Dim lista() As String

Arrayen har ännu inga platser. Om du försöker skriva till lista(1) nu får du felet ”Subscript out of range”. Först när du kör ReDim får den en storlek:

ReDim lista(1 To 10)

Du kan använda en variabel som storlek, till exempel ReDim lista(1 To antal). Det går inte med vanlig Dim, där storleken måste vara en konstant.

ReDim tömmer, ReDim Preserve behåller

Varje gång du kör ReDim skapas arrayen på nytt och alla gamla värden försvinner. Vill du behålla innehållet och bara göra arrayen större (eller mindre) lägger du till Preserve:

ReDim Preserve lista(1 To 11)

Med Preserve finns två begränsningar:

  • Du kan bara ändra den sista dimensionen i en flerdimensionell array. I Dim tabell() As Variant som fått storleken ReDim tabell(1 To 3, 1 To 5) fungerar ReDim Preserve tabell(1 To 3, 1 To 8). Försöker du ändra den första dimensionen till 1 To 6 får du körningsfel 9.
  • Du kan bara ändra den övre gränsen. Den nedre gränsen ska vara densamma som tidigare.

Om du krymper arrayen med Preserve försvinner värdena i de platser som tas bort.

Exempel: samla in värden som uppfyller ett villkor

Anta att kolumn A innehåller namn och kolumn B belopp på rad 2–6. Makrot nedan samlar in namnen på alla som har ett belopp över 1 000. Arrayen växer med en plats för varje träff:

Sub SamlaStoraBelopp()
Dim lista() As String
Dim antal As Long, rad As Long, i As Long
For rad = 2 To 6
If Cells(rad, 2).Value > 1000 Then
antal = antal + 1
ReDim Preserve lista(1 To antal)
lista(antal) = Cells(rad, 1).Value
End If
Next rad
If antal = 0 Then
MsgBox "Inga belopp över 1 000."
Else
For i = LBound(lista) To UBound(lista)
Debug.Print lista(i)
Next i
End If
End Sub

Variabeln antal håller reda på hur många träffar som hittats. Varje gång villkoret uppfylls blir arrayen en plats större med ReDim Preserve, och namnet läggs in på den nya sista platsen. Utan Preserve skulle bara det sista namnet finnas kvar.

Kontrollen If antal = 0 behövs. Om ingen rad uppfyller villkoret har arrayen aldrig fått någon storlek, och då ger LBound och UBound felet ”Subscript out of range”.

Tips: ReDim Preserve kopierar hela arrayen varje gång den körs. För några hundra värden märks det inte. Vid många tusen rader går det fortare att först dimensionera arrayen till största möjliga storlek, till exempel antalet rader. Fyll sedan på den och krymp den en enda gång med ReDim Preserve lista(1 To antal) när loopen är klar.

Vanliga misstag med arrayer

De flesta fel med arrayer handlar om index och storlek. Här är de vanligaste fällorna och hur du undviker dem.

  • Du glömmer att index börjar på 0. Dim namn(4) As String ger fem platser, från namn(0) till namn(4), inte fyra platser som börjar på 1. Om du fyller arrayen från index 1 blir första platsen tom och du tappar lätt bort ett värde. Skriv gränserna explicit, till exempel Dim namn(1 To 5) As String, när du vill räkna från 1. Då syns det direkt i koden.
  • Körningsfel 9: ”Subscript out of range”. Felet uppstår när du använder ett index som ligger utanför arrayens gränser, till exempel namn(5) i en array som slutar på 4. Det kommer också om du anropar UBound på en dynamisk array som ännu inte fått någon storlek med ReDim. Använd LBound och UBound i dina loopar i stället för hårdkodade tal, och se till att en dynamisk array har fått en storlek innan du läser från den.
  • ReDim utan Preserve raderar dina data. Ett vanligt ReDim lista(10) ger arrayen en ny storlek men tömmer samtidigt alla befintliga värden. Vill du behålla det som redan ligger i arrayen måste du skriva ReDim Preserve lista(10).
  • Preserve på fel dimension. Med ReDim Preserve kan du bara ändra den övre gränsen i den sista dimensionen. I en array som data(1 To 10, 1 To 3) går det alltså att ändra 3, men inte 10. Försöker du ändra den första dimensionen får du körningsfel 9. Planera därför arrayen så att det som ska växa ligger i sista dimensionen, eller bygg en ny array och kopiera över värdena.
  • Du antar att Range.Value ger en endimensionell array. data = Range("A2:A6").Value ger en tvådimensionell array, även om området bara har en kolumn. Värdena nås då med data(rad, 1) och inte med data(rad), och båda index börjar på 1. Tänk också på att ett område med en enda cell inte ger någon array alls, utan bara ett enskilt värde.
  • Du blandar ihop storleken och det högsta indexet. UBound ger det högsta indexet, inte antalet element. För en array som börjar på 0 är antalet UBound(arr) - LBound(arr) + 1. Räkna alltid ut antalet på det sättet, så fungerar koden oavsett var arrayen börjar.

Sammanfattning och nästa steg

En array låter dig lagra många värden i en och samma variabel och arbeta med dem via index. Det här är de viktigaste punkterna att ta med sig:

  • Dim namn(4) As String ger fem platser med index 0–4. Vill du börja på 1 skriver du gränserna själv, till exempel Dim tal(1 To 5) As Long.
  • Du fyller och läser en array med index, till exempel namn(0) = "Anna".
  • Loopa med For i = LBound(arr) To UBound(arr) i stället för hårdkodade gränser, så fungerar koden oavsett storlek.
  • data = Range("A2:B6").Value ger en tvådimensionell array som börjar på index 1, med rader först och kolumner sedan. Det går betydligt snabbare än att läsa cell för cell.
  • Dynamiska arrayer deklareras utan storlek och får sin storlek med ReDim. Använd ReDim Preserve när befintliga värden ska vara kvar. Tänk på att bara den sista dimensionen kan ändras.
  • Körningsfel 9, ”Subscript out of range”, betyder nästan alltid att du försöker nå ett index utanför arrayens gränser.

Behöver du repetera grunderna kan du läsa om hur du deklarerar variabler i Excel VBA, med datatyper och Option Explicit. Om du vill veta var i koden en array kan nås och hur länge den behåller sina värden hittar du svaret i artikeln om variablers omfattning och livslängd. Har du värden som aldrig ändras, till exempel en momssats eller ett fast antal rader, passar det ofta bättre att deklarera konstanter i Excel VBA.