Hvordan man bruger array variabler i Excel makroer (VBA)

Et array kan bedst sammenlignes med en tabel, som indeholder et antal elementer af samme datatype.

Ved at bruge et array som variabel kan man slippe for at deklarere en hulens bunke variabler. Man samler dem i stedet på ét sted, i sit array, og så kan man slå op i "tabellen", når man skal læse eller skrive en værdi.

Hvert element i et array har et indeksnummer, og så kan man "slå det op" ved f.eks. at referere til indeksnummer 117. Man kan også gennemløbe arrays med en løkke.

En vigtig ting at huske er, at det første element som standard har 0 (nul) som indeksnummer, og i så fald er element nr. 99 faktisk nummer 100. Synes man, det er forvirrende, kan man øverst i VBA-modulet skrive:

Så sættes standard til 1 i stedet for nul, men man kan også gøre det, når man deklarerer sin array-variabel (se senere), og det anbefales varmt.

Der er to typer arrays: de statiske og de dynamiske. Forskellen er den enkle, at med et statisk array ved man på forhånd, hvor mange elementer det skal indeholde, og så er det låst, mens et dynamisk array kan gøres større eller mindre undervejs.

Det dynamiske array kommer jeg ind på senere, og sidst i artiklen forklarer jeg begreberne UBound og LBound. Jeg viser også, hvordan man lynhurtigt kan kopiere indholdet af et array til et område (range) i regnearket.

Eksemplerne på denne side kan du kopiere ved at markere teksten med musen, trykke CTRL + C og sætte den ind i et VBA-modul i Excel.

Statisk array

Hvor man siger, at det indeholder 100 elementer af datatypen Integer (heltal). Eller:

Hvor man specificerer, at arrayets første element har nummer 1. Det er god programmeringsskik, og så undgår man fejl, hvis man f.eks. genbruger koden senere. Der er ikke noget i vejen for at skrive f.eks.

Det kunne f.eks. være praktisk, hvis arrayet indeholder data for årstallene 2000 til 2010. Her følger et lille eksempel på brug af et statisk array.

Sub ArrayTest()
'Vores array med 100 tekstelementer.
Dim MitArray(1 To 100) As String
Dim iCount As Integer            'Tæller

'Vi fylder tekststrenge i arrayets elementer
For iCount = 1 To 100
   MitArray(iCount) = "Nummer " & iCount + 1
Next

'Element nr. 100 har teksten "Nummer 101",
fordi vi lagde 1 til iCount, da vi konstruerede teksten.
MsgBox MitArray(100)

'Vi skriver nu arrayets elementer til kolonne
'A i det aktive ark.
For iCount = 1 To 100
   Range("A1").Offset(iCount - 1, 0).Value = MitArray(iCount)
Next

End Sub

Hvis arrayet skal indeholde elementer med forskellige datatyper, deklarerer man det bare som af datatypen Variant vel vidende, at datatypen Variant fylder mere i hukommelsen end f.eks. Integer.

Arrays med flere dimensioner

Se, hvis det bare var et endimensionelt array (tabel), der var brug for, ville jeg som regel foretrække at bruge en Collection, fordi jeg synes, at collections er enklere og nemmere at have med at gøre.

Men i modsætning til collections kan arrays have flere dimensioner, og det er smart! Her er et simpelt eksempel på et todimensionelt array:

Sub TodimensioneltArray()
'Et todimensionelt array deklareres.
'Du kan sammenligne det med 10 rækker og 2 kolonner.
Dim MitArray(1 To 10, 1 To 2) As Integer
Dim iCount As Integer 'Tæller

'Arrayet udfyldes
For iCount = 1 To 10
   MitArray(iCount, 1) = iCount
   MitArray(iCount, 2) = iCount + 1
Next

'Vi gennemløber arrayet og skriver værdierne
'til kolonne A og B i det aktive ark.
'Værdierne 1 til 10 vil stå i kolonne A,
'og i kolonne B vil det være 2 til 11.
For iCount = 1 To 10
   Range("A1").Offset(iCount - 1, 0).Value = MitArray(iCount, 1)
   Range("B1").Offset(iCount - 1, 0).Value = MitArray(iCount, 2)
Next

End Sub

Ovenstående kunne også være personnavne og telefonnumre, og hvis man også ville have adresser med, kunne man deklarere "1 To 3" i stedet for "1 To 2".

Det kunne f.eks. gøres således, men før du kopierer makroen ind i et modul og kører den, bør du skrive 10 navne i celle A1 til A10, 10 tal ("telefonnumre") i B1 til B10 og 10 et-eller-andet i celle C1 til C10.

Sub TodimArray()
'Arrayet deklareres som Variant, så det kan
'indeholde forskellige datatyper som f.eks. tal og tekst.
Dim MitArray(1 To 10, 1 To 3) As Variant
Dim rRange As Range
Dim iCount As Integer
Dim iCount2 As Integer

'Vi sætter vores range til celle A1:A10
Set rRange = Range("A1:A10")

'Værdier indlæses fra celle A1:A10 og til højre.
For iCount = 1 To 10
   With rRange.Item(iCount)
      MitArray(iCount, 1) = .Value
      MitArray(iCount, 2) = .Offset(0, 1).Value
      MitArray(iCount, 3) = .Offset(0, 2).Value
   End With
