VBA Data Types: How to Declare (Dim) and Set VBA Variables
A variable in programming is like a variable in math: it stands in the place of a value, and that value can change.
This is extremely useful when writing VBA code.
Declaring variables means telling Excel what data type will go into the variable 💡
So, when declaring variables it’s essential to know which VBA data type to choose for your variable.
Read on and learn why it’s so important to declare a variable, and how to actually do it.
Table of Contents
Variable declaration (VBA dim): How and why
In VBA, declaring has two steps:
- Set a name for the VBA variable. For example, you might give your numbers variable, the “quarterlySales” variable name.
- Set a type for the VBA variable. We’ll go over types in more detail in the next section. For now, just think of type as telling Excel what sort of data you’re going to store in that variable.
For example, you might store a number, a date, or a string of text. You tell Excel what type of data is going to be stored in the variable before you start using it.
Let’s take a look at an example in VBA. We’re going to create a variable called “firstName” and tell Excel that it will contain a string of text. Here’s the syntax we’ll use:
Sub getNames() Dim firstName As String
End Sub
Let’s go over that one step at a time.
“Dim firstName As String.”
Dim is short for “Dimension,” and it’s what you use to declare a new variable. The dim statement tells Excel that the next thing we type is the variable name. In this case, that’s “firstName.”
After that, we have “As.” You always need “As” in your declaration statement—it tells Excel that you’re going to use the following VBA data type for that variable.
And then “String” is the actual VBA data type you want.
So in our case, we’re saying “I’m going to use a variable called firstName, and it’s going to contain Strings.”
So, why declare variables?
Alright, so now you know how to declare VBA variables and how it’s done.
But why? It seems like extra work just as a courtesy to Excel 😆
You need to declare variables because if you don’t, Excel will do it for you (invisibly). And Excel always declares any variable as the “Variant” variable type.
In most cases, that’s not a problem at all.
But when your code gets too dense, and when you use the variables “too much”, it might become a problem because the “Variant” data type hold any type of data.
That also makes it very heavy on your computer because it takes up much more space than other VBA data types. It gets a bit technical now but I’ll try and keep it short ⚙️
For most, it’s sufficient to know that any VBA data type you declare will take up much less space than the default Variant variable type, thus reducing the workload on your computer tremendously.
PRO TIP
VBA variable declaration actually helps you debug your code before you run into potential problems, no matter what data types you use.
If you try to put a value into a variable it’s not declared to contain, Excel shows an error message.
So, you can’t put the text “Spreadsheeto” into an Integer VBA variable, for example.
Setting a VBA variable
In many programming languages, you can assign a value to the variable in the same statement that you use to declare it.
In VBA, declaring VBA variables is optional, but you can’t declare AND set the variable at the same time.
Instead, you’ll need to create a new line that assigns a value. Let’s take a look at an example:
Sub getNames() Dim firstName As String firstName.Value = "Jason" End Sub
We’ve added a line that assigns the value of a variable. In VBA, just append “.Value”, an equals sign, and the value you want to assign (in quotation marks).
Here’s another example of a different VBA data type declaration:
Sub calculateSales() Dim averageSales As Long averageSales.Value = "43000" End Sub
In this one, we’re telling Excel that averageSales will contain numbers because the Long datatype is for storing large numbers. Also, that the averageSales declared variable should have an initial value of 43,000.
Remember that variables can change, so we’re only assigning an initial value here.
You can also assign a value to a variable without the .Value method. You can just use a line like this:
averageSales = "43000"
However, it’s generally a good idea to be as specific as possible when programming. And because it’s easy to type “.Value”, there’s no reason not to.
Common VBA data types
Now that you understand how to declare and assign values to an object variable in VBA, let’s talk about VBA data types.
This is a basic introduction to VBA data types. For most VBA beginners, this will be all you need to know. If you need more information, though, like byte counts or user-defined structures, check out Microsoft’s data type summary.
To start, let’s just look at a quick comparison of some of the numerical VBA data types.
Integer can hold 2 bytes of data – but only numbers. That means it can hold both positive values and negative values, in whole numbers between -32,768 and 32,767.
Long can hold 4 bytes. Again, only whole numbers (both positive values and negative values) but this time between -2,147,483,648 and 2,147,483,647
Double holds 8 bytes, but includes decimals!
Something worth noting: the double data type only stores an approximation of long numbers. This makes it good for storing very large numbers but can cause some issues with complex calculations.
Keep in mind that Integer will always round your decimals to the nearest integer, which means you might get some strange results if you’re not expecting them. If you need more precision, use Double.
There are many other numerical VBA data types. But you should be able to handle just about any case with the 3 above.
What about non-numerical types? There are two common ones: String and Boolean.
String is simple—it holds text. Excel won’t be able to run calculations on Strings (though it will be able to use some text functions).
Boolean holds true and false values; you could use 0 or 1 in a numerical data type, but you can save computational power and be more accurate in your data assignments if you use a Boolean variable.
That’s it – Now what?
If you plan on using VBA to create macros, you’re going to want to get used to working with variables. You’ll be using them a lot.
If you’re starting out with VBA, don’t worry too much about optimizing for the best data types in your VBA dim statement. If you know that Integer rounds to whole numbers and Double is for both big numbers and precision, you’ve made a good start.
Also, if you’re starting out learning VBA, you don’t want to miss my free 30-minute Excel VBA course. Click here to enroll.
Other resources
As mentioned before, if you need some of the more niche variable types for your declarations, check out Microsoft’s exhaustive list here. This list also shows the actual size taken up by the data type.
The list actually applies to the Visual Basic programming language – but because VBA is a derivative of Visual Basic, it also applies to VBA.
You can also check out my extensive guide to VBA programming here.
If you want to learn how to declare variables in a hard (but effective) way, you can force the VBA editor to require you to declare all variables you use. with something called the Option explicit statement. Read about the Option explicit statement here.