Variables in Macros VBA for Excel - Practical Guide

This is a practical guide to using Variables in Macros and VBA for Excel. I will tell you what they are and how to use them.

Sections:

What is a Variable?

Why Should I use Variables?

How to Make and Use Variables in Macros

Naming Variables

Declaring Variables - What is it and Are you Sure It's Not Important?

Notes

What is a Variable?

A variable is a name that you type into a Macro that you can use to store information, be it text or numbers.

Once you put information into the variable, you can later reference that variable in order to get the information that is stored within it. So, think of variables like containers that hold the data that you put into them.

You reference these containers by typing their name.

You can reference a variable as many times as you need throughout a macro and you can also change the data that is contained within it as many times as you need. You control what's in the container.

Why Should I use Variables?

Variables provide consistency and make it much easier to maintain and use your Macros and VBA.

Let's say that you are getting a value from a cell and doing something with it in your macro and you need to reference that cell's value 10 different times within the macro. Now, let's say that you need to get that value from a different cell; if you put that value into a variable and used that variable throughout the macro, you only need to update the cell reference in one place, but if you didn't use a variable, you will need to update all 10 places where you got that cell's value into the macro.

Variables will soon become your best friend when it comes to making Macros and programming in VBA.

How to Make and Use Variables in Macros

Make the Variable

Technically, this is called initializing the variable.

Many people will tell you that you need to do what is called "declaring" your variables before you initialize them, and that is a great idea for programmers in VBA, but it is a waste of time for 98% of all Macros that you will ever create for Excel and, as such, we will skip that part here. I touch on this topic a little at the end of the tutorial.

Since we aren't declaring our variable, we create it by simply typing a name and setting it equal to something:

my_new_variable = 5

I created the variable my_new_variable and put the value 5 into it.

The variable was created when I typed its name and it got a value when I put an equals sign after it and then a value after that equals sign.

It's really as easy as that.

Note that you will create the variable and give it a value at the same time, unless you are declaring variables, which is covered a little at the end of this tutorial.

Use the Variable

Now that you have made the variable, you can use it wherever you need to in your code. Just type the name of your variable and that is it.

If I wanted to output this variable into a message box, I could do this:

MsgBox is just the function used to create the message box and what I put after it is what will be output, in this case, that's the contents of my variable.

Back in Excel, run the macro and the output will be this:

Use the variable wherever you need to as many times as you need to; it will make your life easier than typing-in the values by hand.

Basically, if you are going to use a value more than once, or you might need to change that value at some point in the future, put it into a variable somewhere near the top of your macro and just reference that variable. Whenever you need to change it, the variable will be easy to find and update.

You can also use your variable in mathematical calculations. So, if I wanted to multiply our variable by 2 so the output in the message box would be 10, I could do something like this:

I highlighted the part where the variable is being multiplied. All I did was put the * multiplication sign followed by a two after the name of the variable.

Now, the message box will output 10:

Naming Variables

Naming Rules

The name must start with a letter.

The max length for a variable name is 255 characters; however, if you are even close to that, you are making a mistake because that makes reading the code much more difficult and annoying.

You cannot use any of the following characters in the name of a variable:

•  (space)
• . (period)
• ! (exclamation point)
• @ (at symbol)
• & (ampersand or "and" symbol)
• $ (dollar sign)
• # (pound or "number" sign)

How You Should Name Them

You should NOT give variables the same name as a function in VBA. You are technically able to do this, but it can really cause you a lot of problems, especially once you make larger macros.

The most important thing is to give your variables a meaningful name that will tell you what is contained within them.

If it's a user's input, you could call it user_input; if it stores a part number, call it part_number.

Once you decide on a naming convention, a consistent system of naming that helps you remember what is what, make sure to stick with it throughout your entire code/macro.

Declaring Variables - What is it and Are you Sure It's Not Important?

Declaring a variable means that you write the name of the variable at the top of the macro and then, more often than not, tell the macro what kind of data you want to store in the variable.

You can tell the macro that the variable will be used to store a String, a True/False value, a number of different number types, including Integer and Long, and more.

To declare a variable it looks like this:

Dim my_variable As String

You would put this at the top of the macro before any other code.

There will be another tutorial that covers this topic in more detail but, you really don't need to read that unless you want to do so.

98% of the time it doesn't matter if you declare your variables or not. The reason for that is because Excel will figure out what data is in the variables anyway, and most macros are small little programs that don't require much code or sophisticated structure and so the organization of the data and variables isn't such a big deal. In those cases, declaring each variable becomes more of a hassle than a help.

When you start building larger macros that have many different elements to them, then, it is good practice to declare your variables; however, it still wouldn't be necessary to do so.

Notes

Variables are an important feature of VBA and you should use them in your Macros. They make your code much easier to read and maintain and using them will help you keep your sanity along with more organized code.

Make sure to download the sample Excel file attached to this tutorial to play around with the Macro example shown here and to become more comfortable with variables.