10. Variables

10.1 What Is a Variable?

Technically a variable is a particular location in the memory of the computer (not a very useful definition for our purposes). We would do better to think of a variable as a container (like a can, a box, or a jar). The reason it is called a variable is that the contents, or value, of a variable can change. The value of a variable can be changed as often as necessary, but a variable can have only one value at a time; when another value is put into a variable, that value replaces the value that was there before.

Variables provide a way for the macro to store or "remember" data, so that the macro can use that data at different stages in the execution of the macro. You can use variables to store text, numbers, dates, True/False, or a reference to an object. Variables also provide a means for comparing values, for decisions, looping, and other operations that are done with VBA language statements. Whatever you could do to, or with, the value of a variable, you can do to, or with, that variable itself. In other words, anywhere in your macro where you could use text, a numerical value, a date, True/False, or a reference to an object, etc., you can use a variable with that value instead. Thus, when you use a variable, you are really just using it as a substitute for the value the variable contains.

The advantage of using a variable is the flexibility it provides. If you use constant values in your macro (literal text, a specific number, etc.), as happens when you record a macro, the macro will do exactly the same thing each time it runs, because those values can never be changed. You can make that macro much more flexible if you use variables, with which the macro can do different things each time it runs, depending on the value that the variables have when the macro runs.

Variables also make macro code easier to read and write. It is simpler to use, for example, a variable named vText to store the first part of the contents in a cell, than to have to use the expression Left(ActiveCell.Value, 5) each time. (If you are familiar with Names in a workbook in Excel, variables work somewhat the same way.)

You will see examples of variables in use in later chapters of this tutorial that cover the VBA language statements.

10.2 Naming variables

Each variable has a name, which is used to identify the variable. You choose the name of each variable you create. There are rules regarding the naming of variables, but it is easiest just to follow this one: the variable name must start with a letter, and the rest of the name can consist of a combination of letters and numbers; spaces are not allowed. As with commands, variable names are not case-sensitive. Variable names can be as simple as one letter, like the classic variable name x, but as you write more complex macros using variable names that are more descriptive will make reading and editing the code much easier. A variable that will be used to keep track of how many times a repeated action is done might be called MyCount; a variable that will contain a person's name might be called PersonName, or NameofPerson.

One limitation on naming variables is that certain words are used in VBA for programming commands or names of objects. Those words are reserved for use for those purposes only, and cannot be used to name variables. One way to avoid accidentally using a reserved word as a variable name, and to readily identify it as a variable, is to start each variable name with a lower-case "v," e.g., vCount, vPersonName.

Since variable names look like text in macro code, we need to distinguish text from variables. This is done by enclosing text values in quotation marks. If you forget to do that you will get a compilation error, so remember that when we want to refer to literal text characters, we enclose them in quotes. vMyVariable is a variable, "This is literal text" is text.

10.3 Assigning Values to Variables

The process of putting a value into a variable is known as assignment. Assignment is done slightly differently depending on whether the variable refers to an object or not. To assign a value to a variable that does not reference an object, we just use the equal sign ("="). To assign an object reference to a variable, we use the keyword "Set" and the equal sign. The value of the "expression" on the right of the equal sign is assigned to the variable on the left.

The term "expression" means a phrase that can be evaluated to a value. The definition of "expression" in VBA Help is probably best: "A combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can be used to perform a calculation, manipulate characters, or test data." Assignment examples:

vLastRow = 500

vRoomNumber = "500"

vMaxAmount = 100000

vMinAmount = 10.75

vCity = "Chicago"

vOriginalRow = vRow

Set vCellToCheck = Cells(vRow, vColumn)

Set vOriginalWorksheet = Sheets("source data")

Set vNewWorkbook = Workbooks.Add

