Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
This tutorial introduces and explains the concept of variables.
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.
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.
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:
VariableName can be any valid name for your variable
VariableType can be any valid type of data
You can also declare multiple variables on one line:
'the name of each superhero and their rating
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:
'be sure to give variables specific types when using multiple declaration
Dim HeroName1, HeroName2 As String
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.
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.
Putting all of the above together, we get:
Sub RecordVote()
'the name of each superhero
Dim HeroName As String
'the rating assigned to them
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.
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:
'put the current cell value into the variable HeroName
HeroName = ActiveCell.Value
'retrieve value from variable HeroName and store it in the current cell
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()
'the name of each superhero
Dim HeroName As String
'the rating assigned to them
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:
'go to the votes sheet and get the value of the superhero, and their rating
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:
'go to top of results
Worksheets("Results").Select
Range("B4").Select
'go to first blank cell
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:
'write variable values into this blank row
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:
'copy formats from cell above (included here just to make macro complete)
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()
'the name of each superhero
Dim HeroName As String
'the rating assigned to them
Dim HeroRating As Long
'go to the votes sheet and get the value of the superhero, and their rating
Worksheets("Votes").Select
HeroName = Range("C4").Value
HeroRating = Range("C6").Value
'go to top of results
Worksheets("Results").Select
Range("B4").Select
'go to first blank cell
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
'write variable values into this blank row
ActiveCell.Value = HeroName
ActiveCell.Offset(0, 1).Value = HeroRating
'copy formats from cell above (included here just to make macro complete)
Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 1)).Copy
ActiveCell.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub
Variables are that simple!
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!
The solution to the above problem is to include two magic words at the top of each module - Option Explicit:
Option Explicit
Sub RecordVote()
'the name of each superhero and their rating
'Dim HeroName As String
'the rating assigned to them
'Dim HeroRating As Long
'go to the votes sheet and get the value of the superhero, and their rating
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.
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.
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.
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.
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.
The next level of scope is to declare a variable at the top of a module:
Option Explicit
'the name of each superhero
Dim HeroName As String
'the rating assigned to them
Private HeroRating As Long
Sub RecordVote()
'go to the votes sheet and get the value of the superhero, and their rating
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.
Sometimes you'll want a variable to be available to all procedures in all modules in a workbook. Here are some examples:
Option Explicit
'examples of public variables
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.
There are two differences between a constant and a variable:
You set a value for a constant when you first declare it; and
You can not subsequently change this value.
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
'the name of the current client
Public Const CompanyName As String = "Wise Owl"
'the folder containing model files
Public Const FilePath As String = "c:\model\"
'the current VAT rate (UK sales tax)
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()
'the cell reference of hero name
Const HeroNameCell As String = "C4"
Const HeroRatingCell As String = "C6"
'the name of each superhero
Dim HeroName As String
'the rating assigned to them
Dim HeroRating As Long
'go to the votes sheet and get the value of the superhero, and their rating
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.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.