10 måter å øke hastigheten på makroene dine

Unngå overdreven referanse

Når du kaller en metode eller egenskap til et objekt, må det gå gjennom IDispatch-grensesnittet til OLE-komponenten. Anropene til disse OLE-komponentene tar tid, så å redusere antall referanser til OLE-komponenter kan forbedre hastigheten på makrokoden.

 For påkalling av objektegenskaper eller metoder, brukes vanligvis representasjonsmetoden til  Object.Method , det vil si "." symbol brukes til å påkalle egenskaper og metoder.

Derfor kan antallet metode- eller egenskapskall bedømmes i henhold til antall symboler ".". Jo mindre "." symbol, jo raskere kjører koden.

For eksempel inneholder følgende setning 3 symboler ".".

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

Følgende setning har bare ett symbol ".".

Activewindow.Top = 100

Her er noen triks for å redusere antall symboler "." å løpe raskere.

Først, når du trenger å referere til det samme objektet gjentatte ganger, kan du sette objektet til en variabel for å redusere antall anrop. For eksempel krever følgende kode to anrop per 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 må refereres gjentatte ganger, kan det settes til en variabel  sht  først, slik at hver kode bare trenger å bli kalt én gang.

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

For det andre, hvis du ikke vil deklarere en midlertidig variabel sht, kan du også bruke  With-  setningen nevnt 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 det er mange løkker, prøv å holde egenskaper og metoder utenfor løkken.  Når du gjenbruker en egenskapsverdi for det samme objektet i en løkke, kan du først tilordne egenskapsverdien til en spesifisert variabel utenfor løkken, og deretter bruke variabelen i løkken, som kan oppnå høyere hastighet. 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 løkke i dette eksemplet får Verdi-egenskapen til cellen Cells(1,2). Hvis du tilordner Verdi-egenskapen til Cells(1.2) til en variabel før loopen starter, vil du få en raskere kjøring. 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

Koden ovenfor kaller  ThisWorkbook.Sheets("Sheet1")  hver gang den går i loop. Du kan gjøre dette raskere ved å bruke  With  -setningen til å flytte kallet til  ThisWorkbook.Sheets("Sheet1")  utenfor 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

Unngå å bruke varianttyper

Nybegynnere foretrekker vanligvis å bruke Variant-variabler, som har fordelen av å være mindre kompliserte fordi alle typer data kan brukes uten problemet med minneoverflyt hvis dataene er for store for datatypene heltall eller lange. Varienmt-data krever imidlertid mer ekstra minneplass enn de andre spesifiserte typene (2 byte for heltallsdata, 4 byte for lange data og 16 byte for variantdata), VBA krever mer tid for å behandle data av varianttypen enn andre spesifiserte typer av data. Som 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 koden ovenfor utfører linjene 8 til 13 1 million operasjoner med addisjon, subtraksjon, multiplikasjon og divisjon av Variantvariabler, og linjene 17 til 22 gjør 1 million operasjoner med addisjon, subtraksjon, multiplikasjon og divisjon av heltallsvariabler. På datamaskinen min tok operasjonen av Variant-variabelen omtrent  0,09375  sekunder, mens operasjonen av Integer-variabelen tok omtrent  0,03125  sekunder. Resultatene kan variere fra datamaskin til datamaskin, men  variantvariabler er betydelig tregere enn heltallsvariabler .

Av denne grunn  anbefales det å unngå å bruke Variantvariabler når du eksplisitt kan bruke den angitte datatypen .