10 начина да ускорите вашите макроси

Избягвайте прекомерните препратки

Когато извиквате метод или свойство на обект, той трябва да премине през интерфейса IDispatch на OLE компонента. Извикванията към тези OLE компоненти отнемат време, така че намаляването на броя на препратките към OLE компоненти може да подобри скоростта на макро кода.

За извикване на свойства или методи на обекти обикновено се използва методът за представяне на  Object.Method  , т.е. символ се използва за извикване на свойства и методи.

Следователно броят на извикванията на метод или свойство може да се прецени според броя на символите ".". Колкото по-малко е "." символ, толкова по-бързо се изпълнява кодът.

Например следният израз включва 3 символа ".".

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

Следното твърдение има само един символ ".".

Activewindow.Top = 100

Ето няколко трика за намаляване на броя на символите "." да тичам по-бързо.

Първо, когато трябва да се обърнете към един и същ обект многократно, можете да зададете обекта на променлива, за да намалите броя на извикванията. Например, следният код изисква две повиквания на ред.

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

Тъй като  обектът Sheets("Sheet1")  трябва да бъде препратен многократно, той може първо да бъде зададен на променлива  sht  , така че всеки код трябва да бъде извикан само веднъж.

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

Второ, ако не искате да декларирате временна променлива sht, можете също да използвате  израза With  , споменат по-рано. Както е показано в следния пример:

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

Трето,  когато има много цикли, опитайте се да запазите свойствата и методите извън цикъла.  Когато използвате повторно стойност на свойството на същия обект в цикъл, можете първо да присвоите стойността на свойството на определена променлива извън цикъла и след това да използвате променливата в цикъла, което може да постигне по-висока скорост. Както е показано в следния пример:

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

Всеки цикъл в този пример получава свойството Value на клетката Cells(1,2). Ако присвоите свойството Value на Cells(1.2) на променлива преди началото на цикъла, ще получите по-бързо изпълнение. Както е показано в следния пример:

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

Горният код извиква  ThisWorkbook.Sheets("Sheet1")  всеки път, когато зацикли. Можете да направите това по-бързо, като използвате  израза With  , за да преместите извикването към  ThisWorkbook.Sheets("Sheet1")  извън цикъла. Както е показано в следния пример:

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

Избягвайте използването на типове варианти

Начинаещите обикновено предпочитат да използват променливи тип Variant, което има предимството да е по-малко сложно, тъй като всеки тип данни може да се използва без проблем с препълване на паметта, ако данните са твърде големи за типовете данни Integer или Long. Данните от тип Varienmt обаче изискват повече допълнително пространство в паметта, отколкото другите определени типове (2 байта за данни Integer, 4 байта за данни Long и 16 байта за данни Variant), VBA изисква повече време за обработка на данни от тип Variant, отколкото други определени типове на данни. Както показва следният пример.

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

В горния код редове от 8 до 13 извършват 1 милион операции на събиране, изваждане, умножение и деление на променливи Variant, а редове от 17 до 22 извършват 1 милион операции на събиране, изваждане, умножение и деление на променливи Integer. На моя компютър работата на променливата Variant отне около  0,09375  секунди, докато операцията на променливата Integer отне около  0,03125  секунди. Резултатите може да варират от компютър на компютър, но  вариантните променливи са значително по-бавни от целочислените променливи .

Поради тази причина  се препоръчва да избягвате използването на променливи Variant, когато можете изрично да използвате указания тип данни .