VBA/Excel/Access/Word/Data Type/Variant

Add Variant integer in string to another Variant integer in string

 
Sub dimDemo()
    Dim varAnyThing As Variant
    varAnyThing = "31"
    varAnyThing = varAnyThing + "10"
End Sub

Add Variant integer to Variant string

 
Sub asVar()
    Dim varAnyThing As Variant
    varAnyThing = 31
    varAnyThing = varAnyThing & " is half of 62"
End Sub

Add Variant string to Variant integer

 
Sub variantDemo()
    Dim varAnyThing As Variant
    varAnyThing = "31"
    varAnyThing = varAnyThing + 10
End Sub

Assign difference type variable to Variant variable

 
Sub var()
    Dim varAnyThing As Variant
    varAnyThing = 31
    varAnyThing = "Any old thing"
End Sub

A Variant allows VBA to make its own decision as to what type of data it is holding

 
Sub varSun()
    Dim varMyData As Variant
    varMyData = "This is a Variant"
    Debug.Print varMyData
    varMyData = 12
    Debug.Print varMyData
End Sub

A Variant allows VBA to make its own decision as to what type of data it is holding: string and integer

 
Sub var()
    Dim varMyData as Variant
    varMyData = "This is a Variant"
    varMyData = 12
End Sub

Determining the Type of a Variant

 
Value     Variant type
0     Empty (unitialized)
1     Null (no valid data)
2     Integer
3     Long Integer
4     Single
5     Double
6     Currency
7     Date
8     String
9     Object
10     Error value
11     Boolean
12     Variant (only used with arrays of variants)
13     Data access object
14     Decimal value
17     Byte
36     User Defined Type
8192           Array
Sub VariantExample()
    Dim varAnyThing As Variant
    
    varAnyThing = 12.345
    Debug.Print VarType(varAnyThing)
    
    varAnyThing = 12.345
    varAnyThing = varAnyThing & " is a number"
    Debug.Print VarType(varAnyThing)
     
     varAnyThing = 12.345
     varAnyThing = varAnyThing + "10"
     Debug.Print VarType(varAnyThing)
     
     varAnyThing = 12345
     Debug.Print VarType(varAnyThing)
     
     varAnyThing = 123456
     Debug.Print VarType(varAnyThing)
End Sub

Get the variable type for Variant

 
Sub VariantExample()
    Dim varAnyThing     As Variant
    varAnyThing = 12.345
    Debug.Print VarType(varAnyThing)
    
    varAnyThing = 12.345
    varAnyThing = varAnyThing & " is a number"
    Debug.Print VarType(varAnyThing)
    
    varAnyThing = 12.345
    varAnyThing = varAnyThing + "10"
    Debug.Print VarType(varAnyThing)
    
    varAnyThing = 12345
    Debug.Print VarType(varAnyThing)
    
    varAnyThing = 123456
    Debug.Print VarType(varAnyThing)
End Sub

If you simply declare a variable with a Dim statement and leave off the type-declaration keyword or character, the variable is a variant

 
Sub var()
    Dim myVar
End Sub

One of the few instances in which you have no choice for using a Variant:

 
Sub fewInstance()
    Dim varArray As Variant
    varArray = Array("Element1", 2, "Another string")
End Sub

Variant string

 
Sub VariantStringTest()
    Dim varFirstName    As Variant
    Dim varLastName     As Variant
    Dim varFullName     As Variant
    varFirstName = "Janine"
    varLastName = Null
    varFullName = varFirstName + varLastName
    Debug.Print varFullName
End Sub