Can an array formula be used in a user defined function?

[stratasmith]

Here's what I need to do:

I have a rectangular array with values for a number of variable
(columns) in a number of samples (rows).

In another (square) array, I want to generate the variance of th
natural log of the ratios of all possible pairs of variables. Thus
cell mn of the output array has the variance of the logs of the ratio
of every pair of values in row m and row n of the input array. I ca
do this for an individual cell of the output array with an arra
formula, but as there are about 35 columns of variables there would b
over a thousand array formulas to type in!. I can't use Copy an
Paste, because of the way column n of the input becomes row n of th
output.

I could probably do it in a user defined function, but I'm not sure i
I can use an array formula in a udf, and someone may be able to thin
up a more efficient way of doing what I want.

Any ideas?

SS

 

[Charles Williams]

You can use an excel array formula in a UDF if you use Application.Evaluate
or Worksheet.Evaluate to evaluate it.

It may well be faster to write a UDF that reads the rectangular range into
an array and then calculates the square output array and returns it when the
UDF is entered as an array formula.
The only problem would be that Excel limits you to returning a max of 5461
elements with an array formula UDF prior to Excel 2002 (well unless you want
to start making registry changes for Excel 2000).

Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[stratasmith]

I wasn't familiar with application.evaluate/worksheet.evaluate but I'l
look into them.

It's also occurred to me that I could do the whole thing with
subroutine, looping through the cells of the output array (or at least
those above the leading diagonal, as it's going to be mirrored in th
other half), but this might run up against the same limitation that yo
mentioned. (I'm using Excel 97, by the way.)

Any other thoughts?

SS

 

[Charles Williams]

A Subroutine does not have the same 5461 limits as a worksheet function.

If you can use it as a sub attached to a button or something then I would
recommend using a sub.
VBA is surprisingly fast at handling arrays and arithmetic so this might be
the right approach.

Just make sure you read the input range in a single statement into a single
variant variable (not an array of variants) and process the results into a
square array of doubles:
something like this

Dim vArrIn as variant
dim j as long
dim k as long
dim dAnsa(100,100) as double
'
' setup excel environment
'
application.calculation=xlmanual
application.screenupdating=false
'
' get the input data
'
vArrIn =worksheets("sheet1").range("A1:IV10000")
'
' process input data
'
for j=1 to 10000
for k=1 to 256

...... =vArrIn(j,k)

dAnsa(jj,kk)= ...

next k
next j
'
' put results back to worksheet
'
worksheets("sheet2").range("z5").resize(100,100)=dAnsa
'
' restore excel environment
'
application.calculation=xlautomatic
application.screenupdating=true

Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com