10 måder at fremskynde dine makroer

Undgå overdreven reference

Når du kalder en metode eller egenskab for et objekt, skal den gå gennem OLE-komponentens IDispatch-grænseflade. Opkaldene til disse OLE-komponenter tager tid, så en reduktion af antallet af referencer til OLE-komponenter kan forbedre makrokodens hastighed.

 Til påkaldelse af objektegenskaber eller -metoder bruges repræsentationsmetoden for  Object.Method generelt, det vil sige "." symbol bruges til at påberåbe egenskaber og metoder.

Derfor kan antallet af metode- eller egenskabskald bedømmes efter antallet af symboler ".". Jo mindre "." symbol, jo hurtigere kører koden.

For eksempel indeholder følgende sætning 3 symboler ".".

ThisWorkbook.Sheet1.Range("A1").Value = 100

Følgende sætning har kun ét symbol ".".

Activewindow.Top = 100

Her er nogle tricks til at reducere antallet af symboler "." at løbe hurtigere.

For det første, når du skal henvise til det samme objekt gentagne gange, kan du indstille objektet til en variabel for at reducere antallet af opkald. For eksempel kræver følgende kode to opkald pr. linje.

ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300

Fordi  Sheets("Sheet1")  -objektet skal refereres gentagne gange, kan det sættes til en variabel  sht  først, så hver kode kun skal kaldes én gang.

Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300

For det andet, hvis du ikke ønsker at erklære en midlertidig variabel sht, kan du også bruge  With-  sætningen nævnt tidligere. Som vist i følgende eksempel:

With ThisWorkbook.Sheets("Sheet1")
    .Cells(1, 1) = 100
    .Cells(2, 1) = 200
    .Cells(3, 1) = 300
End With

For det tredje,  når der er mange løkker, så prøv at holde egenskaber og metoder uden for løkken.  Når du genbruger en egenskabsværdi for det samme objekt i en løkke, kan du først tildele egenskabsværdien til en specificeret variabel uden for løkken og derefter bruge variablen i løkken, som kan opnå hurtigere hastighed. Som vist i følgende eksempel:

For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = Cells(1, 2).Value
Next i

Hver sløjfe i dette eksempel får egenskaben Værdi for cellen Cells(1,2). Hvis du tildeler egenskaben Værdi for Celler(1.2) til en variabel, før løkken starter, vil du få en hurtigere kørsel. Som vist i følgende eksempel:

tmp = Cells(1, 2).Value
For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = tmp
Next i

Ovenstående kode kalder  ThisWorkbook.Sheets("Sheet1")  hver gang den looper. Du kan gøre dette hurtigere ved at bruge  With  -sætningen til at flytte opkaldet til  ThisWorkbook.Sheets("Sheet1")  uden for løkken. Som vist i følgende eksempel:

tmp = Cells(1, 2).Value
With ThisWorkbook.Sheets("Sheet1")
    For i = 1 To 1000
        .Cells(1, 1) = tmp
        .Cells(2, 1) = tmp
        .Cells(3, 1) = tmp
    Next i
End With

Undgå at bruge varianttyper

Begyndere foretrækker normalt at bruge Variant-variabler, som har den fordel, at de er mindre komplicerede, fordi enhver type data kan bruges uden problemet med hukommelsesoverløb, hvis dataene er for store til datatyperne Integer eller Long. Varienmt-typedata kræver dog mere ekstra hukommelsesplads end de andre specificerede typer (2 bytes for heltaldata, 4 bytes for lange data og 16 bytes for variantdata), VBA kræver mere tid at behandle data af varianttypen end andre specificerede typer af data. Som det følgende eksempel viser.

Sub VariantTest()
    Dim i As Long
    Dim ix As Integer, iy As Integer, iz As Integer
    Dim vx As Variant, vy As Variant, vz As Variant
    Dim tm As Date
    vx = 100: vy = 50
    tm = Timer
    For i = 1 To 1000000
        vz = vx * vy
        vz = vx + vy
        vz = vx - vy
        vz = vx / vy
    Next i
    Debug.Print "Variant types take " & Format((Timer - tm), "0.00000") & " seconds"
    ix = 100: iy = 50
    tm = Timer
    For i = 1 To 1000000
        iz = ix * iy
        iz = ix + iy
        iz = ix - iy
        iz = ix / iy
    Next i
    Debug.Print "Integer types take " & Format((Timer - tm), "0.00000") & " seconds"
End Sub

I ovenstående kode udfører linje 8 til 13 1 million operationer med addition, subtraktion, multiplikation og division af Variantvariabler, og linje 17 til 22 udfører 1 million operationer med addition, subtraktion, multiplikation og division af heltalsvariable. På min computer tog driften af ​​Variant-variablen omkring  0,09375  sekunder, mens operationen af ​​Integer-variablen tog omkring  0,03125  sekunder. Resultaterne kan variere fra computer til computer, men  variantvariabler er betydeligt langsommere end heltalsvariabler .

Af denne grund  anbefales det at undgå at bruge Variantvariabler, når du eksplicit kan bruge den angivne datatype .