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:
Dim firstName As String
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.
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.