Converting Variant types in Excel VBA

As part of an Excel project, I needed to write some code that converted Variants in a reliable way.

The project involved calling a stored procedure that took parameters of many different types which were provided via cells in a spreadsheet. An Excel spreadsheet cell can contain values in a variety of different data types. When you access a cell value with VBA code, it comes to wrapped in a Variant type. Essentially, I needed to be able to convert data from a very forgiving type system (spreadsheet cells) to a very strictly typed system (a stored procedure parameter in a SQL Server database). And since the data was being supplied by non-technical end-users, I had to be sure that unusual Variant types didn't cause issues with data integrity or summon the dreaded VBA error dialog box. For example, the Variant type vbError, which is used by error cell values such as #NA or #NAME, is infamous for causing problems with VBA macros. You'll see why this is in a future post when I discuss Variant comparisons.

To solve the problem, my approach was to write a function that takes a Variant type (that could be any sub-type) and returns a Variant of the desired sub-type, converting it if possible, choosing a logical substitution value if not, and only raising an error when there is no other option.

In preparation, I wrote some code to build a Variant type conversion chart, which shows whether a subtype can be converted to any other subtype, and any errors that may be generated when the conversion is attempted.

Most of the results in this chart are obvious. It's to be expected that just about every type can be converted to a string or that numbers will sometimes overflow when converting to narrower numeric types. Some results are surprising, such as the inability to convert a vbError to a vbDate or a vbDecimal, even though it can be converted to just about every other type.

The conversions for vbEmpty and vbNull are instructive. The Empty value in VBA represents the default value, so it makes sense that it can be converted into anything else. When you convert Empty to a subtype, it turns into the default value for that subtype: for example, an empty string or the integer 0. The Null value, on the other hand, is intended by VB's designers to be an "intentionally missing" value, and so it's a mistake to coerce it into a data containing type. Thankfully, a spreadsheet cell cannot contain a null value. Null values behave similarly as in SQL in comparisons, and Null is considered Falsey in a VBA If statement.

Here is the variant conversion function that I ended up writing. Remember, the intention was to convert a variant value from any subtype to a defined variant subtype, and to be as forgiving as possible in the process.

Const CustomError = 2742

' Convert Variant [x] to subtype [rtype], returning [if_null] if conversion is
' impossible, otherwise returns a converted Variant value.
' Additional parameters are values that should be treated as null.
Public Function VConvert(rtype As VbVarType, _
                         x As Variant, _
                         if_null As Variant, _
                         ParamArray nullable_values() As Variant) As Variant
    Dim result As Variant

    ' Raise an error if provided with an unsupported type.
    Select Case rtype
        Case vbEmpty, vbNull, vbInteger, vbLong, vbSingle, vbDouble, _
            vbCurrency, vbDate, vbString, vbError, vbBoolean, vbVariant, _
            vbDecimal, vbByte
            ' Guard against Case Else if a valid type is provided.

        Case vbObject, vbDataObject, vbLongLong, vbUserDefinedType, vbArray
            Err.Raise CustomError + 1, , "Invalid result type: " & rtype

        Case Else
            Err.Raise CustomError + 1, , "Unknown result type: " & rtype
    End Select
    
    If VarType(x) = vbString Then
        result = Trim(x)
    Else
        result = x
    End If
    
    If ArraySearch(nullable_values, x) Then
        result = if_null
    ElseIf IsEmpty(result) Or IsNull(result) Or result = vbNullString Then
        result = if_null
    Else
        ' Attempt conversion and return if_null if an error is raised.
        On Error GoTo ErrHandler
        Select Case rtype
            Case vbEmpty: result = Empty
            Case vbNull: result = Null
            Case vbInteger: result = CInt(result)
            Case vbLong: result = CLng(result)
            Case vbSingle: result = CSng(result)
            Case vbDouble: result = CDbl(result)
            Case vbCurrency: result = CCur(result)
            
            Case vbDate
                If IsDate(result) Then
                    result = DateValue(CDate(result))
                Else
                    result = if_null
                End If
            
            Case vbString: result = CStr(result)
            Case vbError: result = CVErr(result)
            Case vbBoolean: result = CBool(result)
            Case vbVariant: result = CVar(result)
            Case vbDecimal: result = CDec(result)
            Case vbByte: result = CByte(result)
        End Select
        On Error GoTo 0
    End If
        
    VConvert = result
    Exit Function

ErrHandler:
    VConvert = if_null
End Function

And here are the unit tests for this function.

Private Sub TestVConvert()
    Dim vDate As Variant
    Dim vResult As Variant
    
    vDate = DateSerial(1982, 5, 22) + TimeSerial(5, 30, 0)
    
    ' Test time truncation
    AssertEquals VConvert(vbDate, vDate, Null), #5/22/1982#
    
    ' Test conversion of vbDate to vbString
    AssertEquals VConvert(vbString, vDate, Null), "5/22/1982 5:30:00 AM"
    
    ' Test nullable ParamArray
    AssertIsNull VConvert(vbString, "Hello", Null, "a", "b", "c", "Hello", "d")
    
    ' Test conversion that causes overflow
    AssertEquals VConvert(vbInteger, 999999, -1), -1
    
    ' Test that the overflow Err was cleared
    AssertEquals Err.Number, 0&
    
    ' Test vbLong to vbInteger regular conversion
    AssertEquals VConvert(vbInteger, 100&, -1, 99.9), 100
    
    ' Test string trimming during conversion
    AssertEquals VConvert(vbString, "   abcd  ", Null), "abcd"
    
    ' Test number conversion with untrimmed string
    AssertEquals VConvert(vbDecimal, "   42.25 ", Null), CDec(42.25)
    
    ' Test that conversions retain type of is_null parameter
    AssertType VConvert(vbDate, "Not a date", 42&), "Long"
    
    ' Test that Nulls get converted to is_null
    AssertEquals VConvert(vbInteger, Null, -1), -1
    
    ' Test that Emptys get converted to is_null
    AssertEquals VConvert(vbLong, Empty, 50&), CLng(50)
    
    ' Test that vbNullStrings get converted to is_null
    AssertEquals VConvert(vbString, "", "[empty]"), "[empty]"
    
    ' Test that converting to invalid type raises error
    On Error Resume Next
    vResult = VConvert(vbObject, vStr, Null)
    AssertEquals Err.Number, CustomError + 1
    Err.Clear
    On Error GoTo 0
End Sub

You may have noticed a call to an unimplemented function called "ArraySearch". VBA provides no built-in function for searching an array, so a robust implementation should be a part of any serious VBA programmer's toolbox. Implementing ArraySearch in a way that intelligently handles heterogeneous arrays of multiple subtypes without producing errors or incorrect result is not trivial and will be a topic for a future post.