Arrays

[Bob]

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

 

[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
http://www.DecisionToolworks.com
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.


--
HTH

Bob

(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.