10 manieren om uw macros te versnellen

Vermijd overmatige verwijzingen

Bij het aanroepen van een methode of eigenschap van een object moet deze via de IDispatch-interface van de OLE-component gaan. Het aanroepen van deze OLE-componenten kost tijd, dus het verminderen van het aantal verwijzingen naar OLE-componenten kan de snelheid van de macrocode verbeteren.

Voor het aanroepen van objecteigenschappen of -methoden wordt doorgaans de representatiemethode  Object.Method  gebruikt, dat wil zeggen de "." symbool wordt gebruikt om eigenschappen en methoden aan te roepen.

Daarom kan het aantal methode- of eigenschapaanroepen worden beoordeeld aan de hand van het aantal symbolen ".". Hoe minder de "." symbool, hoe sneller de code wordt uitgevoerd.

De volgende verklaring bevat bijvoorbeeld 3 symbolen ".".

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

De volgende verklaring heeft slechts één symbool ".".

Activewindow.Top = 100

Hier zijn enkele trucs om het aantal symbolen "." sneller te rennen.

Als u herhaaldelijk naar hetzelfde object moet verwijzen, kunt u het object eerst op een variabele instellen om het aantal aanroepen te verminderen. Voor de volgende code zijn bijvoorbeeld twee oproepen per lijn vereist.

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

Omdat er herhaaldelijk naar het  object Sheets("Sheet1")  moet worden verwezen, kan het eerst worden ingesteld op een variabele  sht  , zodat elke code slechts één keer hoeft te worden aangeroepen.

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

Ten tweede: als je een tijdelijke variabele sht niet wilt declareren, kun je ook de  eerder genoemde With-  instructie gebruiken. Zoals blijkt uit het volgende voorbeeld:

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

Ten derde:  als er veel lussen zijn, probeer dan de eigenschappen en methoden buiten de lus te houden.  Wanneer u een eigenschapswaarde van hetzelfde object in een lus hergebruikt, kunt u eerst de eigenschapswaarde toewijzen aan een opgegeven variabele buiten de lus en vervolgens de variabele in de lus gebruiken, waardoor een hogere snelheid kan worden bereikt. Zoals blijkt uit het volgende voorbeeld:

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

Elke lus in dit voorbeeld krijgt de eigenschap Value van de cel Cells(1,2). Als u de eigenschap Value van Cells(1.2) aan een variabele toewijst voordat de lus begint, krijgt u een snellere run. Zoals blijkt uit het volgende voorbeeld:

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

De bovenstaande code roept  ThisWorkbook.Sheets("Sheet1") aan  elke keer dat deze wordt herhaald. U kunt dit sneller doen door de  With-  instructie te gebruiken om de aanroep naar  ThisWorkbook.Sheets("Sheet1")  buiten de lus te verplaatsen. Zoals blijkt uit het volgende voorbeeld:

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

Vermijd het gebruik van varianttypen

Beginners geven er meestal de voorkeur aan variabelen van het Variant-type te gebruiken, wat het voordeel heeft dat ze minder ingewikkeld zijn, omdat elk type gegevens kan worden gebruikt zonder het probleem van geheugenoverloop als de gegevens te groot zijn voor de gegevenstypen Integer of Long. Gegevens van het Varienmt-type vereisen echter meer extra geheugenruimte dan de andere gespecificeerde typen (2 bytes voor integer-gegevens, 4 bytes voor lange gegevens en 16 bytes voor variantgegevens). VBA heeft meer tijd nodig om gegevens van het variant-type te verwerken dan andere gespecificeerde typen Van de gegevens. Zoals het volgende voorbeeld laat zien.

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

In de bovenstaande code voeren de regels 8 tot en met 13 1 miljoen bewerkingen uit op het gebied van optellen, aftrekken, vermenigvuldigen en delen van variantvariabelen, en voeren de regels 17 tot en met 22 1 miljoen bewerkingen uit op het gebied van optellen, aftrekken, vermenigvuldigen en delen van gehele variabelen. Op mijn computer duurde de werking van de Variant-variabele ongeveer  0,09375  seconden, terwijl de werking van de Integer-variabele ongeveer  0,03125  seconden duurde. De resultaten kunnen variëren van computer tot computer, maar  Variantvariabelen zijn aanzienlijk langzamer dan Integer-variabelen .

Om deze reden  wordt aanbevolen om het gebruik van variantvariabelen te vermijden wanneer u expliciet het opgegeven gegevenstype kunt gebruiken .