Variant data type in VBA and positive uses of it

Question

There's lots of stuff on Google regarding the variant data type. Mostly, they say things like "Avoid using it too much and here's why" or "It can hold any type of data", which things I understand. But I still don't fully understand when to use them. Can someone concisely explain and, more importantly, give examples of best uses of the variant data type (maybe even an example of passing a variant to a function as opposed to an explicitly declared variable)?

Answer 1

"...an example of passing a variant to a function as opposed to an explicitly declared variable"

Joel Spolsky mentions one form of this here, but I find it incredibly useful to simulate method overloading by using Variant-typed parameters.

For example, take a look at this macro that adds worksheets to a workbook:

Sub addSheets(wb As Workbook, ByVal wsNames As Variant)
'Adds one or more worksheets (wsNames) to a workbook (wb)

    Dim ws As Worksheet
    Dim i As Long

    If Not IsArray(wsNames) Then
        wsNames = Array(wsNames)
    End If

    For Each ws In wb.Sheets
        For i = LBound(wsNames) To UBound(wsNames)
            If ws.Name = wsNames(i) Then
                MsgBox "Error: Sheet " & wsNames(i) & " already exists in workbook"
                Stop
                Exit Sub
            End If
        Next
    Next

    For i = LBound(wsNames) To UBound(wsNames)
        Dim newWS As Worksheet
        Set newWS = wb.Worksheets.Add
        newWS.Name = wsNames(i)
    Next

End Sub

Because it will accept either a String or an array of Strings for the wsNames parameter, both of the following calls are valid:

'Adds sheet called "TestWS1" to the active workbook
Call addSheets(ActiveWorkbook,"TestWS1")
'Adds sheets called "TestWS2" and "TestWS3" to the active workbook 
Call addSheets(ActiveWorkbook, Array("TestWS2","TestWS3"))

By using variants and the "IsArray" method (and similar ones like "IsNumeric","IsDate", etc.) to force individual methods to accept multiple data types for a single parameter, you can avoid having to create separate methods to accomplish similar tasks (ie "addSheet" for adding one WS, "addSheets" for adding multiple).

Answer 2

Variant type take more space in memory than any other variable (here you'll find the code for the macro, to test, I just added the routine for variant)

There are some other factors that may be in play for this, it's a very subjective topic as such this is my personal experience:
1. A good code would have to be explicitly explained, using variant may lead to confusion in what should be defined in first place.
2. Variant may not get the real property of the object, thus, it will take you more time to code and you may not see the real properties for the object IE:

Dim myRange as Range
Set myRange = Range("A1:A10")
myRange. 'I can see the properties of the object!
Dim myRange as Variant
Set myRange = Range("A1:A10")
myRange. 'I can't see what properties this may have in this context!

I wanted to give the real context on why to avoid them -I think we both agree on that-.
Now, when to use them? Well this may be subject to discussion as well, but, I have found them useful when you can't control the input.
How this may happen?
Example 1: A TextBox Value let to user input and I don't want to let the user type words, only numbers (whenever you type a word should block it), but, TextBox alone can't block that, so, what you should do?

Private Sub TextBox_Value_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim ChrPressed As Variant
    ChrPressed = Chr(KeyAscii)
    Select Case ChrPressed
        Case IsNumeric(ChrPressed)
        Me.TextBox_Value.Value = Me.TextBox_Value.Value & ChrPressed
        KeyAscii = 0
        Case "0"
        Me.TextBox_Value.Value = Me.TextBox_Value.Value & ChrPressed
        KeyAscii = 0
    End Select
End Sub

Where ChrPressed is Variant since user could press word keys still but we are going to define what is pressed and doing the research inside our code -other approach may lead to error debugger to user making our experience not that smooth-.
2. If you don't want to add the proper references to avoid late or early binding but, you know those functions are allowed in the variable itself, this helps as well to avoid some workarounds you may have to do in the future related to late,early binding (though I haven't faced an issue if I do it that way):

Sub ImportFile()
Dim MyFile As Variant
MyFile = Application.GetOpenFilename("Import File*.CSV", Title:="CSV Data")
    If MyFile = False ...

3. Arrays that can have numerous data and you want to control them as such

Dim ArrayForTest() as Variant
Select Case ArrayForTest(ArrayElement)
Case IsNumeric(ArrayForTest(ArrayElement))
Case IsString(ArrayForTest(ArrayElement)) 'Just representative not the real way to do so

4. Elements within an element that you haven't found a way to properly call them and you'll learn later on or don't really have an impact in performance related to the proper way to call them.

Sub Graphs_makegraph(XValues As Variant, ChartStyle...
ActiveSheet.Shapes.AddChart2(ChartTypeNumber, ChartStyle).Select
...
ActiveChart.FullSeriesCollection(1).XValues = XValues

Most programmers don't like this approach because you are letting Excel "handle the things", a good coding practice is that you should have everything defined as OOP defines, but, I find that excel versatility in doing that right speeds up the solution and save us a few .class/interfaces then back again to the main class that's calling that we would have to do other way in MVP Programming. As an OT: I mean, if we want to get that strict we would need to program in asm language to avoid any interference of compilers our original intention.

Answer 3

In general, always use the smallest-memory variable that you can. Variant is the biggest, so you should only use it when there isn't a better typed one. Here are some examples

Array()

The Array function returns a variant containing an array. The only way you can use Array is to assign to a variant.

vArr = Array(1, "a", True)

Assigning Arrays to Ranges

If you have to write a bunch of data to cells, it's way faster to put them in an array and write them all at once rather than one cell at a time. If your data is all the same data type, use a properly typed array. But if it's not all the same data, you probably need to use an array of Variants.

Dim vaWrite(1 To 2, 1 To 2) As Variant

vaWrite(1, 1) = 1
vaWrite(1, 2) = "Bob"
vaWrite(2, 1) = 2
vaWrite(2, 2) = "Tim"

Range("A1").Resize(2, 2).Value = vaWrite

And going the other way - range to array requires a Variant regardless of the data types in the cells

vArr = Range("A1:C10").Value

Worksheet Functions

Some (all?) worksheet functions can return errors as well as whatever their normal values are. The only data type that can hold, for example, a Double and an Error is a Variant.

vRes = Application.WorksheetFunction.Match(...)

Those are three examples I can think of. There may be more.