This tutorial introduces and explains the concept of variables.
Our Example Macros
Our example is to do with the following spreadsheet:
When you fill in the name of your superhero and give him (or her) a rating, you'll then be able o click on the button to run a macro to record this vote.
When you click on the RECORD VOTE button shown above, Excel will add it to the list of votes:
The list of votes cast so far.
In this example we want to store the name of our superhero and the rating applied to them somewhere on your computer which is easy to access and use. That somewhere is called a variable.
Variables are (a bit) like suitcases
A variable is a bit like a suitcase, in that it stores something. But there are two big differences: suitcases don't (usually) have names, and they're not restricted to storing just one thing. So if you're the sort of person who names their suitcases and stores just one thing in each, you'll find variables strangely familiar!
Our variable to hold the superhero name.
The variable to hold the rating.
Creating Variables
The main way to create a variable is with a DIM statement (you can also use Private, Public and Global, as we'll see when we get on to talk about scope).
The syntax is:
Dim VariableName As VariableType
In this command:
You can also declare multiple variables on one line:
Dim HeroName As String, HeroRating As Long
Be careful with this method though - the following code will create HeroName1 to be of type Variant, since you haven't specifically given it a type:
Dim HeroName1, HeroName2 As String
Variable Names
Variable names can be pretty much anything you like (although they can't contain spaces and certain other punctuation characters, and can't begin with a number). Here are some good names:
Variable name | Why it's good |
|---|
SuperheroName | Not only does it describe exactly what sort of data it will contain, but it's unlikely that it will conflict with an internal reserved VBA word. |
HeroRating | Again, this is descriptive, and probably won't conflict with any internal VBA word. |
Here are some bad names, showing traps to avoid:
Variable name | Why it's bad |
|---|
Name | You're playing with fire with this name. It's highly likely that Microsoft have used this word for their own internal purposes in VBA, and this can lead to bugs which are almost impossible to track down! Avoid using single words which could possibly be misconstrued by Excel. |
n | This has the merit of being short, but it's hard to guess what it means. This will make your code impossible to read if you're using more than 2 or 3 variables (as you will be). |
superheroname | Nothing wrong with this, except that because it doesn't use CamelCase (hope it's obvious where the name comes from) it's hard to read. Is this SuperHeroName? Or SuperHeronAME? |
Super_Hero_Name | Using underscores as separators is just SO last year! |
If you use compound words and camel case, you can't go far wrong.
Variable Types
This isn't a reference website, so let's keep things simple. You can survive and thrive with just 6 variable types:
Type | Notes | Example |
|---|
String | Holds any string of characters or text | Batman |
Long | Holds any whole number | 42 |
Double | Holds any floating point number | 3.14 |
Boolean | Can only hold the values True and False | True |
Date | Holds any date and time combined | 25/12/2025 12:45:00 |
Variant | Use when you're not sure of the data type. Variants don't care WHAT you put into them! | Anything! |
The above data types may waste a couple of bytes of storage data, but with modern computers you won't notice the difference.
The Variables for our Example
Putting all of the above together, we get:
Sub RecordVote()
Dim HeroName As String
Dim HeroRating As Long
It's traditional to declare all of the variables that you're going to use at the top of each procedure, or program, although there's nothing in VBA saying that you have to do this.
The Variables for our Example
Now that we know how to create variables, it's time to assign values to them. First, a quick reminder of what we're trying to do with our macro:
 |  |
We're trying to copy the vote ... | ... into the results sheet |
There are only two basic commands with a variable - you can either assign a value to it, or read a value from it:
HeroName = ActiveCell.Value
ActiveCell.Value = HeroName
Bearing this in mind, let's have a look at our macro (it's listed in full at the bottom of this page). First, we declare the variables we're going to use:
Sub RecordVote()
Dim HeroName As String
Dim HeroRating As Long
The next thing to do is to find out the current hero's name and rating, and store these in variables:
Worksheets("Votes").Select
HeroName = Range("C4").Value
HeroRating = Range("C6").Value
Now we need to go the results sheet, and go down to the cell where the votes will be recorded:
Worksheets("Results").Select
Range("B4").Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Now it's simply a case of transferring the information we've stored in the two variables into the worksheet:
ActiveCell.Value = HeroName
ActiveCell.Offset(0, 1).Value = HeroRating
Finally (and this bit has nothing to do with variables), I've put two slightly complicated lines to copy the formatting down from the row above:
Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 1)).Copy
ActiveCell.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Here's the full code, if you want to try it out:
Sub RecordVote()
Dim HeroName As String
Dim HeroRating As Long
Worksheets("Votes").Select
HeroName = Range("C4").Value
HeroRating = Range("C6").Value
Worksheets("Results").Select
Range("B4").Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = HeroName
ActiveCell.Offset(0, 1).Value = HeroRating
Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 1)).Copy
ActiveCell.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub
Variables are that simple!
Why you should Declare All Variables
The example above would run just as well if you didn't declare your variables:
If you comment out the variable declarations like this, your macro will still work.
In the above case, VBA will reach the first two lines creating variables:
HeroName = Range("C4")
HeroRating = Range("C6")
Because you haven't explicitly declared HeroName and HeroRating, VBA will create them on the fly for you, giving each the type Variant. So far, so good?
Well, yes - until you mistype a variable name. Here's what would happen for a simple mistyping:
 |  |
