Control Visual Basic in Microsoft Office Excel 2007 Visual Basic for Applications

5/16/2007

Contents

Creating Loops

Long before Henry Ford, and even before Marc Brunel, the economist Adam Smith reasoned that in a single day, a single worker could make only one straight pin, but ten people could subdivide the work and create 48,000 pins in the same day—an almost 5,000-fold increase in productivity. Similarly, you can get amazing increases in productivity by converting a macro that runs once into one that runs thousands of times in a loop.

Loop Through a Collection by Using a For Each Loop

Excel allows you to protect a worksheet so that users can change only cells that are explicitly unlocked. You must, however, protect each sheet individually. Suppose that you have a workbook containing budgets for ten different departments and that you want to protect all the worksheets.

The Flow text file includes a macro named ProtectSheets. Here’s what it looks like:

Sub ProtectSheets()
    Dim mySheet As Worksheet
    Set mySheet = Worksheets(1)
    mySheet.Select
    mySheet.Protect "Password", True, True, True
End Sub

This macro assigns a reference to the first worksheet to the mySheet variable, selects that sheet, and then protects it. (Selecting the sheet really isn’t necessary, but it makes it easier to see what the macro is doing.) Now see how you can convert this macro to protect all the worksheets in the workbook.

Copy the ProtectSheets macro from the text file, and paste it into a VBA module in the Chapter07 workbook.
Click in the ProtectSheets macro, and press F8 repeatedly to step through the macro. Make sure you understand everything that the original macro does.
In the third line, replace Set with For Each, replace the equal sign with In, and remove the parentheses and the number between them.
Indent the two statements that begin with mySheet, add a new line, and then type the statement Next mySheet.

The finished macro should look like this:
```
Sub ProtectSheets()
    Dim mySheet As Worksheet
    For Each mySheet In Worksheets
        mySheet.Select
        mySheet.Protect "Password", True, True, True
    Next mySheet
End Sub
```
The For Each statement acts just like Set: It assigns an object reference to a variable. But instead of assigning a single object to the variable, it assigns each item from a collection to the variable. Then, for each (get it?) object in the collection, Visual Basic executes all the statements down to the Next statement. (Technically, you don’t need to put the variable name after Next. If you do use it, Visual Basic requires that it match the variable name after For Each. Always use the loop variable after Next so that Visual Basic can help you avoid creating bugs in your macros.) Statements beginning with For Each and ending with Next are called For Each blocks or For Each loops.
Press F8 repeatedly to step through the macro, watching as it works on each worksheet in turn.
Switch to Excel, and try typing a value into a cell on any worksheet. Afterwards, close the error message box that opens.
Create a new macro named UnprotectSheets that unprotects all the worksheets.

Try to write the macro without looking at the finished code that follows. Hint: You’ll need to use the Unprotect method of the worksheet object, with a single argument that gives the password.

TIP

A For Each loop is a handy way of browsing collections in the Immediate window. However, in the Immediate window, everything you type must be on a single line. You can put multiple statements on a single line by separating the statements with colons. For example, here’s what you’d type in the Immediate window to see the names of all the worksheets in the active workbook: For Each x In Worksheets: ?x.Name: Next x. (In the Immediate window, it’s all right to use short, meaningless names for variables.)

Here’s what the UnprotectSheets macro should look like:
```
Sub UnprotectSheets()
    Dim mySheet As Worksheet
    For Each mySheet In Worksheets
        mySheet.Select ' This statement is optional.
        mySheet.Unprotect "Password"
    Next mySheet
End Sub
```
Save the workbook, press F5 to run the UnprotectSheets macro, and then test it by changing a value on a worksheet.

Looping through a collection is almost as easy as assigning a single object to a variable. The only differences are that you use For Each instead of Set, you specify a collection to loop through, and you add a Next statement to end the loop.

Loop with a Counter by Using a For Loop

Sometimes you want to perform actions repeatedly but can’t use a For Each loop. For example, a For Each loop can work through only a single collection. If you want to compare two parallel collections—such as two ranges—you can’t use a For Each loop. In that situation, Visual Basic has another, more generalized way to loop: a For loop.

