Brug af regnearksfunktioner på VBA arrays

Med VBA makroer og arrays kan man manipulere selv store datamængder lynhurtigt, fordi der er så lille (eller intet) overhead i at læse/skrive til selve regnearket.

Netop derfor bruger jeg ofte arrays, når jeg skal udtrække data, som opfylder visse betingelser. Jeg indlæser rådata i et array, gennemløber det og kopierer det, der opfylder betingelserne, til et andet array.

Og så er det særdeles bekvemt, at jeg kan bruge Excels indbyggede regnearksfunktioner i analysen af de udtrukne data uden at skulle skrive dem til regnearket først.

Eksemplerne på denne side er ret simple og tjener mest til at demonstrere syntaksen, men du kan downloade et eksempel, som er noget mere avanceret.

Filen til download indeholder to regneark: Ét med makroer og konfiguration for dataudtræk og et andet med data. Skulle det have interesse, er der også et eksempel på en Userform med en ListBox samt en filåbn-dialog. Kommentarer til koden er på engelsk - jeg gider ikke lave to versioner.

Syntaksen for at bruge regnearksfunktioner på arrays er ret simpel. Tag for eksempel, at du har et array kaldet "arMatrix" og to variable kaldet "dMiddel" og "dStdAfv," får du arrayets middelværdi og standardafvigelse således:

With Application.WorksheetFunction
   dMiddel = .Average(arMatrix)
   dStdAfv = .StDev(arMatrix)
End With

Bemærk, at VBA "snakker engelsk," dvs. bruger de engelske funktionsnavne. Kender du ikke dem, kan du hurtigt finde en oversigt på nettet - søg fx på "regnearksfunktioner dansk engelsk".

dMiddel = Application.WorksheetFunction.Average(arMatrix)
dStdAfv = Application.WorksheetFunction.StDev(arMatrix)

men "With - End With" konstruktionen er hurtigere, når man adresserer noget i et hierarki mere end én gang.

Hvis du kopierer værdier fra et range til et array i én hurtig operation (som beskrevet på Arrays og ranges), skal arrayet være af datatypen Variant, men regnearksfunktioner er ligeglade med, om arrayet er deklareret som fx Long eller Double.

Som i dette eksempel, der ikke tjener til megen nytte udover at vise, hvordan man fylder et array med 100 rækker og 100 kolonner og så bruger regnearksfunktioner på arrayet.

Sub Demo()
Dim lCol As Long                          'Tæller
Dim lRow As Long                          'Tæller
Dim arMatrix(1 To 100, 1 To 100) As Long  'Array
Dim rTable As Range                       'Range

For lRow = 1 To 100
   For lCol = 1 To 100
      arMatrix(lRow, lCol) = lCol
   Next
Next

'Brug nogle regnearksfunktioner og vis resultaterne
With Application.WorksheetFunction
   MsgBox "Middel " & .Average(arMatrix)
   'Tallet formateres til 2 decimaler
   MsgBox "Standardafvigelse " & Format(.StDevP(arMatrix), "#0.00")
End With

'Sæt ranget rTable til samme dimensioner som arrayet
Set rTable = Range(Range("A1"), Range("A1").Offset(99, 99))

'Kopierer arrayet til rTable i ét hug
rTable.Value = arMatrix

'Spar hukommelse
Set rTable = Nothing
Erase arMatrix

End Sub

På mit arbejde kan jeg få logfiler med 17280 rækker (en værdi hvert 5. sekund) og X procesværdier (i kolonner) for hvert døgn. At analysere så store datamængder manuelt ville tage lang tid, men med makroer gøres det på få sekunder.

Der er et forenklet eksempel i de regneark, du kan downloade, og for at gøre en lang historie kort, kopierer jeg procesværdierne til arrays, løber dem igennem med en løkke og kopierer de værdier, som opfylder visse kriterier (fx "normal produktionsniveau"), til et nyt array.

Derefter bruger jeg bl.a. regnearksfunktioner til analyse af de udtrukne data. En meget brugbar parameter til at vurdere processtabilitet er fx antallet af "afstikkere" defineret som værdier, der er større eller mindre end gennemsnittet +/- 3 gange standardafvigelsen.

Er der ingen "afstikkere," har man med stor sandsynlighed en proces, der er under fuld kontrol.

Når man skal lave den slags, er det praktisk at have makroerne i et separat regneark (eller et tilføjelsesprogram), så man ikke skal kopiere data eller makroer. De regneark, du kan downloade, viser, hvordan man kan gøre det.

På arbejde lader jeg en makro kopiere resultaterne til et tredje regneark. Det viser eksemplerne ikke, her popper de bare op i en MsgBox, men du kan designe det efter dit behov.

For at begrænse størrelsen på download-filen er der ikke mange data i datafilen, men du kan nemt tilføje ved at kopiere.

Der er lige én ting, man skal være opmærksom på med regnearksfunktioner: Nyere versioner af Excel har funktioner, som ikke understøttes af ældre versioner (som fx 2003), men jeg tror, at de fleste har en tilsvarende funktion, som er bagud kompatibel.

Regnearksfunktioner og arrays