There's a misprint here - how quickly can you see it? | The superhero you get as a result |
The problem above could be avoided so easily, if you could just tell VBA that wherever you use a variable, you have to declare it. Read on!
Forcing Declaration of Variables
The solution to the above problem is to include two magic words at the top of each module - Option Explicit:
Option Explicit
Sub RecordVote()
Worksheets("Votes").Select
HeroNane = Range("C4").Value
HeroRating = Range("C6").Value
Here's what you get if you run the above macro now:
The result of running the macro. VBA highlights the variable you've misspelt, and tells you that it's not defined.
Setting the Option to Require Variables as a Default
Given that forcing yourself to declare all variables like this is such a good idea, how can you avoid having to type in Option Explicit at the top of every new module you create? Here's how!
From the menu within VBA (not Excel) select Tools -> Options.
Tick the Require Variable Declaration box shown below.
Select OK.
Tick the box shown!
From now on, every time you create a new module it will have the words Option Explicit at the top, and you'll have to declare any variables that you use.
Moving to and from Variables
SHIFT + F2 and also SHIFT + CTRL + F2are useful keys for moving to and from a variable definition. Here's how they work:
Right-click anywhere on a variable and choose the option shown to go to its declaration (or just click on the variable and press SHIFT + F2).
When you go to a variable's definition, Excel VBA highlights the variable in question:
Excel takes you to the definition of the variable and highlights it.
If you press SHIFT + CTRL + F2 it acts like an undo button for positioning, and takes you back to your previous position in the code, then the one before that, then the one before that, and so on.
What Determines Variable Case?
The case of a variable (whether it appears in capitals, lower case or some combination of the two) is entirely determined by the DIM statement declaring it:
There is no point typing in herorating in the correct case ...
... because Excel VBA will correct it for you when you move off the line.
This means that any changes you make to case in a DIM statement propagate through your entire procedure's code:
 |  |
Changes you make to a variable here ... | ... will apply wherever the variable is used in your procedure |
The net effect of the above is that you should type variables in in lower case - it's quicker, and VBA will correct the case for you when you press ENTER.
Local Scope
Variables have different scope, according to where and how you define them. The variables we've seen so far have scope limited to the procedure in which they're defined:
In the top subroutine called SomethingElse, any attempt to reference HeroName will fail, since it is defined within the separate procedure RecordVote and hence is local to that.
Most variables that you create will be locally defined within a single subroutine like this.
Public Scope for a Module
The next level of scope is to declare a variable at the top of a module:
Option Explicit
Dim HeroName As String
Private HeroRating As Long
Sub RecordVote()
Worksheets("Votes").Select
In the code above, HeroName and HeroRating are defined across all procedures within this module.
Dim and Private mean the same thing in the above context.
Highest Scope - Public to All Modules
Sometimes you'll want a variable to be available to all procedures in all modules in a workbook. Here are some examples:
Option Explicit
Public ModelName As String
Public CompanyName As String
Global FilePath As String
Note that the word Global is old-fashioned, but is still supported - you should use Public instead by preference.
If you're using public variables, you may instead want to create them as constants instead, as shown below.
Declaring and Using Constants
There are two differences between a constant and a variable:
To continue the suitcase analogy from earlier: a constant is like a suitcase which, once locked, can never be re-opened.
Here are some examples of constants that you might create:
Option Explicit
Public Const CompanyName As String = "Wise Owl"
Public Const FilePath As String = "c:\model\"
Public Const VatRate As Double = 0.2
Constants don't have to be public - you can declare them in a procedure as follows:
Sub RecordVote()
Const HeroNameCell As String = "C4"
Const HeroRatingCell As String = "C6"
Dim HeroName As String
Dim HeroRating As Long
Worksheets("Votes").Select
HeroName = Range(HeroNameCell).Value
HeroRating = Range(HeroRatingCell).Value
Good things to hold in constants are file paths, worksheet names and workbook names.
If you want to create a truly global constant - one whose value will be available within all workbooks, all of the time - you need to create an add-in.