Next

'Vi ændrer vores range til A12:A22, før vi skriver.
Set rRange = Range("A12:A22")

'Nu skriver vi værdierne tilbage til regnearket,
'men i omvendt rækkefølge, idet vi gennemløber
'arrayet bagfra.
For iCount = 10 To 1 Step -1
   iCount2 = iCount2 + 1
   With rRange.Item(iCount2)
      .Value = MitArray(iCount, 1)
      .Offset(0, 1).Value = MitArray(iCount, 2)
      .Offset(0, 2).Value = MitArray(iCount, 3)
   End With
Next

Set rRange = Nothing
End Sub

Man kan bygge flere dimensioner på som kinesiske æsker. Et tredimensionelt array kan visualiseres som en terning, men ved endnu flere dimensioner bliver der nok svært at visualisere! Hver ny dimension er blot et array i arrayet eller hierarkisk "knopskydning".

Forestil dig de første elementer som "træstammer". Hver træstamme kan have flere "grene". Hver gren kan have flere "sidegrene". Hver sidegren kan have flere "kviste". Hver kvist kan have flere "blade". Hvert blad kan have flere "ribber" osv. osv.

Dynamiske arrays

Hvis man ikke ved på forhånd, hvor mange elementer der skal være i ens array, kan man deklarere et dynamisk array, som man løbende kan ændre størrelsen på, alt efter om man tilføjer eller fjerner elementer.

Dim DynArray() As Variant '(eller Integer, String osv.)

Når man så når til det punkt i koden, hvor man skal have værdier ind i sit array, skal man give det en størrelse, og det gør man med "ReDim". F.eks.

ReDim DynArray(1 To 100)

ReDim DynArray(1 To lCount)

Man kan også sige noget om størrelsen, når man deklarerer variablen og i stedet for

Dim DynArray() As Variant

ReDim DynArray(1 To 100) As Variant

Man kan redimensionere sit dynamiske array når som helst med ReDim kommandoen, men hvis man ikke bruger ordet "Preserve" i den forbindelse, slettes arrayets indhold.

Lad os sige, at du har et array med 100 elementer, og du nu ønsker at udvide det til 200. Hvis du ønsker at bevare indholdet i de første 100 elementer, skal du skrive:

ReDim Preserve DynArray(1 To 200)

LBound og UBound

Når man arbejder med dynamiske arrays, ved man ikke nødvendigvis, hvor mange elementer de indeholder - de kan være dimensioneret i runtime af en eller flere variabler.

I den situation kan man (f.eks. ved gennemløb) bruge LBound til at finde "bundlinien" og UBound til at finde øvre grænse.

UBound(DynArray, 1)
UBound(DynArray, 2)
LBound(DynArray, 1)
LBound(DynArray, 2)

Hurtig kopiering af et Array til et Range

I nogle af eksemplerne ovenfor har jeg vist, hvordan man kan udfylde et område (et range) i regnearket med indholdet i et array. Det har jeg gjort ved at gennemløbe arrayet med en løkke og så udfylde cellerne én for én.

Det virker også fint med små arrays, men snakker vi om større datamængder, er det en langsom metode, og der er en smartere måde, som kan kopiere arrayet mange gange hurtigere.

Den demonstrerer jeg i proceduren nedenfor. Hele humlen er at definere et range og et array som har de samme dimensioner - altså antal rækker og kolonner. Når arrayet er fyldt ud, kopierer man det til sit range i et snuptag ved at skrive:

Sådan! Her følger et eksempel på en procedure, som gør netop det. Da et range med flere kolonner i sagens natur er todimensionelt, skal vores array også være todimensionelt.

Sub ArrayTilRange()
Dim MitArray() As Integer
Dim rRange As Range
Dim iTal As Integer
Dim iCount As Integer
Dim iCount2 As Integer

'Vi sætter vores range til celle A1 til N30
Set rRange = Range("A1:N30")

'Vores array dimensioneres nu til samme
'størrelse som rRange, dvs. 30 rækker og
'14 kolonner i dette tilfælde.
'Hvis det var arrayet, som skulle definere
'størrelsen på vore range, måtte vi i sagens
'natur gøre det omvendt: finde LBound og
'UBound for vores array og så definere vores
'range på den baggrund.
With rRange
   ReDim MitArray(1 To .Rows.Count, 1 To .Columns.Count)
End With

'Slår skærmopdatering fra for at opnå højere hastighed
Application.ScreenUpdating = False

'Nu udfylder vi vores array
iTal = 0
For iCount = 1 To rRange.Rows.Count
   For iCount2 = 1 To rRange.Columns.Count
      MitArray(iCount, iCount2) = iTal + 1
      iTal = iTal + 1
   Next
Next

'Overfør MitArray til rRange
rRange.Value = MitArray

BeforeExit:
Set rRange = Nothing
'Slår skærmopdatering til igen
Application.ScreenUpdating = True

Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Mere information - og mere avanceret information - om arrays kan f.eks. findes hos Pearson eller Microsoft.

Array variabler i Excel VBA

Statisk array

Arrays med flere dimensioner

Dynamiske arrays

LBound og UBound

Hurtig kopiering af et Array til et Range