Set vNewWorkbookSales = Workbooks.Add (Template:="sales.xlt")
These examples of assignment statements illustrate some principles:
The assignments to vLastRow and vRoomNumber show the difference between a number and text, or string. (In VBA a text value is called a "string.") You are probably familiar with this concept in Excel: two of the number formats of a cell in Excel are "number" and "text." A number is a quantity; you can do arithmetic using that value. A string is a sequence of characters. All the characters in a string might be numeric characters, but they do not represent a number value; usually they represent an identifier, such as a zip code, room number, account number, or identification number. Because they constitute a string, the numeric characters must be enclosed in quotation marks.
Notice how the quantity of one hundred thousand was typed for assignment to vMaxAmount. In VBA the comma separating thousands is omitted.
Since a variable can be used whereever its value can be used, you can include a variable as part of the assignment expression. You can assign the value of one variable to another, as done above with vOriginalRow = vRow. We might want to do that if the value of vRow changes during the macro, and we need to compare it at various times to the value it had originally.
The assignment of a reference to a Range object to vCellToCheck is an example of using variables to identify the row and column number of a cell when using that syntax to identify a range. (Recall that Cells returns a Range object that represents the cells described, as explained previously in this tutorial.)
The method Workbooks.Add adds a workbook to the collection of workbooks in Excel (equivalent to creating a new workbook manually). This method returns a reference to the new workbook created, which here is assigned the object variable vNewWorkbook. The argument is in parentheses because the return value of the method is being used. This rule is discussed in more detail later in this tutorial.
An object variable can be used whereever the actual object is to be referenced, including in dot-notation expressions. Thus, after we have assigned the variable vOriginalWorksheet as above, we can use that variable wherever we could use Sheets("source data"), e.g., in the command vOriginalWorksheet.Activate. The object variable can access the properties and methods of the object it references.

You can also assign the result of an arithmetic operation (using the operators "+", "-", "*" and "/", and others), concatenation (combining text) using the "&" operator, the return value of a method, or the return value of a VBA function, to a variable. (Functions are covered later in this tutorial.) Examples:

y = x * 2

vNextAmount = vPreviousAmount + vDifference

vCombinedString = "Hello, " & "World"

The effect of assigning a "primitive" data type (i.e., a value, not an object) to a variable is subtly different from that of assigning an object reference. In the example above, the value sum of vPreviousAmount and vDifference was assigned to vNextAmount. All these variables are independent, so that after the assignment there is no link maintained among them. vNextAmount is assigned the sum of the values of vPreviousAmount and vDifference at the time of the assigment. If the values of those two variables (or either of them) change after the assignment, that will have no effect on the value of vNextAmount. Where we assigned a reference to Sheets("source data") to vOriginalWorksheet, a link is maintained. vOriginalWorksheet always references the current state of that worksheet. So, as properties of that worksheet change, those changes are reflected when that worksheet is referenced using vOriginalWorksheet.

10.4 Declaring Variables; Data Types

Declaring variables is not a requirement in VBA, and adds a layer of complexity that we need only cover briefly for the purposes of this tutorial. However, code samples in books and on websites often include variable declarations, so you should at least have a passing familiary with the concept. It is better programming practice to declare variables and doing so has advantages; as you gain more experience writing macros you will probably want to do so. For now, we will just offer a brief introduction.

A variable is "declared" by specifying the variable's name and data type. In VBA a variable is declared by using the 'Dim' (for dimension) command, e.g.,

Dim vMyText as String

Dim vNextNumber as Integer
The more common data types for VBA variables are String (for variables containing text); Integer, Long and Decimal (for variables containing numeric values); Date (for variables containing dates or times); Boolean (for variables containing True or False); and Variant (which is the catch-all type that can hold any kind of data).

Variables can also be declared as the data type of objects in the applicable object model, e.g.,

Dim vFirstRange as Range

Dim vOriginalWorkSheet as Worksheet
A detailed discussion of the data types available in VBA is beyond the scope of this tutorial, but if you declare variables you need to be aware of the different data types for numerical data. Particularly important is how large a number a particular numerical type can hold, and whether a type can hold whole numbers or numbers with decimal values. For example, an integer variable can hold numbers up to 32,767. If you try to assign a row number in Excel to an Integer variable, you will get an 'overflow' error if the row number is greater than 32767. For that purpose you would need to use a data type that can hold a larger number, such as Long. Similarly, if you try to save the value 13.87 in an Integer variable, you will get an overflow error; you need to use a type than can contain decimals, such as Decimal.

10.5 Default Values of Variables

If you do not declare a variable, the variable is simply created the first time the variable name is encountered in your macro, and will have the VARIANT data type. Note that every variable (except object variables) contains a default value as soon as it comes into existence (i.e., as soon as it is declared, or as soon as you use it in a macro if you have not declared it). This value is known as the "default value." The variable has that default value until a value is explicitly assigned to it in the macro. It is better practice not to rely on variable default values, and to always assign a value to a variable before you use it for anything else in a macro.