10 maneiras de acelerar suas macros

Evite referências excessivas

Ao chamar um método ou propriedade de um objeto, ele precisa passar pela interface IDispatch do componente OLE. As chamadas para esses componentes OLE demoram, portanto, reduzir o número de referências a componentes OLE pode melhorar a velocidade do código de macro.

Para a invocação de propriedades ou métodos de objetos, geralmente é utilizado o método de representação de  Object.Method  , ou seja, o "." símbolo é usado para invocar propriedades e métodos.

Portanto, o número de chamadas de métodos ou propriedades pode ser avaliado de acordo com o número de símbolos ".". Quanto menos "." símbolo, mais rápido o código será executado.

Por exemplo, a instrução a seguir inclui 3 símbolos ".".

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

A instrução a seguir possui apenas um símbolo ".".

Activewindow.Top = 100

Aqui estão alguns truques para reduzir o número de símbolos "." para correr mais rápido.

Primeiro, quando você precisar se referir ao mesmo objeto repetidamente, poderá definir o objeto como uma variável para reduzir o número de chamadas. Por exemplo, o código a seguir requer duas chamadas por linha.

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

Como o  objeto Sheets("Sheet1")  precisa ser referenciado repetidamente, ele pode ser definido como uma variável  sht  primeiro, de modo que cada código precise ser chamado apenas uma vez.

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

Segundo, se você não quiser declarar uma variável temporária sht, você também pode usar a  instrução With  mencionada anteriormente. Conforme mostrado no exemplo a seguir:

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

Terceiro,  quando houver muitos loops, tente manter propriedades e métodos fora do loop.  Ao reutilizar um valor de propriedade do mesmo objeto em um loop, você pode primeiro atribuir o valor da propriedade a uma variável especificada fora do loop e, em seguida, usar a variável no loop, o que pode atingir uma velocidade mais rápida. Conforme mostrado no exemplo a seguir:

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

Cada loop neste exemplo obtém a propriedade Value da célula Cells(1,2). Se você atribuir a propriedade Value de Cells(1.2) a uma variável antes do início do loop, você obterá uma execução mais rápida. Conforme mostrado no exemplo a seguir:

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

O código acima chama  ThisWorkbook.Sheets("Sheet1")  sempre que faz um loop. Você pode fazer isso mais rapidamente usando a  instrução With  para mover a chamada para  ThisWorkbook.Sheets("Sheet1")  fora do loop. Conforme mostrado no exemplo a seguir:

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

Evite usar tipos de variantes

Iniciantes geralmente preferem usar variáveis ​​do tipo Variant, o que tem a vantagem de ser menos complicado porque qualquer tipo de dado pode ser usado sem o problema de estouro de memória se os dados forem muito grandes para os tipos de dados Integer ou Long. No entanto, os dados do tipo Varienmt requerem mais espaço de memória extra do que os outros tipos especificados (2 bytes para dados inteiros, 4 bytes para dados longos e 16 bytes para dados variantes), o VBA requer mais tempo para processar dados do tipo variante do que outros tipos especificados De dados. Como mostra o exemplo a seguir.

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

No código acima, as linhas 8 a 13 fazem 1 milhão de operações de adição, subtração, multiplicação e divisão de variáveis ​​variantes, e as linhas 17 a 22 fazem 1 milhão de operações de adição, subtração, multiplicação e divisão de variáveis ​​inteiras. No meu computador, a operação da variável Variant demorou cerca de  0,09375  segundos, enquanto a operação da variável Integer demorou cerca de  0,03125  segundos. Os resultados podem variar de computador para computador, mas  as variáveis ​​Variant são significativamente mais lentas que as variáveis ​​Integer .

Por esse motivo,  é recomendável evitar o uso de variáveis ​​Variant quando você puder usar explicitamente o tipo de dados especificado .