10 sposobów na przyspieszenie makr

Unikaj nadmiernych odniesień

Wywołanie metody lub właściwości obiektu wymaga przejścia przez interfejs IDispatch komponentu OLE. Wywołania tych komponentów OLE wymagają czasu, więc zmniejszenie liczby odniesień do komponentów OLE może poprawić szybkość kodu makra.

Do wywoływania właściwości lub metod obiektu zazwyczaj używana jest metoda reprezentacji Object.Method  ,  czyli „.” symbol służy do wywoływania właściwości i metod.

Dlatego liczbę wywołań metod lub właściwości można ocenić na podstawie liczby symboli „.”. Im mniej „.” symbol, tym szybciej działa kod.

Na przykład poniższa instrukcja zawiera 3 symbole „.”.

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

Poniższa instrukcja ma tylko jeden symbol „.”.

Activewindow.Top = 100

Oto kilka sztuczek, aby zmniejszyć liczbę symboli „.” biegać szybciej.

Po pierwsze, gdy musisz wielokrotnie odwoływać się do tego samego obiektu, możesz ustawić obiekt na zmienną, aby zmniejszyć liczbę wywołań. Na przykład poniższy kod wymaga dwóch wywołań na linię.

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

Ponieważ  do obiektu Sheets("Sheet1")  trzeba odwoływać się wielokrotnie, można najpierw ustawić dla niego zmienną  sht  , dzięki czemu każdy kod będzie musiał zostać wywołany tylko raz.

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

Po drugie, jeśli nie chcesz deklarować zmiennej tymczasowej sht, możesz także użyć   wspomnianej wcześniej instrukcji With . Jak pokazano w poniższym przykładzie:

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

Po trzecie,  gdy jest wiele pętli, staraj się zachować właściwości i metody poza pętlą.  Kiedy ponownie używasz wartości właściwości tego samego obiektu w pętli, możesz najpierw przypisać wartość właściwości do określonej zmiennej poza pętlą, a następnie użyć tej zmiennej w pętli, co może osiągnąć większą prędkość. Jak pokazano w poniższym przykładzie:

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

Każda pętla w tym przykładzie pobiera właściwość Value komórki Cells(1,2). Jeśli przypiszesz właściwość Value funkcji Cells(1.2) do zmiennej przed rozpoczęciem pętli, działanie będzie szybsze. Jak pokazano w poniższym przykładzie:

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

Powyższy kod wywołuje  ThisWorkbook.Sheets("Sheet1")  przy każdym zapętleniu. Możesz to zrobić szybciej, używając  instrukcji With  w celu przeniesienia wywołania  ThisWorkbook.Sheets("Sheet1")  poza pętlę. Jak pokazano w poniższym przykładzie:

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

Unikaj używania typów wariantów

Początkujący zazwyczaj wolą używać zmiennych typu Variant, co ma tę zaletę, że jest mniej skomplikowane, ponieważ można zastosować dowolny typ danych bez problemu przepełnienia pamięci, jeśli dane są zbyt duże dla typów danych Integer lub Long. Jednak dane typu Varienmt wymagają więcej dodatkowej pamięci niż inne określone typy (2 bajty dla danych Integer, 4 bajty dla danych Long i 16 bajtów dla danych Variant), VBA wymaga więcej czasu na przetworzenie danych typu Variant niż inne określone typy danych. Jak pokazuje poniższy przykład.

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

W powyższym kodzie linie od 8 do 13 wykonują 1 milion operacji dodawania, odejmowania, mnożenia i dzielenia zmiennych typu Variant, a linie od 17 do 22 wykonują 1 milion operacji dodawania, odejmowania, mnożenia i dzielenia zmiennych typu Integer. Na moim komputerze działanie zmiennej Variant trwało około  0,09375  sekundy, natomiast działanie zmiennej Integer trwało około  0,03125  sekundy. Wyniki mogą się różnić w zależności od komputera, ale  zmienne typu Variant są znacznie wolniejsze niż zmienne typu Integer .

Z tego powodu  zaleca się unikanie używania zmiennych Variant, gdy można jawnie użyć określonego typu danych .