What’s so great about variables?

Although it is possible to create code that does not use variables, you will encounter many examples of VBA code where variables are employed. There are two main reasons for this.

First, Excel doesn’t inherently know what your data is used for. It doesn’t see numerals, symbols, or letters. It just sees data. When you declare variables with specific data types, you help Excel know how it should handle certain pieces of data so that your macros will produce the results you’d expect.

Second, variables help by making your code more efficient and easier to understand. For example, suppose you have a number in cell A1 that you are repeatedly referring to in your macro. You can retrieve that number by pointing to cell A1 each time you need it:

Sub Macro1()
Range("B1").Value = Range("A1").Value * 5
Range("C1").Value = Range("A1").Value * 10
Range("D1").Value = Range("A1").Value * 15
End Sub

However, this forces Excel to waste cycles storing the same number into memory every time you point to cell A1. Also, if you need to change your workbook so that the target number is not in cell A1, but in, let’s say, cell A2, you must edit your code by changing all the references from A1 to A2.

A better way is to store the number in cell A1 just once. For example, you can store the value in cell A1 in an Integer variable called myValue.

Sub WithVariable()
Dim myValue As Integer
myValue = Range("A1").Value
Range("B1").Value = myValue * 5
Range("C1").Value = myValue * 10
Range("D1").Value = myValue * 15
End Sub

This not only improves the efficiency of your code (ensuring Excel reads the number in Cell A1 just once) but ensures that you only have to edit one line should the design of your workbook change.

Using assignment statements

An assignment statement is a VBA instruction that evaluates an expression and assigns the result to a variable or an object. An expression is a combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can perform a calculation, manipulate characters, or test data.

If you know how to create formulas in Excel, you’ll have no trouble creating expressions in VBA. With a worksheet formula, Excel displays the result in a cell. Similarly, you can assign a VBA expression to a variable or use it as a property value.

VBA uses the equal sign (=) as its assignment operator. Note the following examples of assignment statements. (The expressions are to the right of the equal sign.)

x = 1
x = x + 1
x = (y * 2) / (z * 2)
MultiSheets = True

Expressions often use functions. These can be VBA’s built-in functions, Excel’s worksheet functions, or custom functions that you develop in VBA. We discuss VBA’s built-in functions later in this chapter.

Operators play a major role in VBA. Familiar operators describe mathematical operations, including addition (+), multiplication (*), division (/), subtraction (–), exponentiation (^), and string concatenation (&). Less familiar operators are the backslash () that’s used in integer division and the Mod operator that’s used in modulo arithmetic. The Mod operator returns the remainder of one integer divided by another. For example, the following expression returns 2:

17 Mod 3

You may be familiar with the Excel MOD function. Note that in VBA, Mod is an operator, not a function.

VBA also supports the same comparative operators used in Excel formulas: equal to (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). Additionally, VBA provides a full set of logical operators, as shown in Table 24.1. Refer to the Help system for additional information and examples of these operators.

The order of precedence for operators in VBA exactly matches that in Excel. Of course, you can add parentheses to change the natural order of precedence.

On Error GoTo SomeLabel

There are times when an error in your code means you need to gracefully exit the procedure and give your users a clear message. In these situations, you can use the On Error GoTo statement to tell Excel to jump to a certain line of code.

Take this small piece of code, for example. Here, we are telling Excel to divide the value in cell A1 by the value in cell A2 and then place the answer in cell A3. Easy. What could go wrong?

Sub Macro1()
Range("A3").Value = Range("A1").Value / Range("A2").Value
End Sub

As it turns out, two major things can go wrong. If cell A2 contains a 0, we get a divide by 0 error. If cell A1 or A2 contains a nonnumeric value, we get a type mismatch error.

To avoid a nasty error message, we can tell Excel that On Error, we want the code execution to jump to the label called MyExit.

