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.




Mike Middleton

Bob -
I was wondering if there is any way of forcing excel to output the
complete array? <

I don't think so. If it's a User Defined Function, it can only return values
to the worksheet cells where it resides. I think the only thing you can do
is encourage your users to select a very large range before array-entering
the function. As I recall, the extra cells will display the #N/A error code.

- Mike Middleton
Decision Analysis Add-ins for Excel

Bob Phillips

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.



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



Dave Peterson

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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question