Passing Array Formula result to a variable in VBA (Leo)

[Leo]

Hi,
We know that for putting an arrya formula we use "Range.formulaArray"
property.
But what about if I need to use an array formula result in the context of my
code (like myVar =workbook.formula) and passing it to my variable, without
putting it on a cell: just like MyVar or myFunction = arrayformula(sth).
Is there any way?
please help.

 

[Bob Phillips]

myVar = Activesheet.Evaluate(the_array_formula)

--
HTH

Bob

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

 

[Leo]

thank you so much for your reply, it did great help!
after a 2 or 3 times of error I managed to run this code with no error;
Public Sub MySub()
Dim myVar As Long
myVar =
Application.Evaluate("=SUM(IF((Sheet1!$A$2:$A$2000=""P07"")*(Sheet1!$H$2:$H$2000=200707),Sheet1!$U$2:$U$2000,0))")
Debug.Print myVar
End Sub

the point was that it should be used withouth the symbol "{}"
--
Thans & Best regards
Leo, InfoSeeker

myVar = Activesheet.Evaluate(the_array_formula)

--
HTH

Bob

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