Just so the OP's hopes aren't raised too much...

I think you missed the part where the user selected a 3x3 range and the OP still
wanted to return a 5x5 result.



Bob Phillips wrote:

Yes, it is quite simple.

First you count the rows (I am assuming it goes down) with

numRows = Application.Caller.Rows.Count

redim your temporary array to that size

Redim myArray (1 To numRows)

and then after your code loads the array, pad it like so

For i = current_last_value_index to NumRows

myArray(i) = ""
Next i

and load the array into the return value

myFunction = Aplication.Transpose(myArray)

If it has columns as well you will need to handle that in the Redim and with
an inner loop filling in the extra columns for each extra row.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Bob" wrote in message
...
Hi Everyone:

In excel, I am writing a function that returns an array, for example a
matrix. This function is called from the formula bar of the spreadsheet,
and returns the results to the spreadsheet. However, the array only fills
the cells that the user has selected prior to typing the name of the
function, its arguments and finally hitting Ctrl-Shift-Enter. I was
wondering if there is any way of forcing excel to output the complete
array? For example, if the user has selected 3x3 cells, but my output is
5x5, for my function to show the result of all 5x5 elements?

Thanks for all your help.

Bob


--

Dave Peterson