The Range.Calculate methods are very useful additions to Excel’s other calculation methods (Application level Calculate, CalculateFull, CalculateFullRebuild and Worksheet.calculate: the missing one is Workbook.Calculate!).
You can use the Range Calculate methods to:
- Force calculation of a block of formulas or a single formula
- See how long the variations of a particular formula take to calculate
- Speed up repeated calculations
Download my RangeCalc Addin
You can download my RangeCalc addin from my website’s downloads page (xla password is dm).
This adds a button to the addins tab which uses Range.Calculate to time calculation of the currently selected cells.
Inspecting the RangeCalc code: different problems with different versions
You can unlock the xla to view the code using a password of dm.
The code in the RangeCalc sub bypasses a number of Range.calculate quirks in various Excel versions:
Sub RngTimer() ' ' COPYRIGHT © DECISION MODELS LIMITED 2000,2001. All rights reserved ' ' timed calculation of selected Range ' ' bypass grouped and interactive problem 17/10/00 ' remove interactive=false: Excel 97 Hangs when UDF error 14/2/01 ' fix for application.iteration and array formulae with Excel2002 29/10/2001 ' Dim dRangeTime As Double Dim iMsg As Integer Dim blIter As Boolean Dim oCalcRange As Range ''' range to calculate Dim dOvhd As Double Dim strMessage As String ' ' store iteration property ' blIter = Application.Iteration ' If ActiveWorkbook Is Nothing Or ActiveSheet Is Nothing Or ActiveWindow Is Nothing Or Selection Is Nothing Then Exit Sub Else If TypeName(Selection) = "Range" Then ' ' if Excel2002 or greater handle iteration problem ' If Left(Application.Version, 1) = "1" Then ' ' switch off iteration ' Application.Iteration = False End If ' ' expand selected range to include all of any multicell array formula ' - makes Excel 2002 behave like earlier versions ' - allows notification if range has been expanded ' Call ExpandRange(Selection, oCalcRange) ' On Error GoTo errhandl ' dOvhd = MicroTimer ''' ensure frequency is initialised dOvhd = MicroTimer ''' get time dOvhd = MicroTimer - dOvhd ''' calc microtimer overhead ' dRangeTime = MicroTimer oCalcRange.Calculate dRangeTime = MicroTimer - dRangeTime - dOvhd ' On Error GoTo 0 ' dRangeTime = Int(dRangeTime * 100000) / 100 ' ' 16/11/2009 - bypass multi-cell array formula problem ' If Val(Application.Version) > 9 And Val(Application.Version) < 12 Then oCalcRange.Dirty End If ' ' change message if array formula caused expansion of selection ' If oCalcRange.Count = Selection.Count Then strMessage = CStr(Selection.Count) & " Cell(s) in Selected Range " Else strMessage = CStr(oCalcRange.Count) & " Cell(s) in Expanded Range " End If iMsg = MsgBox(strMessage & CStr(dRangeTime) & " Milliseconds", vbOKOnly + vbInformation, "RangeCalc") End If End If Application.Iteration = blIter ''' restore setting Set oCalcRange = Nothing Exit Sub errhandl: On Error GoTo 0 Application.Iteration = blIter ''' restore setting Set oCalcRange = Nothing iMsg = MsgBox("Unable to Calculate Range", vbOKOnly + vbCritical, "RangeCalc") End Sub
Using Range.Calculate on ranges that contain circular references within the range fails in Excel versions before Excel 2007.
In Excel 2007 and later Range.calculate only does a single iteration of the circular reference in Manual calculation mode, regardless of the Iteration settings.
So the RangeCalc addin switches iteration off whilst doing the Range.Calculate.
Multiple Sheets Selected
If you have multiple sheets selected Range.Calculate fails with a 1004 error, so the RangeCalc code has an error trap and message for any failure in Range.Calculate.
Multiple Areas selected on a single Sheet
Range.Calculate will happily calculate a multi-area selection as long as all the areas are on the same sheet.
Multi-Cell Array formulas
If you do not select all the cells in a multi-cell array formula Range.Calculate will fail. My RangeCalc addin solves this problem by:
- Automatically expanding the range to calculate to include all the cells in any array formula which intersects the selected range
- Notifying the user that the range has been expanded
The VBA code to exapnd the range looks like this:
Sub ExpandRange(oStartRange As Range, oEndRange As Range) ' ' COPYRIGHT © DECISION MODELS LIMITED 2000,2001. All rights reserved ' ' Input: oStartRange, a range object that may or may not contain array formulae ' Output: oEndRange, a range object that has been expanded - ' to include all the cells in any array formula that is partly in the range ' Dim oCell As Range Dim oArrCell As Range ' ' loop on cells in oStartRange ' and expand range to include all the cells in any array formulae ' On Error Resume Next ' Set oEndRange = oStartRange For Each oCell In oStartRange If oCell.HasArray = True Then For Each oArrCell In oCell.CurrentArray ' ' add any extra array cells ' If Intersect(oEndRange, oArrCell) Is Nothing Then ' ' if this cell is not in the expanded range then add it ' Set oEndRange = Union(oEndRange, oArrCell) End If Next oArrCell End If Next oCell Set oCell = Nothing Set oArrCell = Nothing End Sub
There is also another problem with multi-cell array formulas and Range.Calculate, but it only exists in Excel 2002 and 2003 (after a Range.Calculate the array formula gets evaluated once for each cell it occupies in all subsequent recalculations). This problem is bypassed by using Range.Dirty on the range!
Note: The bug in Range.Dirty is still there in Excel 2013. (it always works on the active sheet even when the range refers to another sheet!)
Range.Calculate and Range.CalculateRowMajorOrder – different handling of within-range dependencies
In early Excel versions (Excel 97 and 2000) Range.Calculate used a very simple calculation method: calculate the cells in each row in turn from left to right and ignore any forward references or within range dependencies. This method is fine as long as you know thats what it does and arrange your formulas accordingly (otherwise you may get incorrect results)!
But some people thought this was a bug, so it got fixed in Excel 2002 and 2003 (and later versions): Range.Calculate now starts by doing the left-to right calculation on each row in turn, and then starts recalculating any cells that refer to uncalculated cells within the range. In other words it achieves the same result as the standard Excel recalculation method.
The only problem was that this made Range.Calculate slower than in previous versions: and so some customers refused to upgrade because they could not run their bump runs fast enough!
So in Excel 2007 Microsoft solved the problem by introducing Range.CalculateRowMajorOrder. This method worked exactly the same way as the Excel 97 versions of Range.Calculate and was faster than the new Range.Calculate, and so everyone was happy except the VBA coders who had to work out when to use which method.
Some more Range.Calculate Limitations
Whilst the 2 Range Calculate methods are very useful, they do have some limitations:
- They are both single-threaded calculation methods (In todays world this a serious limitation)
- There is no keystroke sequence to initiate them from the UI (FastExcel uses Alt-F9 for this)
- Re-entrant use of Range.Calculate is not allowed: for instance you can’t call Range.Calculate from inside a UDF
- Range.Calculate works in US english dates etc.
- Range.Calculate and Range.CalculateRowMajorOrder can be fast calculation methods
- But they are not multi-threaded
- For me they are essential tools for comparing formula speed
- They need a bit of wrapping code, as in my RangeCalc addin, to make them generally useful.
Awesome explanation. You are one giant free university. 🙂
Today (more than seven years after this post) I ran into the multi-cell array formula bug described here (in a current Excel 365 version), where Range.Dirty() followed by Worksheet.Calculate() would not recalculate all dependent cells of the array. It happens in the middle of a CSE array, as well as in the middle of a dynamic array, leaving the sheet ‘calculated’ but inconsistent. So I consider this a terrible bug in Excel.
Being able to find this blog entry was wonderful, and I used the `ExpandRange` code translated to C# to get around it. Thank you Charles for digging into this, writing it up and posting on a public space, and for keeping your writings around for all these years.