Hello all,
I'm looking for tips on how to avoid the seemingly intermitent, yet cripling performance bottleneck that occurs when calling User Defined Functions that refer to large ranges containing formulas.

Let me describe a simple setup that triggers the performance problem I am seeing.

PROBLEM SETUP
----------------
On a new blank spreadsheet, setup a column containing 14,000 static (ie. not formulas) numbers. Let's call this the Static column.

Setup another 14,000 row column next to the Static column. Let's call this the Formula column. Each cell in the Formula column should manipulate the equivalent cell in the Static column using a formula (subtracting a number from the number in the static column should suffice).

enter a call to the UDF "doit" on another cell, passing it the entire Formula column as the input argument range. An implementation of doit is found below

Public Function doit(theRange As Range) As Long
'Doing something with theRange seems to trigger the performance
'bottleneck
Dim dummy as Integer
If IsEmpty(theRange) Then dummy = 0

'Count number of times this function is called
static numberOfCalls as Long
numberOfCalls = numberOfCals+1

doit=numberOfCalls
End Function
--------------

PROBLEM OBSERVATIONS
--------------------------
1)
The performance hit is due to the doit function intermitently being called 14,000 times by excel (once for each cell in the input argument range)
This happens most often though not always when requesting a Full recalc with dependency tree rebuild (Ctrl+Alt+Shift+F9) though it also happens, albeit less frequently with all other calculate requests, namely:
Full Recalc (Ctrl+Alt+F9)
Recalc (F9)
Sheet Recalc (Shift+F9)

2)
Note that this DOES NOT seem to happen if instead of the Formula Column, we pass the Static column to the doit function

3)
Note also that this does not seem to happen if the column size is small

4)
In a more complex spreadsheet with several "doit" calls referring to various large formula ranges, this intermitent 14,000 times call repeat problem occurs much more often as it seems to affect each doit cell at different times. The more doIt cells there are the more chances for the problem to occur. INterestingly though I have not yet seen an ocasion when it's affected more than one doit cell at a time.


PROBLEM ANALYSIS
----------------------
I am experiencing this using Excel 2003 and my thoughts on what is happening based on the observations above are the following:

1)
The fact that the call is repeated once for each cell in the passed range (14,000 times) and that the problem occurs most often when requesting a Full Recalc with dependency tree rebuild leads me to beleive that the problem is caused by the dependency tree logic

2)
The fact that the problem only manifest itself when the passed range is made up of formulas which themselves depend on other cells, also points to a dependency tree problem as clearly this setup has a higher level of dependencies than when the passed range is just the static data column

3)
The fact that the problem does not manifest itself for small ranges points to a possible Excel 2003 dependency limit breach causing Excel to always do a full recalc rather than store a large dependency tree. However, why is the behaviour intermitent? And why is the limit breached for such a small spreadsheet. (Read more on limits on these links)
http://blogs.msdn.com/excel/archive/...26/474258.aspx
http://www.decisionmodels.com/calcsecretsf.htm

5)
I am also surprised that the problem does not occur unless the UDF mentions the passed range in the body of the code. ie the range being in the argment list on its own does not cause the problem. It's only when you try t use the argument in the body


WHAT NEXT?
--------------

I would be very greatful if anyone with experience in building large spreadsheets or with experience of this particular problem could share how they've been getting around this issue

Thanks so much


TheKid