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.
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.