Dutch Gemini's Weblog Pages

August 7, 2009

Error 2015 using Application.Evaluate in Excel VBA

Filed under: Excel,Programming,VBA — dutchgemini @ 2:58 pm

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

Advertisements

3 Comments »

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

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

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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: