EVALUATE method returns zero

[Rob]

I have a UDF which uses EVALUATE ( )

If I take the contents of the bracket and put it into a cell then Excel
calculates the correct answer, but I get zero from Evaluate.

The contents of the bracket is a SUMPRODUCT formula.

 

[Niek Otten]

Hi Rob,

<I take the contents of the bracket and put it into a cell then Excel calculates the correct answer>

The way one refers to cells in a worksheet differs from how it's done in VBA.
What's your formula?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a UDF which uses EVALUATE ( )
|
| If I take the contents of the bracket and put it into a cell then Excel
| calculates the correct answer, but I get zero from Evaluate.
|
| The contents of the bracket is a SUMPRODUCT formula.
|
|

 

[Rob]

See below from the Imediate window. If I put the formula onto my sheet it
works, but I get the error when I try to evaluate the statement.


?"=SUMPRODUCT((Ex_Sales_Date=""" & SalesDate & """*1)*1,
(Ex_Sales_Yield1=""" & Yield_1 & """)*1,(Ex_Sales_Yield2=""" & Yield_2 &
""")*1,(Ex_Sales_Yield3=""" & Yield_3 & """)*1,Ex_Sales_Volume)"
=SUMPRODUCT((Ex_Sales_Date="27/01/2008"*1)*1,
(Ex_Sales_Yield1="")*1,(Ex_Sales_Yield2="")*1,(Ex_Sales_Yield3="")*1,Ex_Sales_Volume)

?application.Evaluate("=SUMPRODUCT((Ex_Sales_Date=""" & SalesDate &
"""*1)*1, (Ex_Sales_Yield1=""" & Yield_1 & """)*1,(Ex_Sales_Yield2=""" &
Yield_2 & """)*1,(Ex_Sales_Yield3=""" & Yield_3 & """)*1,Ex_Sales_Volume)")
Error 2015