Array variabler i Excel 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.
Men lad os for en stund koncentrere os om det statiske array.
Statisk array
Et statisk array deklareres ganske enkelt ved f.eks. at skrive:
Dim MitArray(100) As Integer
Hvor man siger, at det indeholder 100 elementer af datatypen Integer (heltal). Eller:
Dim MitArray(1 To 100) As String
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.
Dim MitArray(2000 To 2010) As Integer
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()
Dim MitArray(1 To 100) As String
Dim iCount As Integer
For iCount = 1 To 100
MitArray(iCount) = "Nummer " & iCount + 1
Next
MsgBox MitArray(100)
For iCount = 1 To 100
Range("A1").Offset(iCount - 1, 0).Value = MitArray(iCount)
Next
End Sub
TIP!
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()
Dim MitArray(1 To 10, 1 To 2) As Integer
Dim iCount As Integer
For iCount = 1 To 10
MitArray(iCount, 1) = iCount
MitArray(iCount, 2) = iCount + 1
Next
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()
Dim MitArray(1 To 10, 1 To 3) As Variant
Dim rRange As Range
Dim iCount As Integer
Dim iCount2 As Integer
Set rRange = Range("A1:A10")
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
Set rRange = Range("A12:A22")
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.
Et dynamisk array kan deklareres således:
Dim DynArray() As Variant
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.
eller
ReDim DynArray(1 To lCount)
hvor lCount er en talvariabel af datatypen Long.
Man kan også sige noget om størrelsen, når man deklarerer variablen og i stedet for
Dim DynArray() As Variant
skrive
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.
Hvis du f.eks. har et array som i runtime er blevet sat fra 1 til 99, ligesom:
ReDim Preserve DynArray(1 To 99)
Vil
UBound(DynArray)
returnere 99, og
LBound(DynArray)
returnere 1.
Hvis arrayet har flere dimensioner svarende til f.eks.
ReDim Preserve DynArray(1 To 100, 5 To 10)
Vil flg.
UBound(DynArray, 1)
UBound(DynArray, 2)
LBound(DynArray, 1)
LBound(DynArray, 2)
returnere 100, 10, 1 og 5, idet 1 og 2 peger på hver sin dimension.
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:
MitRange.Value = MitArray
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
Set rRange = Range("A1:N30")
With rRange
ReDim MitArray(1 To .Rows.Count, 1 To .Columns.Count)
End With
Application.ScreenUpdating = False
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
rRange.Value = MitArray
BeforeExit:
Set rRange = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub
Det var så lidt om arrays. Beslægtede emner er Collections og Class Collections.
Mere information - og mere avanceret information - om arrays kan f.eks. findes hos Pearson eller Microsoft.
Relaterede sider:
|