Visual Basic em Português

Página pessoal de Jorge Paulino sobre o Visual Basic (VB.NET, ASP.NET, VB6, VBA) e algumas noticias de tecnologia

Excel: Dicas de VBA - Performance

É 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()

        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!

9 comentários:

Pedro Costa disse...

o Application.EnableEvents não conhecia. Estamos sempre a aprender. No entanto parece-me que a sua utilidade resume-se a quando temos código para eventos, como o que mostras-te (Worsheet_Change). Ou estarei enganado?

o Application.ScreenUpdating não tem só a utilidade de acelerar o processo como também de ser mais agradável ao utilizador, sem que este esteja a ver coisas, possivelmente estranhas, a acontecerem rapidamente. Em conjunto com o Application.Statusbar é bastante bom.

Aliás apesar de não provocar qualquer tipo de melhoria de performance verdadeiramente (na verdade até pode tirar alguma, mas de forma negligenciável), o Application.Statusbar pode até dar a ilusão de ser mais rápido se oferecer informações do que está a fazer ou do que falta ainda fazer. Bastante útil em processos muito demorados. Exemplo:

Application.statusbar = "A processar registo " & actual & " de " & total

Não esquecer de no final colocar:

Application.Statusbar = False

para fazer o reset para defeito.

Não há nada de mais frustrante que ter um processo demorado e não saber se bloqueou ou se ainda falta muito. O tempo pode ser o mesmo mas psicologicamente parece mais.

Já agora sou MetalFatigue do P@P. Já me tens dado umas valentes ajudas em VBA :)
Principalmente ao inicio quando eu era ainda um maçarico nisto.

Cumprimentos

jpaulino disse...

>> No entanto parece-me que a sua utilidade resume-se a quando temos código para eventos, como o que mostras-te (Worsheet_Change).

O EnableEvents = False serve para que os eventos não "disparem", e isso poderá reduzir bastante o tempo de execução.

Não falei no Application.StatusBar porque não está relacionado com performance.

>> Já agora sou MetalFatigue do P@P. Já me tens dado umas valentes ajudas em VBA :)

Ainda bem que tenho ajudado e para breve mais alguns artigos sobre VBA. Fica prometido!

Um abraço
Jorge Paulino

Pedro Costa disse...

>>Não falei no Application.StatusBar porque não está relacionado com performance.

Sim eu compreendi era só uma pequena dica. Pois tal como disse não tem ganho real. O ganho é ilusório.

>>>Ainda bem que tenho ajudado e para breve mais alguns artigos sobre VBA. Fica prometido!

Não prometas que eu até prefiro .NET (C# ou VB). No entanto por razões profissionais sou obrigado a estar mais por dentro de VBA, infelizmente para mim.

Anónimo disse...

Pessoal,
poderia apresentar uma pequena macro para uso de teste em célula formatada em vermelho?

Jorge Paulino disse...

Olá,

Não entendi qual é o objectivo. Pode dar mais detalhes?

Anónimo disse...

Olá boa tarde. Alguém sabe como inserir um valor por defeito num textbox no vba excel?
agradeço

Jorge Paulino disse...

E a textbox está num userform?

D13G1NN disse...

Grande dica.
Seu estudante de excel com vba pelo curso adv, www.cursoadv.com.br
Estou sem em busca de material para complementar meus estudos.
O site está muito bom, parabéns e obrigado pelas dicas.

Eduardo Paes Leme disse...

muito bom artigo... vou passar pro nosso grupo, obrigado!
http://www.escolatecnicarj.com.br

vlww

Mensagens Recentes



Microsoft Office Especialist

Membro da Comunidade
Experts-Exchange


Administ. da Comunidade
Portugal-a-Programar



Twitter

Artigos no CodeProject

Artigos no CodeProject

Subscrever Novidades

Endereço de Email:

Delivered by FeedBurner

Seguidores

Histórico