VBA Excel - Performance, mergulhando profundamente no VBA - VBA Performance

| Blog Office VBA | Blog Excel | Blog Access |

Muitas pessoas que utilizam o VBA para resolver os seus problemas no dia a dia, sequer imaginam que a linguagem VBA é a mesma utilizada no VB 6.0.

Essa dica a seguir serve para aqueles que corajosamente começaram criando uma macro aqui e outra ali e com o tempo interessaram-se pelo VBA, e hoje criam soluções prá lá e prá cá.

Application.ScreenUpdating & Application.Calculation

O modo como estes dois trabalham juntos encorajaram muitos desenvolvedores VBA para o MS Excel a desligá-los, enquanto seu código executa salvando um monte de tempo e desperdício de ciclos de processamento. E a melhoria é realmente significativa. Há aplicações que chegam a ficar 100 vezes mais rápido (por exemplo: Certo processo levava vários minutos antes da customização, após a otimização leva apenas par de segundos).

A configuração do cálculo é vital embora um erro grave tenha sido relatado depois que alguém, inadvertidamente desabilitou os cálculos dos números manualmente, não percebendo a necessidade de reativá-los quando a aplicação fazia cálculos.

Application.Calculation

Por padrão o MS Excel está definido para o cálculo automático. Para isso o MS Excel usa o seu motor inteligente - apenas calcula as células que foram alteradas, ou tenham sido afetadas por mudanças desde o último cálculo. Isso é ótimo, mas é importante ter em mente as coisas que fazem o MS Excel recalcular:

• Inserir / editar ou apagar um valor na célula
• Pressionar F9 para acionar um cálculo manual
• Inserir ou excluir linhas ou colunas
• ocultar ou reexibir linhas ou colunas
• fixação ou remoção de dados de filtragem
• e alguns outros.

Application.Screenupdating

O padrão do MS Excel mantém a visão da tela atualizada para refletir quaisquer alterações. Isso é uma coisa boa, caso contrário, não veríamos os efeitos quando editássemos uma planilha. A grosso modo a atualização da tela acontece sempre que um calcule ocorre. E isso às vezes pode ser uma chatice no desempenho, especialmente se houver gráficos complexos ou gráficos para redesenhar.

Aqui está um código que ajudará a tornar as suas aplicações muito mais rápidas:

Option Explicit

Option Private Module

Private mlCalcStatus As Long

Private mbInSpeed As Boolean

Public Sub speed()

   On Error Resume Next

   If Not mbInSpeed Then

      Let Application.ScreenUpdating = False

      Let Application.DisplayAlerts = False

      Let Application.EnableEvents = False

      Let mlCalcStatus = Application.Calculation

      Let Application.Calculation = xlCalculationManual

      Let mbInSpeed = True

   Else

      'we are already in speed - don't do the settings again

   End If

End Sub

Public Sub unspeed()

   On Error Resume Next

   Let Application.ScreenUpdating = True

   Let Application.DisplayAlerts = True

   Let Application.EnableEvents = True

   If mbInSpeed Then

      Let Application.Calculation = mlCalcStatus

   Else

      'this shouldn't be happening - put calc to auto - safest mode

      Let Application.Calculation = xlCalculationAutomatic

   End If

   Let mbInSpeed = False

End Sub

Use mais ou menos assim nas suas aplicações:

Public Sub mnu_ListFormulaLinks()

   On Error GoTo err_h

   

   If Not ActiveWorkbook Is Nothing Then

      speed

      'do your stuff

   Else

      MsgBox "Please open the workbook you wish to analyse", vbExclamation, gc_MsgBoxTitle

   End If

   

exit_proc:

   unspeed

   Exit Sub

err_h:

   MsgBox "Error " & Err.Number

   Resume exit_proc

End Sub

Reference: Codematic.net

Tags: VBA, Excel, performance, speed

VBA Excel - Previna o recalculo durante a execução do seu código (Preventing Calculation).

Algo que costuma deixar a execução de uma série de passos em nossas planilhas é o fato de o MS Excel recalcular tudo a cada passo alterado na nossa aplicação. Isso acaba por tornar lentíssima a execução das nossas aplicações. É lógico que podemos dar um jeito nisso. Como? Basta ampliar o exemplo abaixo na sua aplicação. Bons estudos!

Sub StopCalculation()
      Application.Calculation = xlCalculationManual

   ' Digite o seu código aqui.

   Application.Calculation = xlCalculationAutomatic
End Sub

André Luiz Bernardes A&A - WORK, DON´T PLAY! http://al-bernardes.sites.uol.com.br/ bernardess@gmail.com

VBA Excel: Modo Calculation

Modo Calculation

Normalmente o MS Excel recalculará uma Célula ou um Range quando estes forem alterados. Às vezes essa atualização trás certos transtornos, fazendo com que a performance caia. Podemos prevenir que o MS Excel recalcule o workbook por usarmos o statement:.

Neste exemplo abaixo forçamos o recálculo para o modo manual:

Let Application.Calculation = xlCalculationManual

Também temos a possibilidade de mudar esse statement no final do nosso código:

Let Application.Calculation = xlCalculationAutomatic

André Luiz Bernardes A&A - WORK, DON´T PLAY! http://al-bernardes.sites.uol.com.br/ bernardess@gmail.com