Excel VBA: Hvordan man sætter fart på sine makroer

Med små makroer betyder hastigheden ikke noget - de afvikles på et splitsekund, men efterhånden som ens programmer vokser sig større og større, bliver det vigtigere, at koden afvikles hurtigt og effektivt. Man kan gøre flere ting for at sætte fart på, og noget af det mest effektive kan være at slå opdatering af skærmen og regnearket fra, mens koden afvikles.

Skærmopdatering

Hvis makroen skriver til celler i regnearket og/eller skifter mellem faneblade, kan man opleve, at skærmen ligefrem "skvulper". Der bruges rigtig mange ressourcer på denne skærmopdatering, og meget ofte kan man få betragtelig hastighedsforøgelse ved at slå opdateringen fra, når makroen starter, og slå den til, når programmet er afviklet. Det gøres således:

Application.ScreenUpdating = False 'Slå fra

Application.ScreenUpdating = True 'Slå til

Det er irriterende, hvis programmet støder på en fejl og går ned, før det slår skærmopdateringen til igen. Det er derfor fornuftigt at bruge fejlbehandling, som sikrer, at skærmopdateringen slås til uanset hvad. Det kan f.eks. gøres sådan:

Sub EtEllerAndet()

On Error GoTo ErrorHandle

Application.ScreenUpdating = False

'Aktion

BeforeExit:
Application.ScreenUpdating = True

Exit Sub
'Her havner vi ved programfejl
ErrorHandle:
MsgBox Err.Description & " Sub EtEllerAndet"
Resume BeforeExit 'Dirigerer tilbage til BeforeExit
End Sub

Ved normal afvikling slås skærmopdateringen til, lige før kommandoen "Exit Sub". Hvis man ikke har fejlbehandling (som her), vil programmet gå ned, hvis der sker en fejl.

Her har vi skrevet "On Error GoTo ErrorHandle", så hvis der sker en fejl, vil programmet gå til mærkaten "ErrorHandle". Bemærk så, at der efter fejlbehandlingen står "Resume BeforeExit". Det vil få programmet til at hoppe til mærkaten "BeforeExit", og skærmopdateringen bliver slået til, før programmet afsluttes.

Genberegning

Normalt genberegner Excel, hvis man ændrer på en celleværdi, som har betydning for andre celleværdier. Det kan også være en ganske alvorlig tidsrøver ved større applikationer. Som med skærmopdatering kan man slå genberegning fra og til:

Application.Calculation = xlCalculationManual 'Slå fra

Application.Calculation = xlCalculationAutomatic 'Slå til

Du skal dog huske, at når genberegning er slået fra, opdaterer Excel ikke celleværdier. Så hvis din makro skal bruge opdaterede celleværdier, er det nødvendigt at gennemtvinge en genberegning, enten for et specifikt område (Range("MitRange").Calculate) eller hele regnearket (Calculate).

Økonomisk kode

Meget ofte kan man sætte turbo på koden med små midler. Her følger nogle få tips.

Brug Range objekter i stedet for "Selection"

Især hvis man bruger indspillede makroer, er der ofte mulighed for at forbedre hastigheden væsentligt ved at rette koden lidt til. Indspillede makroer bruger nemlig i vid udstrækning at vælge cellerne, man markerer, og opererer så med denne Selection. Det kan se således ud:

Range("A1:A6").Select
Selection.Font.Bold = True

Range("A1:A6").Select
Selection.ClearContents

Det er meget hurtigere at operere direkte på sit Range uden at vælge det. Således:

Range("A1:A6").Font.Bold = True

Range("A1:A6").ClearContents

Når man indspiller en makro, står der også tit en masse, som er helt overflødigt, såsom at teksten ikke er understreget, gennemstreget og hvad ved jeg. Det kan man som regel roligt slette.

With...End With

Hvis man skal operere flere gange på et objekt (en celle f.eks.), kan det spare en del tid at operere direkte på objektet ved at bruge With...End With konstruktionen. Således:

With Worksheets(1).Range("A1").Font
   .Italic = True
   .Underline = xlUnderlineStyleSingle
   .ColorIndex = 3
End With

Worksheets(1).Range("A1").Font.Italic = True
Worksheets(1).Range("A1").Font.Underline = _ 
xlUnderlineStyleSingle
Worksheets(1).Range("A1").Font.ColorIndex = 3

Select Case i stedet for If Then

Hvis det næste skridt afhænger af, om en variabel er det ene eller det andet, er det hurtigere at bruge en "Select Case" konstruktion i stedet for en stribe af If...Then sætninger. For eksempel således:

Select Case vValue
   Case Is = 0
      'Aktion
   Case Is > 0 < 10
      'Aktion
   Case Is >= 10
      'Aktion
   Case Else
      'Aktion
End Select

Regnearksfunktioner

Det kan være sjovt at skrive sine egne funktioner, men hvis en af Excels indbyggede regnearksfunktioner kan løse opgaven, er det altid meget hurtigere. For eksempel er følgende meget hurtigere end at lave sin egen funktion til at finde gennemsnittet.

Snit = Application.WorksheetFunction.Average(Range("A1:A6"))

For Each løkker

Hvis man skal gennemløbe collections (som f.eks. Worksheets eller Ranges), er det hurtigste at bruge en For Each konstruktion. Således er den første løkke herunder hurtigere end den næste:

Dim rCell as range
Dim rRange as Range

Set rRange = Range("A1:A10")
For Each rCell in rRange
   MsgBox rCell.Value
Next

'******************

Dim i as Integer
Dim rRange as Range

Set rRange = Range("A1:A10")
For i = 1 To rRange.Count
   MsgBox rRange.Item(i).Value
Next i

Sæt fart på dine makroer i Excel