Excel Tips: EnableCalculation Method

I noticed that whenever you invoke Application.Calculation to Automatic, it will recalculate the entire workbook. This is bad if your calculation procedure is only concerned to a particular worksheet. With the function below, I was able to control the scenario:

Public Const ONEONLY as Integer = 1
Public Const DISABLEALL as Integer = 2
Public Const ENABLEALL as Integer = 3

Sub SheetCalculation(iMethod As Integer, Optional sWorksheetName As String)

Dim ws As Worksheet

Application.Calculation = xlCalculationManual

Select Case iMethod

Case ONEONLY: 'To enable active sheet and disable the others

For Each ws In Worksheets
If ws.Name <> sWorksheetName Then
ws.EnableCalculation = False
Else
ws.EnableCalculation = True
End If
Next

Case DISABLEALL: 'To disable all sheets
For Each ws In Worksheets
ws.EnableCalculation = False
Next

Case ENABLEALL: 'To enable all sheets
For Each ws In Worksheets
ws.EnableCalculation = True
Next

Set ws = Nothing

End Select
Application.Calculation = xlCalculationAutomatic

End Sub

With the function above, you can disable/enable the other/all worksheets while doing calculation on the active sheet. This will save calculation time if the active sheet does not rely on any the other sheets at all.

Optimize VBA

This page contains a number of tips for optimizing your VBA codes for Microsoft Excel 97 and 2003.