VBA Performance Code

[animas]

I found the following code for using in projects to increase VBA performance.

[FONT=Courier]Option Explicit
Option Private Module


Private mlCalcStatus As Long
Private mbInSpeed As Boolean


Public Sub speed()
   On Error Resume Next
   If Not mbInSpeed Then
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      Application.EnableEvents = False
      mlCalcStatus = Application.Calculation
      Application.Calculation = xlCalculationManual
      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
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
   Application.EnableEvents = True
   If mbInSpeed Then
      Application.Calculation = mlCalcStatus
   Else
      'this shouldn't be happening - put calc to auto - safest mode
      Application.Calculation = xlCalculationAutomatic
   End If
   mbInSpeed = False
End Sub[/FONT]

What do you use for your projects?

 

[Ruddles]

Turn screen updating off, yes, this can make a huge difference.

Turning off alerts shouldn't speed up your code by much, if anything. Beware of turning off alerts if they're likely to be telling you something useful.

Setting calculation to manual can help, but code may be relying on calculation to be taking place during program execution, so in that case it's a definite "no".

 

[animas]

Thanks.

What are pros n cons for EnableEvents?

 

[Ruddles]

Consider disabling events before opening workbooks programatically in case they have Workbook_Open macros which may disrupt your processing.

(Disabling events won't stop autoexec macros running when you open an Access database programatically.)

If you disable events, event-driven macros like Worksheet_Change and Worksheet_Calculate won't run, so if they play a vital part in the operation of your workbook you may get erroneous results.