What is a Variable? | Declaring | Name Restrictions | Naming Conventions | String | Boolean | Integer | Double

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.

Declaring Variables

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 DataType in the above code.

What about VariableName? Well, your variable name can pretty much be anything you want it to be, subject to the following limitations.

Variable Name Restrictions

As of Microsoft Excel 2010, VBA variable names are subject to the following restrictions:

  1. They must begin with a letter. Numbers and underscores can be used later in the name.
  2. They cannot exceed 255 characters in length. Who wants a 255 character variable name, anyway!?
  3. They cannot be named the same as any Excel key word. For example, Range, Sheet and Integer are forbidden variable names.
  4. 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.
  5. 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.

Naming Conventions

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

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

Result:
VBA String Variable

The default value for a string is the empty string “”.

Boolean

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

Result:
VBA Boolean Variable

I sort of jumped the gun here by introducing an If-Then statement, but you are a smart audience. Because bFlag was set to 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 True and False, respectively.

The default value for a boolean is False.

Integer

Integer data types store whole numbers.

Sub IntegerDemo()
    Dim iValue As Integer
    iValue = 5.5
    MsgBox (iValue)
End Sub

Result:
VBA Integer Variable

Did you see that? Notice how the value of iValue was rounded to a whole number. Recall, we said the integer data type can only store whole numbers. That doesn’t mean we can’t try to feed it a decimal number. It just converts the decimal number to an integer.

The default value for an integer is 0.

Double

Double data types can store whole numbers and precise decimal numbers.

Sub DoubleDemo()
    Dim dValue As Double
    dValue = 5.5
    MsgBox (dValue)
End Sub

Result:
VBA Double Variable

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.