In worksheets that have a large amount of formulas, this behavior can drastically slow down your macros.
If your workbook is formula intensive, you may not want Excel to trigger a recalculation every time a cell value is altered by your macro. Calculation property to tell Excel to switch to manual calculation mode.
When a workbook is in manual calculation mode, the workbook will not recalculate until you explicitly trigger a calculation.
Screen Updating property is useful when running macros that jump from cell to cell, sheet to sheet, and workbook to workbook. Screen Updating to False stops your screen from flickering (refreshing) as your macro runs.
While I’m thinking about this stuff, I’d like to share a few simple tips for speeding up your Excel VBA code. So feel free to comment and share any other performance tips you may have.
Each time a cell that affects any formula in your spreadsheet is changed or manipulated, Excel recalculates the entire worksheet.
Public Function Updating(b Enable As Boolean) As Boolean 'Usage: 'Your Sub() of Function() ' Updating(False) ' Your code...
' Updating(True) 'Or use this if the routine is (or could be) called from a routine that also calls 'this function (prevents intermediate updates/recalc's/flickering): 'Your Sub() of Function() ' Dim Boolean updatestatus ' updatestatus = Updating False 'Basically, this takes over mode of caller ' Your code... Screen Updating = True End Sub Public Function Count Colored Cells(Fill Color As Variant, rng Color Range As Range, Optional Sum Contents Of Fill Color As Boolean = False) As Variant Dim rng Count As Range Dim fillcol As Long Dim Count As Variant 'Detect if Fill Color is a range and if so, take it's first cell's color If Type Name(Fill Color) = "Range" Then fillcol = Fill Color(1, 1). Color Index Else fillcol = Fill Color End If 'Sum or count cells with the specified Fill Color If Sum Contents Of Fill Color = True Then For Each rng Count In rng Color Range If rng Count. Color Index = fillcol Then Count = Worksheet Function.