Arrays og range-formler i Excel makroer (VBA)

På siden Arrays og ranges har jeg beskrevet, hvordan man kan få betragtelige hastighedsforøgelser ved at kopiere et range til et array og så manipulere data i arrayet, før arrayet indsættes som en tabel i ét hug.

Men den metode kopierer kun værdierne, ikke formlerne. Hvad gør man, hvis man skal have formlerne med, fordi de skal indsættes et andet sted? Man skriver:

Det benyttede jeg mig af, da jeg forleden skulle sammenstykke en ny tabel, hvor cellerne skulle beholde de originale cellereferencer.

For det første går det lynhurtigt ved at bruge et array som tabel, og til sammenligning med andre kopieringsmetoder skal man ikke bekymre sig om, hvorvidt den nye celle stadig peger på den originale eller ej.

Du ved, hvis du fx kopierer B1 til D2, og celle B1 har formlen "= A1", ja så vil formlen i D2 blive "= C2", medmindre der er brugt dollartegn: "= $A$1".

Det følgende lille kodeeksempel illustrerer forskellen på at kopiere med ".Value" og ".Formula". Du kan markere koden med musen, kopiere (CTRL+C) og sætte ind i et VBA-modul med CTRL+V.

Eksemplet forudsætter, at der står noget i cellerne A1:A10, og at celle B1:B10 har formlen "=A1", "=A2" osv. nedefter.

Først kopieres B1:B10 ind i et array med brugen af ".Value". Værdierne kopieres i omvendt rækkefølge til et nyt array, som indsættes i celle E2:E11. Dernæst gentages øvelsen med brug af ".Formula" og indsættes i G2:G11.

Du vil se, at med ".Value" er det kun værdierne og ikke formlerne, der kopieres.

Sub FormlerTilArray()
'Illustrerer forskellen på at kopiere værdier
'og formler fra et range til et array.
Dim vInArray()          'Input-array
Dim vOutArray()         'Output-array
Dim rMyRange As Range   'Rangevariabel
Dim lCount As Long      'Tæller
Dim lRow As Long        'Tæller

On Error GoTo ErrorHandle

'Sætter ranget til området B1:B10
Set rMyRange = Range("B1:B10")

'Kopierer værdierne til arrayet, der
'automatisk får samme dimensioner
'som ranget. Arrays som laves på denne
'måde har altid 1 som base. Dvs. at det
'første element adresseres vInArray(1,X)
'og ikke vInArray(0,X)
vInArray() = rMyRange.Value

'Dimensionerer det andet array
ReDim vOutArray(1 To UBound(vInArray), 1 To 1)

'Gennemløber det første array bagfra og
'indsætter værdierne i det andet array i omvendt rækkefølge.
For lCount = UBound(vInArray) To 1 Step -1
   lRow = lRow + 1
   vOutArray(lRow, 1) = vInArray(lCount, 1)
Next

'Kopierer ranget igen, men denne gang med formler:
vInArray() = rMyRange.Formula

'Redefinerer rMyRange til området E2:E11
Set rMyRange = Range("E2:E11")

'Indsætter værdierne i det nye range
rMyRange.Value = vOutArray()

'Nulstiller tælleren lRow
lRow = 0

'Kopierer formlerne til vOutArray i omvendt rækkefølge.
For lCount = UBound(vInArray) To 1 Step -1
   lRow = lRow + 1
   vOutArray(lRow, 1) = vInArray(lCount, 1)
Next

'Redefinerer rMyRange til området G2:G11
Set rMyRange = Range("G2:G11")

'Indkopierer arrayet
rMyRange.Value = vOutArray

'Indsætter lige et par overskrifter:
Range("E1").Value = "Værdier"
Range("G1").Value = "Formler"

BeforeExit:
On Error Resume Next
Set rMyRange = Nothing
Erase vInArray
Erase vOutArray
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Til mindre opgaver kan denne metode være overkill, men den er virkelig smart, hvis der fx skal håndplukkes i store datamængder for at lave en ny tabel, som skal indsættes et andet sted med de originale formler og cellereferencer.

Arrays og range-formler i Excel VBA