Excel vba and arrayformula

Question

I am trying to get values ​​from an array formula in VBA as an array. A simple example: I have a cell (say D1) that has an array formula, for example, = A1: A10 * B1: B10 when I select this in the spreadsheet and press F9, I get an array of 10 numbers, say = {5; 12; fifteen; 24; 25; 24; 42; 40; 54; 70}

I want to store these values ​​in a VBA array: a (0) = 5, a (1) = 12, a (3) = 15, etc .; you get the idea.

I tried to find the answer to this question, but could not find anything suitable even on MSFT. There are many answers on how to move from VBA to the range of worksheets (I know this), but not so. I looked at trying to do this using ParamArray (the number of elements will not be fixed), assigning it directly to an unlimited array and through EVALUATE (range), but I can not get them to work. I feel like I'm missing something obvious.

Answer 1

Not sure what you have already tried. But it Evaluateshould work.

If I have the following:

The code:

Sub getArrayFormulaResult()

 sFormula = Range("D1").FormulaArray

 aResult = Evaluate(sFormula)

 MsgBox Join(Application.Transpose(aResult), ",")

End Sub

will result in:

Answer 2

try it

Dim var as variant
var=Worksheets("MySheet").Evaluate(Worksheets("MySheet").Range("D1").formula)

, Worksheet.Evaluate, , , Mysheet . 2_D ( )