The Compare worksheet in the Chapter07 workbook contains two named ranges. The one on the left is named Old, and the one on the right is named New. You can think of these as being an original forecast and a revised forecast. The cells in the Old range contain values. The cells in the New range contain a formula that will calculate a random number each time you press F9 to recalculate the workbook. (The formula in those cells is =ROUND(RAND()*50+100,0), which tells Excel to calculate a random number between 0 and 1, multiply it by 50, add 100, and round to the nearest whole number. Because the numbers in the New range are randomly generated, the ones you see will differ from the ones in this graphic.)

The Flow text file contains a macro named CompareCells, which looks like this:

Sub CompareCells()
    Dim i As Integer
    Calculate
    If Range("New").Cells(i) > Range("Old").Cells(i) Then
        Range("New").Cells(i).Interior.Color = rgbLightGreen
    Else
        Range("New").Cells(i).Interior.Color = rgbLightSteelBlue
    End If
End Sub

The macro first executes the Calculate method, which calculates new values for all the cells in the New range. Then the macro compares only the last cell in the New range with the last cell in the Old range. If the New value for that one cell is greater than the Old value, the cell turns light green; otherwise, it turns light steel blue. The macro assigns the Count of cells in the range to the variable i, which is a simple integer.

Now see how you can convert this macro to use a loop to compare and color all the cells in the New range.

Copy the CompareCells macro from the text file, and paste it into a VBA module in the Chapter07 workbook.
Click in the CompareCells macro, and press F8 repeatedly to step through the macro. Make sure you understand everything the original macro does.
In the statement that assigns the Count to the variable, insert the word For in front of the variable, and then insert 1 To after the equal sign.
Type Next i before the End Sub statement, and indent all the statements between For and Next.

The finished macro should look like this:
```
Sub CompareCells()
    Dim i As Integer
    Calculate
    For i = 1 To Range("New").Cells.Count
        If Range("New").Cells(i) > Range("Old").Cells(i) Then
            Range("New").Cells(i).Interior.Color = rgbLightGreen
        Else
            Range("New").Cells(i).Interior.Color = rgbLightSteelBlue
        End If
    Next i
End Sub
```
The keyword For works just like a simple assignment statement. It assigns a number to the variable. (The For statement assigns a number to an integer variable, while the For Each statement assigns a reference to an object variable.) The variable that holds the number is called a loop counter. You specify the start value for the loop counter (in this case, 1) and the stop value (in this case, the total number of cells in the range).

The For loop assigns the start value to the loop counter, executes all the statements down to the Next statement, adds 1 to the loop counter, and checks the loop counter against the stop value. If the loop counter is greater than the stop value, the For loop jumps to just past the Next statement. If the loop counter is less than or equal to the stop value, the For loop does it all again.
Press F8 repeatedly to watch the macro work. Step through at least two or three loops, and then press F5 to finish the macro.

In many cases, using a For Each loop is more convenient than using a For loop. However, a For loop is a more general tool: you can always use a For loop to reproduce the behavior of a For Each loop. For example, here’s how you could write the ProtectSheets macro without using For Each:

Sub ForProtectSheets()
    Dim mySheet As Worksheet
    Dim i As Integer
    For i = 1 to Worksheets.Count
        Set mySheet = Worksheets(i)
        mySheet.Select
        mySheet.Protect "Password", True, True, True
    Next i
End Sub

The For loop is a little more dangerous than a For Each loop because you have to be sure to get the start and stop values correct. If you have a stop value that is smaller than the start value, the loop will run forever—a condition known as an infinite loop. With a For Each loop, it is impossible to create an infinite loop.

Loop Indefinitely by Using a Do Loop

A For Each loop works through a collection. A For loop cycles through numbers from a starting point to an ending point. In some situations, however, neither of these options works.

For example, suppose that you want to retrieve the names of all the Excel workbooks in the current folder. Visual Basic has a function that tells you the names of files in a folder (or directory). The function is named Dir, after the old MS-DOS operating system command of the same name. The first time you use Dir, you give it an argument that tells which kind of files you want to look at. To retrieve the name of the first Excel workbook in the current directory, you use the statement myFile = Dir(“*.xlsx”). To get the next file that matches the same pattern, you use Dir again, but without an argument. You must run Dir repeatedly because it returns only one file name at a time. When Visual Basic can’t find another matching file, the Dir function returns an empty string.

So how do you create a macro that retrieves the names of all the Excel files in the current folder? The list of files in the directory isn’t a collection, so you can’t use a For Each loop. You can’t use a For loop either because you don’t know how many files you’ll get until you’re finished. Fortunately, Visual Basic has one more way of controlling a loop: a Do loop.