In the code that follows, you see that the MyExit label is followed by a message to the user that gives her friendly advice instead of a nasty error message. Also note the Exit Sub line before the MyExit label. This ensures that the code simply exits if no error is encountered:

Sub Macro1()
On Error GoTo MyExit
Range("A3").Value = Range("A1").Value / Range("A2").Value
Exit Sub
 
MyExit:
MsgBox "Please Use Valid Non-Zero Numbers"
End Sub

On Error Resume Next

Sometimes you want Excel to ignore an error and simply resume running the code. In these situations, you can use the On Error Resume Next statement.

For example, this piece of code is meant to delete a file called GhostFile.exe from the C:Temp directory. After the file is deleted, a nice message box tells the user the file is gone:

Sub Macro1()
Kill "C:TempGhostFile.exe"
MsgBox "File has been deleted."
End Sub

It works great if there is indeed a file to delete. But if for some reason the file called GhostFile.exe does not exist in the C:Temp drive, an error is thrown.

In this case, we don’t care if the file is not there. We were going to delete it anyway. So we can simply ignore the error and move on with the code.

By using the On Error Resume Next statement, the code runs its course whether or not the targeted file exists:

Sub Macro1()
On Error Resume Next
Kill "C:TempGhostFile.exe"
MsgBox "File has been deleted."
End Sub

On Error GoTo 0

When you’re using certain error statements, it may be necessary to reset the error-checking behavior of VBA. To understand what this means, take a look at this example.

Here, we first want to delete a file called GhostFile.exe from the C:Temp directory. To avoid errors that may stem from the fact that the targeted file does not exist, we use the On Error Resume Next statement. After that, we are trying to do some suspect math by dividing 100/Mike:

Sub Macro1()
On Error Resume Next
Kill "C:TempGhostFile.exe"
Range("A3").Value = 100 / "Mike"
End Sub

Running this piece of code should generate an error due to the fuzzy math, but it doesn’t. Why? Because the last instruction we gave to the code was On Error Resume Next. Any error encountered after that line is effectively ignored.

To remedy this problem, we can use the On Error GoTo 0 statement to resume the normal error-checking behavior:

Sub Macro1()
On Error Resume Next
Kill "C:TempGhostFile.exe"
On Error GoTo 0
 
Range("A3").Value = 100 / "Mike"
End Sub

This code ignores errors until the On Error GoTo 0 statement. After that statement, the code goes back to normal error-checking where it triggers the expected error stemming from the fuzzy math.

For-Next loops

The following is the syntax for a For-Next loop:

For counter = start To end [Step stepval]
    [instructions]
    [Exit For]
    [instructions]
Next [counter]

The following listing is an example of a For-Next loop that does not use the optional Step value or the optional Exit For statement. This function accepts two arguments and returns the sum of all integers between (and including) the arguments:

Function SUMINTEGERS(first, last)
    total = 0
    For num = first To last
        total = total + num
    Next num
    SUMINTEGERS = total
End Function

The following formula, for example, returns 55—the sum of all integers from 1 to 10:

=SUMINTEGERS(1,10)

In this example, num (the loop counter variable) starts out with the same value as the first variable and increases by 1 each time the loop repeats. The loop ends when num is equal to the last variable. The total variable simply accumulates the various values of num as it changes during the looping.

You also can use a Step value to skip some values in the loop. Here’s the same function rewritten to sum every other integer between the first and last arguments:

Function SUMINTEGERS2(first, last)
    total = 0
    For num = first To last Step 2
        total = total + num
    Next num
    SUMINTEGERS2 = Total
End Function

The following formula returns 25, which is the sum of 1, 3, 5, 7, and 9:

=SUMINTEGERS2(1,10)

For-Next loops can also include one or more Exit For statements within the loop. When this statement is encountered, the loop terminates immediately, as the following example demonstrates:

