Now that you know all about data types, let’s discuss variables.
What is a Variable?
A variable is like a movie voucher. It reserves a spot in your computer’s memory so you can use it later. You get to pick what you want to store in the variable, just like you would pick what movie you want to see with your movie voucher. You can change the value stored in the variable as many times as you like, as long as the values comply with the data type requirements. The bottom line is that variables make your VBA macros shorter and easier to read.
When you declare a variable, you are reserving a spot in your computer memory for a specific data type with a specific name. VBA variable declarations can technically be placed anywhere within your macro, but convention states they should be placed immediately after your opening, just like we did in our Declaring a Range demo. Generically, variable declarations follow the format:
Dim VariableName as DataType
Dim is short for Dimension. Recall from the last lesson, the most common data types are String, Boolean, Integer and Double. Nine out of ten times, you would put one of those names in place of
Variable Name Restrictions
As of Microsoft Excel 2010, VBA variable names are subject to the following restrictions:
- They must begin with a letter. Numbers and underscores can be used later in the name.
- They cannot exceed 255 characters in length. Who wants a 255 character variable name, anyway!?
- They cannot be named the same as any Excel key word. For example, Range, Sheet and Integer are forbidden variable names.
- They cannot contain spaces, periods, or most special characters. If you want a multi-word variable name, you can separate the words with underscores or capital letters.
- VBA variables are not case-sensitive. You cannot have one variable named “strDog” and another named “strdog”.
Although you are free to name your variables however you wish, it’s a good practice to consistently follow some sort of naming convention.
There is no “correct” naming convention for VBA variables. You can make up your own convention, but be sure to use it consistently. A solid naming convention makes it easier for you and others to read your macros. You don’t have to keep scrolling around to try to find out what data type your variable is supposed to be. Here’s my naming convention for the most common data types:
- String variable names begin with str (strEmployee)
- Boolean variable names begin with b (bFlag)
- Integer variable names begin with i (iCount)
- Double variable names begin with d (dSalary)
Feel free to adopt the same convention! Now let’s sharpen your skills with some examples.
String data types store text. Add a module and paste the following VBA subroutine:
Sub StringDemo() Dim strPresident As String strPresident = "George Washington" Range("A1") = strPresident End Sub
The default value for a string is the empty string “”.
Boolean data types store True or False values.
Sub BooleanDemo() Dim bFlag As Boolean bFlag = False If bFlag = True Then Range("A1") = "Hello" Else Range("A1") = "Goodbye" End If End Sub
I sort of jumped the gun here by introducing an If-Then statement, but you are a smart audience. Because
False, Range “A1” was changed to “Goodbye.” Play around. Change bFlag to
True and see what happens. You can also change bFlag to -1 or 0 to represent
The default value for a boolean is False.
Integer data types store whole numbers.
Sub IntegerDemo() Dim iValue As Integer iValue = 5.5 MsgBox (iValue) End Sub
Did you see that? Notice how the value of
The default value for an integer is 0.
Double data types can store whole numbers and precise decimal numbers.
Sub DoubleDemo() Dim dValue As Double dValue = 5.5 MsgBox (dValue) End Sub
Running the code with the Double data type produces a msgbox containing the unrounded value, 5.5.
The default value for a double is 0.
In this tutorial, you learned how to declare a variable and the best place to place your variable declarations. You learned what’s allowed and not allowed when naming your variables and you discovered that it’s wise to follow a consistent naming convention for your variables. After that, you ran some examples to see how different data types behave.
You’ve come a long way since Chapter 1.1! Stay with us to learn a bit more about variables.
If you really want to learn VBA, grab a copy of our Ultimate VBA Training Bundle before reading our next tutorial. We specifically created these cheat sheets to help you get the most out of our upcoming lessons. Together, the set has over 200 practical tips covering the 125 most important topics in Excel VBA. We also stuffed it with 140 helpful macro examples.