excel vba Evaluate() on string with IIF()

Question

In excel VBA, my project has formula saved in a table. Through VBA, this formula is retireved into a String variable tempFormula the elements are are replaced with values from relevant source cells.

For Example if the formula stored is (Actual/Total) * 100 and the terms Actualand Totalare repalce with numberical values say 80 and 120, the tempFormula would look like this (80/120)* 100.

On executing Application.Evaluate(tempFormula) gives the desird result, for this example it would be 66.67.

I am facing error when the String variable tempFormula is develops into an expression after replacements to "IIf("Yes" = "Yes", 20, 0) + IIf("Yes" = "Yes", 20, 0)". I mean to say that the VBA throws error when it reaches the statement

tempResult = evaluate("IIf("Yes" = "Yes", 20, 0) + IIf("Yes" = "Yes", 20, 0)")

I even tried

tempResult = evaluate("IIf("Yes" = "Yes", 20, 0) + IIf("Yes" = "Yes", 20, 0)").Formula

Can anyone help me out with a solution to this problem. I do not want to use a temporary cell elsewhere in the workbook to evaluate and use the result, Unless that's the only workaround to this issue.

Answer 1

If you use Evaluate, you can't use the IFF. Try it yourself:

tempResult = Evaluate("If("Yes" = "Yes", 20, 0) + If("Yes" = "Yes", 20, 0)")

In VBA you could use:

Dim i
i = IIF("Test" = "Test", 20, 0) + IIF("TEST" = "TEST1", 0, 30)
Debug.Print i 'i would give 50 here

but IIF is not a normal excel formula so evaluate throws an error there.
My apologies for the confusion with the replacement part but I hope it's clear now to change the IIF to IF in this situation.