Since a while I am using Application.Evaluate because of the fact that you can craft formulas as strings and have Excel VBA compute them for you.
Only recently I discovered that some of the cells in a workbook contained #VALUE? instead of a sound value. After some investigation I found out that, without raising any error at application level (in VBA), the Application.Evaluate function failed consistently with this error whenever a particular condition occurred.
This condition is related to limits in the underlying software layer of Excel, the one that runs Application.Evaluate.
Apparently this particular error occurs when the parsed function returns a string value having a length above 255 characters. As long as the length of the string is less than or equal to 255 characters, the return value is the desired string. Above 255 characters, the function “crashes”.
In my case, I was not immediately attended on this error because I used a Variant to store the returned value from Application.Evaluate (sample code):
Dim vValue As Variant
vValue = Application.Evaluate("GetData()") ' Error 2015 saved in vValue if GetData returns 255+ characters
ActiveCell.Value = vValue
If I would have used a String to store the value, I would probably have gotten a “Runtime Error 13 – Type Mismatch” with Excel booming out of my routine without any debugging possibility (sample code):
Dim sValue As String
sValue = Application.Evaluate("GetData()") ' Run-time error if GetData() returns 255+ characters
ActiveCell.Value = sValue
To catch problems, I now completed code with an If statement (sample code):
Dim vValue As Variant
vValue = Application.Evaluate("GetData()")
If (VBA.VarType(vValue) = vbError) Then
ActiveCell.Value = "String Overflow on GetData()"
Else
ActiveCell.Value = vValue
End If
There seem to be more Excel functions that suffer from this 255 character limit, but could not establish which. For the moment, I will apply some workarounds until it gets solved by Microsoft.
Dutch
Dutch Gemini's Weblog Pages 
Thanks for this, I was passing through a #value error as I was resetting a combo control Cell link to 0, by defining the field as String trapped the error allowing the “on error goto XXXX” to work as required
Comment by John Allott — June 21, 2012 @ 11:42 pm |
The error trapping in your post was very helpful for me, although for a different error type.
This line of code was my aid: If (VBA.VarType(vValue) = vbError) Then
I wanted to identify with VBA the first cell in a column that returns “” constant (double quotes) by using Application.Evaluate on a Match function. When no double quotes are found Application.Evaluate returns Error 2042.
Small tip supporting supporting what is mentioned in regards to the variable deceleration – To be able to store Error 2042 in the variable, it should be declared as variant (i.e. Dim LastRow as Variant). This way the code won’t run into an error when Application.Evaluate returns Error 2042.
Comment by Gadi Bizinyan — March 26, 2014 @ 1:56 am |
One (probably really common) mistake I came across that gave me “error 2015” and “type mismatch”: If the string you are evaluating only has row and column as cell reference but doesn’t specify sheet, then you would get this error if the sheet was not previously activated.
Simply add worksheets(“yourSheet”).activate before each “evaluate” to make sure.
Comment by Pietz — June 24, 2016 @ 2:07 am |
Hola, hay alguna alternativa para la funcion “evaluate”?? Gracias por la explicación, me ayudo mucho a ver mi error!!
Comment by Claudia — July 4, 2019 @ 4:07 am |
The Evaluate() function allows in practice to use Excel as a “compile and execute on demand” feature. For instance, you can use it in the form of Application.Evaluate(“1+1”) or Application.Evaluate(“ABS(-1)”). A nice example could be the creation of a scientific calculator where you can write arithmetic functions yourself and have them computed. The alternative would be retrieving the result directly from the function or calculation. This, however, requires that your project contains a hard-copy of any UDF called (native VBA functions are provided by Office). Application.Evaluate() will not crash your project, only return an error in case it cannot understand the input.
Comment by dutchgemini — July 4, 2019 @ 7:53 am |