Home > Articles

Understanding Program Variables

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Chapter 3: Understanding Program Variables

In this chapter

  • Learning why variables are useful

  • Declaring variables for use in your macros

  • Understanding the different types of variables

  • Learning to use array variables and constants

  • Using techniques that help you avoid the most common variable errors

Your VBA procedures often will need to store temporary values for use in statements and calculations that come later in the code. For example, you might want to store values for total sales and total expenses to use later in a gross margin calculation. Although you probably could get away with using the underlying application to store these values (in, say, a cell in an Excel worksheet), this almost always isn't very practical. Instead, VBA (like all programming languages) lets you store temporary values in variables. This chapter explains this important topic and shows you how to use variables in your VBA procedures.

Declaring Variables

Declaring a variable tells VBA the name of the variable you're going to use. (It also serves to specify the data type of the variable, which I'll explain later in this chapter.) You declare variables by including Dim statements (Dim is short for dimension) at the beginning of each Sub or Function procedure.

In its simplest form, a Dim statement has the following syntax:

Dim variableName


Technically, you can put variable declarations anywhere you like within a procedure and VBA won't complain. The only real restriction is that the Dim statement must precede the first use of the variable in a procedure. Having said all that, however, it's not only traditional, but also clearer, to list all your Dim statements together at the top of a procedure.

Here, variableName is the name of the variable. You make up these names yourself, but there are a few restrictions you need to bear in mind:

  • The name must begin with a letter

  • The name can't be longer than 255 characters

  • The name can't be a VBA keyword (such as Dim or Sub or End)

  • The name can't contain a space or any of the following characters: . ! # $ % & @

For example, the following statement declares a variable named totalSales:

Dim totalSales

Most programmers set up a declaration section at the beginning of each procedure and use it to hold all their Dim statements. Then, after the variables have been declared, you can use them throughout the procedure. Listing 3.1 shows a Function procedure that declares two variables—totalSales and totalExpenses—and then uses Excel's Sum function to store a range sum in each variable. Finally, the GrossMargin calculation uses each variable to return the function result.


To conserve space, you can declare multiple variables on a single line. In the GrossMargin function, for example, you could declare totalSales and totalExpenses using the following statement:

Dim totalSales, totalExpenses

Listing 3.1 A Function that Uses Variables to Store the Intermediate Values of a Calculation

Function GrossMargin()
  ' Declarations
  Dim totalSales
  Dim totalExpenses
  ' Code
  totalSales = Application.Sum(Range("Sales"))
  totalExpenses = Application.Sum(Range("Expenses"))
  GrossMargin = (totalSales - totalExpenses) / totalSales
End Function

In the GrossMargin function, notice that you store a value in a variable with a simple assignment statement of the following form:

variableName = value


To avoid confusing variable names with the names of things that are built into the VBA language, many programmers begin their variable names with a lowercase letter. If the name contains multiple "words," then each subsequent word should use an uppercase first letter (for example, totalSales or newFileName). This is the style I use in this book.

Also, note that VBA preserves the case of your variable names throughout a procedure. For example, if you declare a variable named totalSales and you later enter this variable name as, say, totalsales, VBA will convert the name to totalSales automatically as part of its syntax checking. This means two things:

  • If you want to change the case used in a variable, change the first instance of the variable (usually the Dim statement).

  • After you've declared a variable, you should enter all subsequent references to the variable entirely in lowercase.

  • + Share This
  • 🔖 Save To Your Account