Function ROWOFLARGEST(c)
    NumRows = Rows.Count
    MaxVal = WorksheetFunction.Max(Columns(c))
    For r = 1 To NumRows
        If Cells(r, c) = MaxVal Then
            ROWOFLARGEST = r
            Exit For
        End If
    Next r
End Function

The ROWOFLARGEST function accepts a column number (1–16,384) for its argument and returns the row number of the largest value in that column. It starts by getting a count of the number of rows in the worksheet. (This varies, depending on the version of Excel.) This number is assigned to the NumRows variable. The maximum value in the column is calculated by using the Excel MAX function, and this value is assigned to the MaxVal variable.

The For-Next loop checks each cell in the column. When the cell equal to MaxVal is found, the row number (variable r, the loop counter) is assigned to the function’s name, and the Exit For statement ends the procedure. Without the Exit For statement, the loop continues to check all cells in the column, which can take quite a long time.

The previous examples use relatively simple loops. But you can have any number of statements in the loop, and you can even nest For-Next loops inside other For-Next loops. The following is VBA code that uses nested For-Next loops to initialize a 10 × 10 × 10 array with the value –1. When the three loops finish executing, each of the 1,000 elements in MyArray contains –1:

Dim MyArray(1 to 10, 1 to 10, 1 to 10)
For i = 1 To 10
    For j = 1 To 10
        For k = 1 To 10
            MyArray(i, j, k) = –1
        Next k
    Next j
Next i

Do While loops

A Do While loop is another type of looping structure available in VBA. Unlike a For-Next loop, a Do While loop executes while a specified condition is met. A Do While loop can have one of two syntaxes:

Do [While condition]
    [instructions]
    [Exit Do]
    [instructions]
Loop

or

Do
    [instructions]
    [Exit Do]
    [instructions]
Loop [While condition]

As you can see, VBA enables you to put the While condition at the beginning or the end of the loop. The difference between these two syntaxes involves the point in time when the condition is evaluated. In the first syntax, the contents of the loop may never be executed if the condition is met as soon as the Do statement is executed. In the second syntax, the contents of the loop are always executed at least one time.

The following example is the ROWOFLARGEST function presented in the previous section, rewritten to use a Do While loop (using the first syntax):

Function ROWOFLARGEST2(c)
    NumRows = Rows.Count
    MaxVal = Application.Max(Columns(c))
    r = 1
    Do While Cells(r, c) <> MaxVal
        r = r + 1
    Loop
    ROWOFLARGEST2 = r
End Function

The variable r starts out with a value of 1 and increments within the Do While loop. The looping continues as long as the cell being evaluated is not equal to MaxVal. When the cell is equal to MaxVal, the loop ends, and the function is assigned the value of r. Notice that if the maximum value is in row 1, the looping does not occur.

The following procedure uses the second Do While loop syntax. The loop always executes at least once:

Function ROWOFLARGEST(c)
    MaxVal = Application.Max(Columns(c))
    r = 0
    Do
        r = r + 1
    Loop While Cells(r, c) <> MaxVal
    ROWOFLARGEST = r
End Function

Do While loops can also contain one or more Exit Do statements. When an Exit Do statement is encountered, the loop ends immediately.

Do Until loops

The Do Until loop structure closely resembles the Do While structure. The difference is evident only when the condition is tested. In a Do While loop, the loop executes while the condition is TRUE. In a Do Until loop, the loop executes until the condition is TRUE. Do Until also has two syntaxes:

Do [Until condition]
    [instructions]
    [Exit Do]
    [instructions]
Loop

or

Do
    [instructions]
    [Exit Do]
    [instructions]
Loop [Until condition]

The following example demonstrates the first syntax of the Do Until loop. This example makes the code a bit clearer because it avoids the negative comparison required in the Do While example:

Function ROWOFLARGEST4(c)
    NumRows = Rows.Count
    MaxVal = Application.Max(Columns(c))
    r = 1
    Do Until Cells(r, c) = MaxVal
        r = r + 1
    Loop
    ROWOFLARGEST4 = r
