Dutch Gemini's Weblog Pages

August 31, 2009

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

Filed under: Excel,Programming,VBA — dutchgemini @ 1:45 pm

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())
? VBA.TypeName(Application.Evaluate("MyVariantDate()"))

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.



Leave a Comment »

No comments yet.

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

Create a free website or blog at WordPress.com.

%d bloggers like this: