What is a VBA Variant? And Why Would You Ever Use One?
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.