A VBA Variant is a VBA variable that has not been declared to have a specific data type.  It can therefore hold any type of data that VBA variables are able to hold – be it text, numbers, dates, time or objects.  

Variants use more memory than variables in which the data type is specified, and therefore can slow down the execution of the program that you are running. 

If you have large procedures or complicated code, this speed can be extremely important in the efficient running of your program. So the rule of the thumb should be to always specify what data is going to be held in your variable by explicitly declaring them.

Why would I use a variant then?

There may be some occasions where you need to use a variant.  When you declare your variables, you are restricted to what type of data is stored in the variable – so if you declare an integer, for example, the data that is stored in that variable has to be between -32768 to 32767.  If the number that is going into that variable falls outside of that scope, your code will end up throwing an error.

This would be particularly annoying if you were importing data into Excel from a database using VBA code for example – some of the data may need ‘cleaning’ – a number may perhaps have a rogue letter in it and therefore if imported as a number, would end up breaking the program at that point.  Using a variant variable could solve this problem.

Sub Test1()
Dim ProductPrice As Currency
Dim Qty As Double
Dim TaxAmount as Currency
Dim FinalAmount as Currency

ProductPrice = "$5.00 "
Qty = "10"
TaxAmount = "$4.14"
FinalAmount = "$54.14"

Range("A1 ") = ProductPrice
Range("B1 ") = Qty
Range("C1 ") = TaxAmount
Range("D1 ") = FinalAmount

However, when you try to run this code, the following error appears.

This is due to the dollar signs that are in the data – the Product Price, Tax Amount and Final Amount have been declared as numbers – yet the data being populated into those variables contains a $ – therefore turning the number into a string – hence an error will occur.

However, if you amend your code…

Sub Test2()
Dim ProductPrice As Variant
Dim Qty As Variant
Dim TaxAmount as Variant
Dim FinalAmount as Variant

ProductPrice = "$5.00 "
Qty = "10"
TaxAmount = "$4.14"
FinalAmount = "$54.14"

Range("A1 ") = ProductPrice
Range("B1 ") = Qty
Range("C1 ") = TaxAmount
Range("D1 ") = FinalAmount

The correct information will then go into your Excel file:

If you look at the formula bar, you will see that Excel has brought in A1, C1 and D1 as numbers formatted for currency.

So even though you declared your variables as variants, Excel cleverly knew to bring them in as numbers.

Things to remember and to watch out for

Variant variables take more memory and slow down your program

The Variant data type is automatically specified by the type of data it takes in – so in the four variables we created, three of them would have been string variables and one would have been a number variable.

Variant variables take in any data – which might make your code more difficult to debug.

In summary, if you know what data you are going to be storing in your variable, a good rule to follow is to always EXPLICITLY declare your variables ie as a string, integer, double or single for example.  If you are worried about data being stored that it not of a specific type – then declare your variable IMPLICTLY using a VARIANT.