Excel Hangs reliably when executing an UDF
 

I created a UDF to perform a regression on two ranges, X and Y that
reliably hangs Excel 2003 and 2007: both ranges are copied into arrays
of length N, and I create an array of intermediate results of length
N*(N-1)/2 as follows

Public Function TheilSenRegression(x As Range, y As Range)
Dim xx() As Double, yy() As Double, slopes() As Double
'compute N and Nc2 = N(N-1)/2
ReDim xx(1 to N) as double 'copy x into xx
ReDim yy(1 to N) as double 'copy y into yy
ReDim slopes(1 to Nc2) as double 'copy intermediate results into
slopes
..
..
TheilSenSlope = Application.WorksheetFunction.Median(slopes)

TheilSenRegression = Array(TheilSenSlope, TheilSenIntercept)

End Function


The function works like a charm when I call it less than about 10
times in a spreadsheet - X and Y are of length 100, so that nC2 is
just under 5000. Call it more often and Excel hangs reliably. My gut
instinct is that Excel gets into trouble allocating memory for the
array slope() or that calling Median causes a problem, but have no
thoughts on how to fix it. Any insights would be greatly appreciated

Sincerely

Thomas Philips

Excel Hangs reliably when executing an UDF
 

I can't say what is causing your problem, but here are a few things to
try:

1. add an errorhandler to the function

e.g. make the first line in the function
on error goto errorhandler
and the last lines in the function
exit function
errorhandler:
msgbox "error!"


2. there appear to be some variables in the function that are grabbing
their values from somewhere else - the values aren't passed to the
function. For example, where does N come from? Pass those values to
the function (like you do x and y).

Finally, I don't understand how this function is copying the values
from the ranges into the arrays. Is part of the code missing?

David Sauder

On Mar 28, 3:34 pm, wrote:
I created a UDF to perform a regression on two ranges, X and Y that
reliably hangs Excel 2003 and 2007: both ranges are copied into arrays
of length N, and I create an array of intermediate results of length
N*(N-1)/2 as follows

Public Function TheilSenRegression(x As Range, y As Range)
Dim xx() As Double, yy() As Double, slopes() As Double
'compute N and Nc2 = N(N-1)/2
ReDim xx(1 to N) as double 'copy x into xx
ReDim yy(1 to N) as double 'copy y into yy
ReDim slopes(1 to Nc2) as double 'copy intermediate results into
slopes
.
.
TheilSenSlope = Application.WorksheetFunction.Median(slopes)

TheilSenRegression = Array(TheilSenSlope, TheilSenIntercept)

End Function

The function works like a charm when I call it less than about 10
times in a spreadsheet - X and Y are of length 100, so that nC2 is
just under 5000. Call it more often and Excel hangs reliably. My gut
instinct is that Excel gets into trouble allocating memory for the
array slope() or that calling Median causes a problem, but have no
thoughts on how to fix it. Any insights would be greatly appreciated

Sincerely

Thomas Philips