Excel’s cell formula is not always a formula

Before using the cell’s formula string, you should always test if Excel considers it a formula. Sounds silly, but I’ve seen a lot of examples about manipulating Excel cell formulas where this simple rule is not considered.

As a matter of fact, Excel considers the formula in a cell a formula when the first character in the formula string is the equal sign “=”.

To be sure you are dealing with a formula, Do:

• test for Cell.HasFormula (True there is one, False there is none), or
• test if the first character in the formula string: VBA.Left$(Cell.Formula, 1) = “=”

but Do Not:

• test for VBA.Len(Cell.HasFormula) > 0

Why? Simply testing if the length of the formula is greater than zero is not enough. In fact, when reading the formula string from a cell without a formula, Excel does not return an empty string -as one could expect- but a stringified value of the cell’s contents, in other words, it does indeed return non-zero length strings even when no formula is present.

In summary, test if you are in the presence of a formula (Cell.HasFormula is the quickest) before using the formula string.

Dutch

Excel’s Application.Evaluate() does not consider all data types

Continuing on my recent post on Application.Evaluate, I have found other differences that have led to mostly abandon this function in favour of a User Defined Function (UDF). My need was to evaluate portions of the cell’s formula string containing references to functions in an Add-In. I used Application.Evaluate() because it accepts a string with a valid command as input.

Apart from being limited by the parameter’s string limit of 255 characters -what I can accept, if documented even better- I was less happy with the fact that Application.Evaluate() does not support or return all the data types.

I presume this is because Application.Evaluate() is based on the same principles that Excel uses when dealing with the .Value2 property of a cell (this property doesn’t use the Currency and Date data types but returns values formatted as floating-point numbers using the Double data type).

I was using Application.Evaluate() in the following piece of code:

        {...}

        ' Evaluate the string and retrieve the value.
        '
        wFormulaValue = Application.Evaluate(wFormulaString)
        Select Case VBA.VarType(wFormulaValue)
        Case vbEmpty, vbNull
            ' Null string, set double quotes
            wFormulaValue = VBA.Chr$(34) & VBA.Chr$(34)
        Case vbDate
            ' Need to cast if not alone
            If (wFormulaIsNotAlone) _
            Then wFormulaValue = "DATE(" & VBA.Year(wFormulaValue) & "," & VBA.Month(wFormulaValue) & "," & VBA.Day(wFormulaValue) & ")"
        Case vbString
            ' Need to enclose in double quotes if not alone
            If (wFormulaIsNotAlone) _
            Then wFormulaValue = VBA.Chr$(34) & wFormulaValue & VBA.Chr$(34)
        Case vbBoolean
            ' Need to cast
            wFormulaValue = VBA.IIf(wFormulaValue, "TRUE()", "FALSE()")
        Case vbObject, vbVariant, vbDataObject, vbByte, vbUserDefinedType, vbArray
            ' Converted in N/A
            wFormulaValue = "NA()"
        Case vbError
            ' Oops, an error occurred parsing the formula string. To repair, we use the shown value.
            wFormulaValue = VBA.CStr(pCell.Value)
            ' Need to enclose in double quotes if not alone
            If (wFormulaIsNotAlone) _
            Then wFormulaValue = VBA.Chr$(34) & wFormulaValue & VBA.Chr$(34)

        Case Else
            ' Keep. Included here vbInteger, vbLong, vbSingle, vbDouble, vbDecimal, vbCurrency
        End Select

        {...}

You can imagine that if the value’s type obtained using VBA.VarType() is not properly identified, you end up in the wrong Case branch and the rest of your code will work with a different type of data.

Here’s a quick example with a UDF that returns a Variant. I am returning a variant of type Date. Drop it into a module in your workbook:

Public Function MyVariantDate() As Variant
    Dim dDay As Date
    dDay = Now
    MyVariantDate = dDay
End Function

In the immediate window (Ctrl+G) type:

? MyVariantDate()
31-08-2009 14:22:15
? VBA.TypeName(MyVariantDate())
Date
? VBA.TypeName(Application.Evaluate("MyVariantDate()"))
Double

The Date magically converted in a Double.

I have rewritten part of the code using Application.Evaluate() and have developed a UDF that does the job the way I need, maybe not so speedy but very accurately.

Dutch

Error 2015 using Application.Evaluate in Excel VBA

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