Exploring Range.Calculate and Range.CalculateRowMajorOrder: fast but quirky formula calculation

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

Circular References

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.

Summary

• 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.

Advertisement