10 sätt att snabba upp dina makron

Undvik överdriven referens

När du anropar en metod eller egenskap för ett objekt måste den gå igenom IDispatch-gränssnittet för OLE-komponenten. Anropen till dessa OLE-komponenter tar tid, så att minska antalet referenser till OLE-komponenter kan förbättra hastigheten på makrokoden.

 För anropande av objektegenskaper eller metoder används vanligtvis representationsmetoden för  Object.Method , det vill säga "." symbol används för att anropa egenskaper och metoder.

Därför kan antalet metod- eller egenskapsanrop bedömas enligt antalet symboler ".". Ju mindre "." symbol, desto snabbare körs koden.

Till exempel innehåller följande påstående 3 symboler ".".

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

Följande påstående har bara en symbol ".".

Activewindow.Top = 100

Här är några knep för att minska antalet symboler "." att springa snabbare.

Först, när du behöver referera till samma objekt upprepade gånger, kan du ställa in objektet till en variabel för att minska antalet anrop. Till exempel kräver följande kod två samtal per linje.

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

Eftersom  Sheets("Sheet1")  -objektet måste refereras upprepade gånger, kan det ställas in på en variabel  sht  först, så att varje kod bara behöver anropas en gång.

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

För det andra, om du inte vill deklarera en temporär variabel sht, kan du också använda  With  -satsen som nämndes tidigare. Som visas i följande exempel:

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

För det tredje,  när det finns många loopar, försök att hålla egenskaper och metoder utanför loopen.  När du återanvänder ett egenskapsvärde för samma objekt i en loop kan du först tilldela egenskapsvärdet till en specificerad variabel utanför loopen och sedan använda variabeln i loopen, vilket kan uppnå snabbare hastighet. Som visas i följande exempel:

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

Varje slinga i detta exempel får egenskapen Value för cellen Cells(1,2). Om du tilldelar egenskapen Value för Cells(1.2) till en variabel innan loopen startar, kommer du att få en snabbare körning. Som visas i följande exempel:

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

Ovanstående kod anropar  ThisWorkbook.Sheets("Sheet1")  varje gång den loopar. Du kan göra detta snabbare genom att använda  With  -satsen för att flytta anropet till  ThisWorkbook.Sheets("Sheet1")  utanför loopen. Som visas i följande exempel:

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

Undvik att använda varianttyper

Nybörjare föredrar vanligtvis att använda Variant-variabler, vilket har fördelen att vara mindre komplicerat eftersom alla typer av data kan användas utan problem med minnesspill om data är för stora för datatyperna heltal eller långa. Varienmt-data kräver dock mer extra minnesutrymme än de andra specificerade typerna (2 byte för heltalsdata, 4 byte för långa data och 16 byte för variantdata), VBA kräver mer tid för att bearbeta data av varianttyp än andra specificerade typer av data. Som följande exempel visar.

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 ovanstående kod gör raderna 8 till 13 1 miljon operationer med addition, subtraktion, multiplikation och division av Variantvariabler, och raderna 17 till 22 gör 1 miljon operationer med addition, subtraktion, multiplikation och division av heltalsvariabler. På min dator tog driften av Variantvariabeln cirka  0,09375  sekunder, medan operationen av Integer-variabeln tog cirka  0,03125  sekunder. Resultaten kan variera från dator till dator, men  Variantvariabler är betydligt långsammare än heltalsvariabler .

Av denna anledning  rekommenderas det att undvika att använda Variantvariabler när du uttryckligen kan använda den angivna datatypen .