Dicas de VBA – Performance

Por Jorge Paulino para o Pplware!

É muito importante quando se desenvolve alguma macro, que o tempo de execução não seja exagerado, e até porque, as folhas de calculo têm normalmente diversos cálculos, objectos, formatações, etc, o que pode tornar as acções mais lentas.

Existem no entanto pequenos “truques” que permitem melhorar reduzir o tempo de execução das macros e melhorar o aspecto visual quando estas correm.

Application.ScreenUpdating

Esta propriedade define se o Excel actualiza ou não a folha de cálculo. Por defeito está definida como verdadeira (True), mas podemos defini-la como falsa (False).

Por exemplo, se queremos apagar algumas linhas ou colunas individualmente, sempre que damos uma ordem para eliminar, a folha é actualizada, aumentando o tempo de execução. Se definirmos Application.ScreenUpdating = False, só quando voltarmos a definir Application.ScreenUpdating = True, é que a folha é actualizada, reduzindo significativamente o tempo de execução e o aspecto geral da macro.

No seguinte exemplo, só após o código terminar, a folha será actualizada:

Sub DeleteColumns() 

       Application.ScreenUpdating = False 

        Range("c:c").Delete 

        Range("b:b").Delete 

        Range("a:a").Delete 

      Application.ScreenUpdating = True

    End Sub

Application.Calculation

A propriedade Calculation permite definir de que forma são feitos os cálculos. Sempre que escrevemos um valor numa célula, o Excel actualiza todos os cálculos, em todas as células, o que poderá levar algum tempo. Por defeito esta propriedade está definida como automática (xlCalculationAutomatic) mas podemos no entanto definir para não ser executada, ou seja, para manual (xlCalculationManual).

Por exemplo, e para testarmos esta opção, se tivermos na colunas A, B, C, D, E e F todas as células com uma formula para gerar um número aleatório =Rand() , sempre que escrevermos um valor numa célula, irão ser actualizadas todas as formulas. No seguinte exemplo será desabilitado enquanto o ciclo estiver a ser executado, e poderá testar com e sem o desabilitar dos cálculos automáticos, para ver a diferença.

Sub WriteValues()

        Dim x As Integer

        Application.Calculation = xlCalculationManual

        For x = 1 To 100

            Cells(x, "g").Value= x

        Next

        Application.Calculation = xlCalculationAutomatic

    End Sub

Application.EnableEvents

Com explicado num artigo anterior, existem diversos eventos que estão disponíveis no Excel. Esses eventos podem executar operações demoradas e isso pode não ser desejado. Por defeito esta propriedade está definida como verdadeira (True) mas podemos desactivar quando queremos que os eventos não aconteçam.

Imagine que no evento Change, que é executado sempre que algum valor seja alterado, temos um ciclo demorado.

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim x As Integer

        For x = 0 To 1000

            Debug.Print(x)

        Next

    End Sub

Com a opção Application.EnableEvents = True, sempre que escrever um valor em qualquer célula, irá correr também o código anterior.

Definindo esta propriedade como falsa, Application.EnableEvents = False, fará com que apenas se execute o nosso código e não os restantes eventos do documento.

Sub Run()

        Dim x As Integer

        Application.EnableEvents = False

        For x = 1 To 100

            Cells(x, "g").Value = x

        Next

        Application.EnableEvents = True

    End Sub

Isoladamente ou em conjunto, estas três propriedades permitem melhorar significativamente o tempo de execução de uma macro e com isso tornar a nossa folha de cálculo mais atractiva.

NOTA: é preciso ter algum cuidado com a utilização destas propriedades, e garantir que são sempre repostas para as definições originais, correndo-se o risco de a folha não trabalhar como está planeado. Se colocarmos, por exemplo, a propriedade Application.EnableEvents = False e não voltarmos a colocar a True, todos os eventos ficaram congelados.

Para breve mais dicas de como melhorar o código em VBA!

Escrito por: Jorge Paulino

Página Autor: http://www.jorgepaulino.com