(Solved) – VBA Evaluate doesn’t seem to work without manual intervention the first time

  • by

I am facing a strange issue with Evaluate. The strFormula is a string with a formula. While executing this code, i get the Run-time error 13: Type Mismatch. I checked that the formula within strFormula is valid (I copy pasted it in an excel cell & it evaluates and returns TRUE/FALSE). Also the length of the string is not exceeding 255 characters as i believe Evaluate can only handle upto 255 chars.

'Reading the formula template from a cell
strFormula = Trim(ThisWorkbook.Sheets("Validation").Cells(1, 3))

'Replacing the R# in the formula with the Row# to build the actual formula to evaluate
strFormula = Replace(strFormula, "R#", iRowCounter)

blnTest = Evaluate(strFormula)

Now here is the strange part – when i get the Type Mismatch error, if i click on Debug and then if i go to Formula tab & click on “Calculate Now” or “Calculate Sheet” OR if i type in any formula (e.g. = 1 1) in any cell in the excel and then hit an F8 or F5 to continue execution in debug mode, then the Evaluate works fine! (it returns TRUE/FALSE & execution continues). I checked that Calculation Option is set to Automatic and in the code i’m not modifying it.

Any idea on why Evaluate is not working in the first instance? I tried to put some dummy (as shown below) Evaluate statements before this to see if that helps, but those evaluate statements worked fine & i got the Type Mismatch error on my statement. I tried to put an ActiveSheet.Calculate before the Evaluate statement to force Excel to calculate before executing the actual evaluate but that didnt help either.

temp = Evaluate("=1 * 1")
temp = Evaluate("=1   1")
blnTest = Evaluate(strFormula)

Any pointers on how to resolve this will be great!

Leave a Reply

Your email address will not be published. Required fields are marked *