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 значно повільніші, ніж змінні Integer .

З цієї причини  рекомендується уникати використання змінних Variant, якщо ви можете явно використовувати вказаний тип даних .