I have gotten these symptoms to show up in both Excel 2003 and Excel
2000.

The following downloadable sheet, has just the one user-defined
function (in cell selected when opened). To make testing manageable,
calculation is purposely set to "manual" and "not on save". After
opening, pressing F9 (calculate key) takes you through 1981 passes of
the function until the sheet thinks it is calculated. The function
has a 2001-cell range argument. Calculation can be forced by
editing/changing any single cell in the range D18 through AA18

As usual, during calculation you can interrupt the process by
selecting a cell on the sheet before it is done. Checking the seconds
per function pass dumped to the Immediate Window, you will be able to
estimate roughly how long it will take to finish after 1981 passes (5
minutes on one of my machines, 10 on another).

http://www.oz.net/~gurfler/Download/TestBugN.xls

Once you have let the calculation complete, it never takes that long
again. The following sheet is in that state, but is in no other
respect different:

http://www.oz.net/~gurfler/Download/TestBugOK.xls

Diagnostics illustrating very strange behavior during each pass of the
function are dumped to the Immediate Window in the following (just hit
F9 as soon as it is loaded):

http://www.oz.net/~gurfler/Download/TestBugDebug.xls

These diagnostics indicate that on first pass the function only sees
one cell in its range-argument as non-empty, next pass two cells,
etc., more or less, until everything clears up after 1981 passes (why
20 short of 2001 ???).

Making the range argument of the function into a worksheet array is a
great way to prevent this horrible stall in calculation, though the
diagnostic here does show that even then it still takes two passes to
complete, first pass apparently all cells come through empty.

http://www.oz.net/~gurfler/Download/...DebugArray.xls

OK, no smart-alec comments: I know that this function merely
duplicates what can be achieved through the following formula using
exclusively excel's built in functions:

{=AVERAGE(SMALL(AJ27:AJ2027,AG28:AG128))} (as an ArrayFormula)

My purpose is only to test the functionality of user defined functions
like this, and the example provided here shows some limitations, or at
least the need for work-arounds in some instances.

Does anybody know what is causing this behavior, and how to mitigate
its effects? Is it really necessary/advisable to create arrays for
all unser-defined function arguments, or can something else be done to
help avoid this bizarre behavior.

Thanks, Nick