The ListFiles macro in the Flow text file retrieves the first two Excel files from the current directory and puts their names into the first two cells of the first column of the active worksheet. Here’s the original macro:

Sub ListFiles()
    Dim myRow As Integer
    Dim myFile As String
    myRow = 1
    myFile = Dir("*.xls")
    Cells(myRow, 1) = myFile
    myRow = myRow + 1
    myFile = Dir
    Cells(myRow, 1) = myFile
End Sub

Aside from the variable declaration statements, this macro consists of two groups of three statements each. In each group, the macro assigns a row number to myRow, retrieves a file name using the Dir function, and then puts the file name into the appropriate cell. The first time the macro uses Dir, it specifies the pattern to match. The next time, the macro uses Dir without an argument so that it will retrieve the next matching file.

Now see how you can convert this macro to loop until it has found all the files in the folder.

Copy the ListFiles macro from the text file, and paste it into a VBA module in the Chapter07 workbook.
In the Chapter07 workbook, activate the Files worksheet.
Make sure the current folder is the one containing the practice files for this book. (Click the Microsoft Office Button, click Open, change to the correct folder, and then click Cancel.)
In the Visual Basic editor, click in the ListFiles macro, and press F8 repeatedly to step through the macro. (The names of the files your macro retrieves might differ from those in the graphics.) Make sure you understand the original macro.

TIP

As you step through the macro, move the mouse pointer over a variable name to see the current value stored in that variable.
At the end of the first statement that contains a Dir function, insert a new line, and type Do Until myFile = “” (There is no space between the quotation marks.)

This statement begins the loop. You begin the loop after the first Dir function because you use Dir with an argument only once.
At the end of the second statement that contains a Dir function, insert a new line, and type Loop.

This statement ends the loop and sends Visual Basic back to the start of the loop to check if it’s time to quit.
Delete the second Cells(myRow, 1) = myFile statement.

You don’t need this statement because the loop repeats the assignment statement as many times as needed.
Just before the myRow = 1 statement, insert a line, and then enter the statement Cells.Clear.

This ensures that the worksheet is empty in case you run the macro multiple times and some lists are shorter than others.

TIP

When you use a macro to write a list onto a worksheet, make sure there are no old lists left in the worksheet. You can use Cells.Clear to erase the worksheet, or use Worksheets.Add to create a new one.
Indent the three statements between the Do and Loop statements.

The revised macro should look like this:
```
Sub ListFiles()
    Dim myRow As Integer
    Dim myFile As String
    Cells.Clear
    myRow = 1
    myFile = Dir("*.xlsx")
    Do Until myFile = ""
        Cells(myRow, 1) = myFile
        myRow = myRow + 1
        myFile = Dir
    Loop
End Sub
```
The myFile = “” expression at the end of the Do Until statement is a conditional expression, precisely like one you’d use with an If statement. The conditional expression must be something that Visual Basic can interpret as either True or False. Visual Basic simply repeats the loop over and over until the conditional expression is True. Note that the condition may never be true, in which case the loop will never execute. For example, if there were no .xlsx files in the folder, the stop condition would be true the very first time it executes.

If you want to increment a number during the loop, you must enter a statement to do so. You must always be careful to cause something to happen during the loop that will allow the loop to end. In this case, you retrieve a new file name from the Dir function.
Press F8 repeatedly to watch the macro work. Step through at least two or three loops, and then press F5 to finish the macro.

A Do loop is the most flexible of all the looping structures. Anything that you can do with a For loop or a For Each loop, you can do with a Do loop. If you had to be stranded on a desert island with only one loop structure, the Do loop would be the best one to have. For example, here is how you could write the ProtectSheets macro by using a Do loop.

Sub ProtectSheets()
    Dim mySheet As Worksheet
    Dim i As Integer
    i = 1
    Do Until i > Worksheets.Count
        Set mySheet = Worksheets(i)
        mySheet.Select
        mySheet.Protect "Password", True, True, True
        i = i + 1
    Loop
End Sub

The flexibility makes the Do loop a little more complicated than the others because you have to create and increment your own loop variable and provide your own condition for ending the loop. This makes a Do loop particularly vulnerable to becoming an infinite loop. For example, if you forgot to add the statement to retrieve a new file name, or if you had included the argument to the Dir function inside the loop (so that Dir would keep returning the first file name over and over), you’d have an infinite loop.

Save to your account