Programming Array Formulas using VBA

Posted by Vikram Peddakotla on January 10, 2001 8:16 PM

How can I simulate the creation of an array formula thru VBA code?

Situation:
My function needs to output an array of values and each cell of the output should have the formula
{=MyFunction(B10,A10,C15)}, so that if I change any parameter of my function, (for example: If I change B10 to B12) each value of the array should automatically recalculate it's value as well as modify it's formula to reflect the change.

I am working on Excel 97.

Posted by Robert on January 11, 2001 6:09 PM

What you really want is unclear to me, but with this you might get it to work the way you want:

Range has formula property which you can set:

Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"

Posted by Vikram Peddakotla on January 11, 2001 7:36 PM

Let me try again.
To put it in very simple terms, how can I get a function written in VBA to return an array of values to the Excel Spreadsheet?

Posted by Robert on January 12, 2001 4:45 AM

Are you looking for this?

Sub try_array()
Dim data As Variant
data = Range("A2:A6").Value
Range("C2:C6").Value = data
End Sub

ok?