Calculation Mode and Excel Optimization
How to properly set calculation mode in Excel VBA
Most people set calculation mode to 'manual' at the beginning of the code, then set it to 'automatic' at the end. This assumes that the setting was 'automatic' in the first place and the user wants the calculation mode to be 'automatic' after the code finishes. While I believe that in 99.9% of cases, calculation should always be set to automatic (see Spreadsheet Speeding up Re-calculations for explanation), your code should never make unrequested changes without notifying the user. Here is how to properly set the calculation mode so it is restored to whatever it was previously.
' get current calculation state, save for later
CalcMode = Application.Calculation
' set to manual for the duration of the code
Application.Calculation = xlCalculationManual
'your code here
' set it back to whatever it was before the code was run.
Application.Calculation = CalcMode
Optimize your macros
This can be cut and pasted into your routines to make them run faster. It will stop events, disable automatic calculation and screen updating and, if any dialog boxes pop up, it gives the default answer silently. Keep in mind that in some cases, you may actually want some of these things to happen (i.e. your code may depend on calculations made in the workbook during processing). In that case, simply remove the parts you don't want. In keeping with the spirit of storing and restoring settings to avoid disrupting the user, the code uses variables to store the current states of each property, turns them off for the duration of the code, then restores them to whatever they were previously. That being said, sometimes you want calculation, events, etc to occur. I recommend leaving calculation at 'automatic' under most circumstances, otherwise you are asking for trouble.
Paste this in right after the initial Sub line in your macro (or you might want to create a separate procedure just for this):
Dim bAlerts As Boolean
Dim CalcMode As Long
Dim bScreen As Boolean
' save current settings
bEvents = Application.EnableEvents
bAlerts = Application.DisplayAlerts
CalcMode = Application.Calculation
bScreen = Application.ScreenUpdating
' disable events, alerts, automatic calculation & screen updating
With Application
.EnableEvents = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
' your code here
Then right before the End Sub, paste this:
With Application
.EnableEvents = bEvents
.DisplayAlerts = bAlerts
.Calculation = CalcMode
.ScreenUpdating = bScreen
End With
Minimize interaction between VBA and Excel
Check out my VBA Tips, Tricks and Best Practices post series for even more tips.
Believe it or not, VBA and Excel are actually separate entities. There is a time penalty when reading worksheet data into VBA, so you want to minimize the amount of times you have to touch the worksheet. Ideally, you should touch the worksheet once to read in data, then once to write back.
This is why cell-by-cell loops are dangerous, because you are hitting the worksheet multiple times. See Trimming cells, before and after for an example. Here's a typical loop:
' select a bunch of cells and run this
Dim cell As Excel.Range
Dim totals As Long ' = 0
For Each cell In Selection
If cell.Value = "3" Then
totals = totals + 1
End If
Next cell
MsgBox "There are " & totals & " cells with 3 in them."
End Sub
If there are 500 cells in the selection, then VBA has to touch the worksheet 500 times. As you can imagine, this code will be extremely slow (relative to, say, the amount of time it takes to make microwave popcorn). It will be even slower as you try to do more complicated things like insert formulas, update another worksheet, make more intense calculations, etc. A better way is to read the entire range into an array, and do your looping there. An array in memory will loop much faster.
Here is some stock code I've been using that can be used to read any selection into memory and manipulate it very quickly.
' touch the worksheet twice only
Dim arrData() As Variant
Dim rng As Excel.Range
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long
' let's not accidently use this on a non-Range object
If TypeName(Selection) <> "Range" Then Exit Sub
lRows = Selection.Rows.Count
lCols = Selection.Columns.Count
ReDim arrData(1 To lRows, 1 To lCols)
ReDim arrReturnData(1 To lRows, 1 To lCols)
Set rng = Selection
' once
arrData = rng.Value
For j = 1 To lCols
For i = 1 To lRows
' arrData(i, j) will contain the data from the cell you want to check
Next i
Next j
' twice
rng.Value = arrData
Set rng = Nothing
End Sub
See Trimming cells, before and after for an example of how I use this. The way to rewrite the TypicalLoop code would be
Dim arrData() As Variant
Dim rng As Excel.Range
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long
Dim totals As Long ' = 0
' let's not accidently use this on a non-Range object
If TypeName(Selection) <> "Range" Then Exit Sub
lRows = Selection.Rows.Count
lCols = Selection.Columns.Count
ReDim arrData(1 To lRows, 1 To lCols)
ReDim arrReturnData(1 To lRows, 1 To lCols)
Set rng = Selection
arrData = rng.Value
For j = 1 To lCols
For i = 1 To lRows
If arrData(i, j) = 3 Then
totals = totals + 1
End If
Next i
Next j
MsgBox "There are " & totals & " cells with 3 in them."
Set rng = Nothing
End Sub
