returning an array from a custom function
 

I have a custom function that returns five values as an
array. If I call the array function from a row of five
cells it works fine. But if I call the function from a
column of five cells, it returns the first value of the
return array five times. It seems to think that all
arrays are rows! I thought that an array would be a row
or column depending on circumstances. What rule am I
missing here, or is this an honest error in EXCEL?

Ron Davis
Mathematical Programming Services

returning an array from a custom function
 

Hi Ron,

It always returns a row, but repeats the row 5 times if you call it from a
column. Try calling it from a 5 by 5 range to see this.

So my array functions mostly return a two-dimensional array, even if one of
the dimensions is one long.
Other people like to use TRANSPOSE to switch the row to a column.


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

"Ron Davis" wrote in message
...
I have a custom function that returns five values as an
array. If I call the array function from a row of five
cells it works fine. But if I call the function from a
column of five cells, it returns the first value of the
return array five times. It seems to think that all
arrays are rows! I thought that an array would be a row
or column depending on circumstances. What rule am I
missing here, or is this an honest error in EXCEL?

Ron Davis
Mathematical Programming Services

returning an array from a custom function
 

Ron Davis wrote:

It seems to think that all
arrays are rows!

More likely, your function *defines* the output array as a horizontal
array. As was suggested, instead of finishing up your function with
something like

FunctionName = myArray, try
FunctionName = Application.Transpose(myArray)

Alan Beban


I have a custom function that returns five values as an
array. If I call the array function from a row of five
cells it works fine. But if I call the function from a
column of five cells, it returns the first value of the
return array five times. It seems to think that all
arrays are rows! I thought that an array would be a row
or column depending on circumstances. What rule am I
missing here, or is this an honest error in EXCEL?

Ron Davis
Mathematical Programming Services