Formula References between Sheets versus within Sheets shootout: Which calculates faster and uses more Memory

I thought I would revisit the differences between formulas that reference other worksheets and formulas that only reference their own worksheet. Referencing other worksheets always used to be a memory hog, but so much has changed between Excel 2003 and Excel 2013 that I wanted to see the current status.

The Test Workbooks

The test workbooks are all generated by simple VBA code contained in thw MakeInterLinkedSheets.xlsb workbook, which you can down load from here.

Generating Many Linked Worksheets

The code to generate the interlinked worksheets is shown below. You can choose how many worksheets to generate, and then each worksheet will contain a column of constants and a column of formulas that refer to each of the other worksheets. So if you choose 1500 worksheets each worksheet will contain 1500 formulas with every formula referring to a different worksheet (you can’t get much more linked than that!). Thats a total of 2.25 million formulas.

Sub MakeManyLinkedSheets()
 '
 ' make a large number of worksheets, each of which links to all of the others
 '
 Dim j As Long
 Dim k As Long
 Dim varSheets As Variant
 Dim nSheets As Long
 Dim nRequest As Long
 Dim nAdd As Long
 Dim var() As Variant
 '
 varSheets = Application.InputBox("Enter the Number of Interlinked Sheets to Generate", "Inter-Linked Sheets", 1500)
 If Not IsNumeric(varSheets) Then
 MsgBox "Input must be a number, MakeManyLinkedSheets cancelled", vbOKOnly + vbCritical
 Exit Sub
 Else
 Application.ScreenUpdating = False
 Application.Calculation = xlManual
 '
 nRequest = CLng(varSheets)
 '
 ' add sheets: cannot add more than 255 in one .Add statement
 '
 nSheets = ActiveWorkbook.Worksheets.Count
 nRequest = nRequest - nSheets
 Application.StatusBar = "Adding Sheets"
 Do While nRequest > 0
 nAdd = nRequest
 If nAdd > 255 Then nAdd = 255
 ActiveWorkbook.Worksheets.Add before:=ActiveWorkbook.Worksheets(nSheets), Count:=(nAdd)
 nSheets = ActiveWorkbook.Worksheets.Count
 nRequest = nRequest - nAdd
 Loop
 '
 ' add constant and linkage formula
 '
 For j = 1 To ActiveWorkbook.Worksheets.Count
 Application.StatusBar = "Generating Linkages on Sheet " & CStr(j)
 ReDim var(ActiveWorkbook.Worksheets.Count, 2)
 For k = 1 To ActiveWorkbook.Worksheets.Count
 var(k, 1) = j * k
 var(k, 2) = "=Sheet" & CStr(k) & "!a" & CStr(k)
 Next k
 Worksheets(j).Range("a1").Resize(ActiveWorkbook.Worksheets.Count, 2).Formula = var
 Next j
 Application.StatusBar = False
 Application.Calculation = xlAutomatic
 End If
 End Sub

Since you still (even in XL 2013) cannot create more than 255 sheets in a single Worksheets.Add command the code creates the worksheets in blocks of 255.

Memory Used & File Size

In old versions of Excel (97/2000) this code hit the memory wall at about 200 worksheets.
In Excel 2013 32-bit you can get up to over 2500 worksheets but 4000 fails at about 1.4 gigabytes.
In Excel 2013 64-bit I got to 5 gigabytes of memory trying for 4000 sheets but it was so slow I gave up.

For 1500 sheets:

• Excel 2010 32 uses about 430 Megabytes of memory for the workbook
• Excel 2013 32 uses about 540 Megabytes of memory for the workbook
• Excel 2013 64 uses about 770 Megabytes of memory for the workbook
• The workbook takes about 40 Megabytes when saved as an XLSB

Comparing Within Sheet References and Between Sheets References

Generating the Formulas

I used 3 different methods for generating the within-sheet reference formulas (in R1C1 mode):

Method 1 uses a formula that refers to the previous column on this row:
=Sheet1000!RC[-1]

Method 2 uses a formula that always refers to column 1 for this row:
=Sheet1000!RC1

Method 3 uses a formula that randomly refers to rows and columns (nRequest is the number of sheets requested):
"=Sheet1000!R" & Int(Rnd() * nRequest + 1) & "C" & Int(Rnd() * nRequest + 1)


The reason for using 3 different formulas is to see what effect different kinds of references have on memory and calculation speed.

The complete code for method 3 looks like this:

</pre>
Sub MakeManyFormulas3()
 '
 ' make a single worksheet that refers to itself
 ' generate pairs of columns:
 ' numeric constant followed by formula that refers to a random row and column
 '
 Dim j As Long
 Dim k As Long
 Dim varSheets As Variant
 Dim nSheets As Long
 Dim nRequest As Long
 Dim nAdd As Long
 Dim var() As Variant
 '
 varSheets = Application.InputBox("Enter the Number of Formulas to Generate", "Inter-Linked Sheets", 200)
 If Not IsNumeric(varSheets) Then
 MsgBox "Input must be a number, MakeManyLinkedFormulas cancelled", vbOKOnly + vbCritical
 Exit Sub
 Else
 Application.ScreenUpdating = False
 Application.Calculation = xlManual
 Application.ReferenceStyle = xlR1C1
 nRequest = CLng(varSheets)
 '
 ' add constant and linkage formula
 '
 For j = 1 To nRequest
 ReDim var(nRequest, 2)
 For k = 1 To nRequest
 var(k, 1) = j * k
 '
 ' refers to random row and column
 '
 var(k, 2) = "=Sheet1000!R" & Int(Rnd() * nRequest + 1) & "C" & Int(Rnd() * nRequest + 1)
 Next k
 Worksheets("Sheet1000").Range("a1").Resize(nRequest, 2).Offset(0, (j - 1) * 2).FormulaR1C1 = var
 Next j
 Application.StatusBar = False
 Application.Calculation = xlAutomatic
 Application.ReferenceStyle = xlA1
 End If
 End Sub

Timing and Memory Results

The memory used is the difference between before and after memory (Private Working Set) as measured by Windows 7 Task Manager.

The Full Calculate Time is the time taken for the second or third multi-threaded (4 cores – 8 threads) full calculation of all 2.25 million formulas as measured by FastExcel.

Conclusions

I must admit I was surprised about the calculation times: I thought they would be larger for the between sheets references than for the within sheets references. But there is no real noticeable difference: a larger factor is where the within-sheet formulas refer to, or more likely the total number of unique formulas used.
(Random >column to the left> always first column)

XL Version	Formula Method	Memory (MB)	Full Calc Time
2010 32	Interlink Sheets	426 MB	0.30 Seconds
2010 32	Previous Column	320 MB	0.27 Seconds
2010 32	First Column	214 MB	0.26 Seconds
2010 32	Random	286 MB	0.60 Seconds
2013 32	Interlink Sheets	538 MB	0.29 Seconds
2013 32	Previous Column	402 MB	0.29 Seconds
2013 32	First Column	300 MB	0.26 Seconds
2013 32	Random	369 MB	0.66 Seconds
2013 64	Interlink Sheets	835 MB	0.33 Seconds

My conclusions from all this are:

• 64-bit Excel uses more memory than 32-bit Excel
• Interlinking sheets uses more memory than within-sheet references.
• There is no significant calculation time penalty in using inter-sheet references
• Excel 2013 uses more memory than Excel 2010
• The more unique formulas there are the more memory and calculation time is needed