Excel Array Formulas

[andrewhharmon]

I've created a simple formula that returns an array. This array is of
unkown number of rows and columns. Currently I enter the formula into
a cell, then drag and select a large numner of cells and use ctrl +
shift + enter to have the cells populate the array formula results.
The problem is that i don't know how far i have to drag. it is a lot
of trial and error. Is there anyway to have a function return an array
and populate it to the correct number of cells? Thanks for your help.

-Andrew

 

[Guest]

Use the formula to return the array to an array variable in VBA. Then you
can use Ubound and Lbound to determine how many cells in the worksheet should
be filled.

 

[Bob Phillips]

Here is an example for you to work on

Function myfunc()
Dim ary
Dim tmp
Dim rng As Range
Dim i As Long, j As Long

Set rng = Application.Caller
ReDim tmp(1 To rng.Rows.Count, 1 To rng.Columns.Count)
'<=== add your code to generate array of data
'the next line is just a simple example
ary = [{1,"a","1a";2,"b","2b";3,"c","3c"}]
For i = LBound(ary, 1) To UBound(ary, 1)
For j = LBound(ary, 2) To UBound(ary, 2)
tmp(i, j) = ary(i, j)
Next j
Next i
For i = LBound(tmp, 1) To UBound(tmp, 1)
For j = UBound(ary, 2) + 1 To UBound(tmp, 2)
tmp(i, j) = ""
Next j
Next i
For j = LBound(tmp, 2) To UBound(tmp, 2)
For i = UBound(ary, 1) + 1 To UBound(tmp, 1)
tmp(i, j) = ""
Next i
Next j
myfunc = tmp
End Function

--
---
HTH

Bob

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