End Function

Finally, the following function is the same procedure but is rewritten to use the second syntax of the Do Until loop:

Function ROWOFLARGEST5(c)
    NumRows = Rows.Count
    MaxVal = Application.Max(Columns(c))
    r = 0
    Do
        r = r + 1
    Loop Until Cells(r, c) = MaxVal
    ROWOFLARGEST5 = r
End Function

The Range property

You can use the Range property to refer to a range directly by using a cell address or name. The following example assigns the value in cell A1 to a variable named Init. In this case, the statement accesses the range’s Value property.

Init = Range("A1").Value

In addition to the Value property, VBA enables you to access a number of other properties of a range. For example, the following statement counts the number of cells in a range and assigns the value to the Cnt variable:

Cnt = Range("A1:C300").Count

The Range property is also useful for referencing a single cell in a multicell range. For example, you may create a function that is supposed to accept a single-cell argument. If the user specifies a multicell range as the argument, you can use the Range property to extract the upper-left cell in the range. The following example uses the Range property (with an argument of “A1”) to return the value in the upper-left cell of the range represented by the cell argument:

Function SQUARE(cell as Range)
    CellValue = cell.Range("A1").Value
    SQUARE = CellValue ^ 2
End Function

Assume that the user enters the following formula:

=SQUARE(C5:C12)

The SQUARE function works with the upper-left cell in C5:C12 (which is C5) and returns the value squared.

The Cells property

Another way to reference a range is to use the Cells property. The Cells property accepts two arguments (a row number and a column number) and returns a single cell. The following statement assigns the value in cell A1 to a variable named FirstCell:

FirstCell = Cells(1, 1).Value

The following statement returns the upper-left cell in the range C5:D12:

UpperLeft = Range("C5:D12").Cells(1,1).Value

The following statement uses the Excel COUNTA function to determine the number of nonempty cells in the worksheet:

NonEmpty =WorksheetFunction.COUNTA(Cells)

The Offset property

The Offset property (like the Range and Cells properties) also returns a Range object. The Offset property is used in conjunction with a range. It takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive (down or right), negative (up or left), or zero. The following example returns the value one cell below cell A1 (that is, cell A2) and assigns it to a variable named NextCell:

NextCell = Range("A1").Offset(1,0).Value

The following Function procedure accepts a single-cell argument and returns the sum of the eight cells that surround it:

Function SUMSURROUNDINGCELLS(cell)
    Dim Total As Double
    Dim r As Long, c As Long
    Total = 0
    For r = –1 To 1
        For c = –1 To 1
            Total = Total + cell.Offset(r, c)
        Next c
    Next r
    SUMSURROUNDINGCELLS = Total – cell
End Function

This function uses a nested For-Next loop. So when the r loop counter is –1, the c loop counter goes from –1 to 1. Nine cells are summed, including the argument cell, which is Offset(0, 0). The final statement subtracts the value of the argument cell from the total. The function returns an error if the argument does not have eight surrounding cells (for example, if it’s in row 1 or column 1).

To better understand how the nested loop works, following are nine statements that perform the same calculation:

Total = Total + cell.Offset(–1, –1) ' upper left
Total = Total + cell.Offset(–1, 0) 'left
Total = Total + cell.Offset(–1, 1) 'upper right
Total = Total + cell.Offset(0, –1) 'above
Total = Total + cell.Offset(0, 0) 'the cell itself
Total = Total + cell.Offset(0, 1) 'right
Total = Total + cell.Offset(1, –1) 'lower left
Total = Total + cell.Offset(1, 0) 'below
Total = Total + cell.Offset(1, 1) 'lower right

The Formula property

The Formula property returns the formula (as a string) contained in a cell. If you try to access the Formula property for a range that consists of more than one cell, you get an error. If the cell does not have a formula, this property returns a string, which is the cell’s value as it appears in the Formula bar. The following function simply displays the formula for the upper-left cell in a range:

