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.
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.
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 = 500These examples of assignment statements illustrate some principles:
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")
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.
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 StringThe 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).
Dim vNextNumber as Integer
Variables can also be declared as the data type of objects in the applicable object model, e.g.,
Dim vFirstRange as RangeA 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.
Dim vOriginalWorkSheet as Worksheet