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!