Function CELLFORMULA(cell)
    CELLFORMULA = cell.Range("A1").Formula
End Function

You can use the HasFormula property to determine whether a cell has a formula.

The Address Property

The Address property returns the address of a range as a string. By default, it returns the address as an absolute reference (for example, $A$1:$C$12). The following function, which is not all that useful, returns the address of a range:

Function RANGEADDRESS(rng)
    RANGEADDRESS = rng.Address
End Function

For example, the following formula returns the string $A$1:$C$3:

=RANGEADDRESS(A1:C3)

The formula that follows returns the address of a range named MyRange:

=RANGEADDRESS(MyRange)

The Count property

The Count property returns the number of cells in a range. The following function uses the Count property:

Function CELLCOUNT(rng)
    CELLCOUNT = rng.Count
End Function

The following formula returns 9:

=CELLCOUNT(A1:C3)

The Parent property

The Parent property returns an object that corresponds to an object’s container object. For a Range object, the Parent property returns a Worksheet object (the worksheet that contains the range).

The following function uses the Parent property and returns the name of the worksheet of the range passed as an argument:

Function SHEETNAME(rng)
    SHEETNAME = rng.Parent.Name
End Function

The following formula, for example, returns the string Sheet1:

=SHEETNAME(Sheet1!A16)

The Name property

The Name property returns a Name object for a cell or range. To get the actual cell or range name, you need to access the Name property of the Name object. If the cell or range does not have a name, the Name property returns an error.

The following Function procedure displays the name of a range or cell passed as its argument. If the range or cell does not have a name, the function returns an empty string. Note the use of On Error Resume Next. This handles situations in which the range does not have a name:

Function RANGENAME(rng)
    On Error Resume Next
    RANGENAME = rng.Name.Name
    If Err.Number <> 0 Then RANGENAME = ""
End Function

The NumberFormat property

The NumberFormat property returns the number format (as a string) assigned to a cell or range. The following function simply returns the number format for the upper-left cell in a range:

Function NUMBERFORMAT(cell)
    NUMBERFORMAT = cell.Range("A1").NumberFormat
End Function

The Font property

The Font property returns a Font object for a range or cell. To actually do anything with this Font object, you need to access its properties. For example, a Font object has properties such as Bold, Italic, Name, Color, and so on. The following function returns TRUE if the upper-left cell of its argument is formatted as bold:

Function ISBOLD(cell)
    ISBOLD = cell.Range("A1").Font.Bold
End Function

The EntireRow and EntireColumn properties

The EntireRow and EntireColumn properties enable you to work with an entire row or column for a particular cell. The following function accepts a single cell argument and then uses the EntireColumn property to get a range consisting of the cell’s entire column. It then uses the Excel COUNTA function to return the number of nonempty cells in the column:

Function NONEMPTYCELLSINCOLUMN(cell)
    NONEMPTYCELLSINCOLUMN = WorksheetFunction.CountA(cell.EntireColumn)
End Function

You cannot use this function in a formula that’s in the same column as the cell argument. Doing so generates a circular reference.

The Hidden property

The Hidden property is used with rows or columns. It returns TRUE if the row or column is hidden. If you try to access this property for a range that does not consist of an entire row or column, you get an error. The following function accepts a single cell argument and returns TRUE if either the cell’s row or the cell’s column is hidden:

Function CELLISHIDDEN(cell)
    If cell.EntireRow.Hidden Or cell.EntireColumn.Hidden Then
        CELLISHIDDEN = True
    Else
        CELLISHIDDEN = False
    End If
End Function

You can also write this function without using an If-Then-Else construct. In the following function, the expression to the right of the equal sign returns either TRUE or FALSE—and this value is returned by the function:

Function CELLISHIDDEN(cell)
    CELLISHIDDEN = cell.EntireRow.Hidden Or cell.EntireColumn.Hidden
End Function