How to Declare Variables in VBA

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

In VBA, like any other programming language, variables are important. They’re one of the core parts of the language, in fact.

A variable in programming is like a variable in math: it stands in the place of a value, and that value can change.

The fact that the value can change gives you a lot of power when you’re running VBA scripts.

What it means to declare variables

When you want to use a variable in programming, you should declare it.

In VBA, declaring has two steps:

  1. Set a name for the variable. For example, you might call yours “quarterlySales.”
  2. Set a type for the 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 have to tell Excel what kind of data is going to be stored in the variable before you start using it.

Unfortunately, Excel can’t figure this out automatically. So you’ll need to do it manually every time.

Kasper Langmann, Co-founder of Spreadsheeto

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.

First, we have “Sub getNames()”. This is the beginning of a VBA procedure. You’ll often declare new variables inside of procedures.

Then we come to “Dim firstName As String.”

Dim is short for “Dimension,” and it’s what you use to declare a new variable. Dim 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 data type for that variable.

So in our case, we’re saying “I’m going to use a variable called firstName, and it’s going to contain Strings.”

Finally, we have “End Sub,” which tells Excel we’re done working on this procedure.

Setting variables

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 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 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 variable should have an initial value of 43,000.

Remember that variables can change, so we’re only assigning an initial value here.

Kasper Langmann, Co-founder of Spreadsheeto

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 data types in VBA

Now that you understand how to declare and assign values to variables in VBA, let’s talk about data types.

This is a basic introduction to 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.

Kasper Langmann, Co-founder of Spreadsheeto

To start, let’s just look at a quick comparison of some of the numerical data types.

Integer can hold 2 bytes of data. That means it can hold whole numbers between -32,768 and 32,767.

Long can hold 4 bytes. That’s -2,147,483,648 through 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.

Kasper Langmann, Co-founder of Spreadsheeto

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 it. If you need more precision, use Double.

There are many other numerical 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 Boolean.

Get used to working with variables

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.

For now, don’t worry too much about the data types. If you know that Integer rounds to whole numbers and Double is for both big numbers and precision, you’ve made a good start.

Again, if you want to see the different data types supported by VBA and what kind of data they can hold, check out Microsoft’s data-type reference page.