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.
