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:
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 ...