VB & VBA in a Nutshell: The Language

3.3. The Variant

VBA contains a special data type, the Variant. Internally, the Variant is highly complex, but it's also extremely easy to use. The Variant is the default data type of VBA, so the following code casts myVar as a variant:

Dim myVar

The Variant data type allows you to use a variable with any of the intrinsic VBA data types, automatically working out what is the closest data type to the value you are assigning. When you consider the amount of processing required to determine what data type should be used for an abstract value, it's a testament to the VB development team at Microsoft that the Variant is as quick as it is. However, there is a slight performance hit when using both variant data and functions that return variant data, which we discuss later in this chapter.

Another drawback to using variant data is that your code becomes at best horrible to read, and at worst unreadable! To illustrate, consider two versions of the same function, the first written exclusively with variants, the second using strong typing:

Private Function GoodStuff(vAnything, vSomething, _
                           vSomethingElse)

If vAnything > 1 And vSomething > "" Then
   GoodStuff = vAnything * vSomethingElse
Else
   GoodStuff = vAnything + 10
End If

End Function

Private Function GoodStuff(iAnything As Integer, _
                           sSomething As String, _
                           iSomethingElse As Integer) _
                          As Integer
If iAnything > 1 And sSomething > "" Then
   GoodStuff = iAnything * iSomethingElse
Else
   GoodStuff = iAnything + 10
End If

End Function

I know ...