Implementing Loops

Looping, or executing a section of code repeatedly, is a common need in programming. There are two classes of loops: fixed loops, have a definite, known number of times that they execute, and variable loops generally rely on a logical expression to determine whether looping should continue or not.

Fixed Loops: For...Next

Not only are For.. .Next loops easy to use, they're also fairly flexible. I show the general structure of a For.. .Next loop in the following simple procedure. This procedure prints the numbers 1 to 50 in the Immediate window.

Sub Simp1eForNext() Dim n As Integer

Next End Sub

As the following example illustrates, you can also go backward and use increments other than 1. The following procedure prints even numbers in descending order from 50 to 1 to the Immediate window.

Sub ReverseForNext() Dim n As Integer

Next End Sub

Debugging with Debug.Print

Debug.Print is useful during the development process to check various aspects of your code. Debug.Print [output] prints the output to the Immediate window. For example, by sprinkling Debug.Print statements in your code, you can record how a particular variable of interest changes as your program executes. You can print multiple variables at once by separating each variable with a comma as shown in the following example.

Sub mustrateDebugO

Dim x As Integer

Dim y As Integer

Debug.Print "-X-", "-Y-" For x = 1 To 10


Next End Sub

Often you'll use For.. .Next to loop through various workbook and worksheet objects such as worksheets, rows, columns, open workbooks, and so on. Listing 3.1 presents one way to loop through all of the worksheets in a workbook.

Listing 3.1: Looping Through Worksheets in a Workbook

Sub WorksheetLoop()

Dim nIndex As Integer

For nIndex = 1 To ThisWorkbook.Worksheets.Count

Debug.Print ThisWorkbook.Worksheets(nIndex).Name

Next End Sub

In Listing 3.1, I simply declared an integer variable named nIndex and used a For.. .Next loop to work my way through each worksheet, printing the name of each worksheet as I went. I'll cover the details of ThisWorkbook later in Chapter 5, but it is probably apparent what I'm doing with it here. ThisWorkbook is just a shorthand way to refer to the workbook that contains the code.

For...Each Variation

Often the items that you need to loop through are objects in a collection. A collection is a kind of object that contains like objects. For example, the Worksheets collection contains all of the worksheet objects in a workbook. VBA provides the statement For.. .Each specifically to loop through collections. For example, Listing 3.1 could also be written as follows:

Sub WorksheetLoop2() Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets Debug.Print ws.Name

Next End Sub

This variation can also be useful for looping through the elements in an array as demonstrated here:

Sub ArrayLoop()

Dim avColors As Variant Dim vItem As Variant avColors = Array("Red", "Green", "Blue")

For Each vItem In avColors Debug.Print vItem

Next End Sub

This procedure creates an array using the handy Array function that assigns an array to a variant variable. It then loops through the elements in the array using For.. .Each.

Before I move on to the next topic, I should point out one more thing you can do within a fixed loop. Occasionally, you may need to exit a For.. .Next loop before completing all of the iterations. To exit a For.. .Next loop early, you can use the Exit For statement. For example, suppose you are looping through a bunch of cells on a worksheet looking for a cell that exhibits a certain characteristic. Once you find the cell, you don't need to look in the remaining cells. Listing 3.2 demonstrates this functionality.

Listing 3.2: Exiting a For...Next Loop Early

Sub ExitForExample()

Dim nLastRow As Integer Dim nColumn As Integer Dim nRow As Integer Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(1) nLastRow = 15 nColumn = 1

For nRow = 1 To nLastRow

If ws.Cells(nRow, nColumn).Address = "$A$7" Then

Debug.Print "Found cell. Exiting for loop."

Exit For


Debug.Print ws.Cells(nRow, nColumn).Address End If


Set ws = Nothing

End Sub

This listing draws on many of the topics we've discussed and some that we've yet to discuss. Let's take a minute to analyze this listing and review what we've discussed. First, notice the empty rows between some of the statements in this listing. These empty rows serve to break the procedure into four logical groupings. The first grouping contains the subroutine declaration and declares the variables the procedure will use. The second grouping initializes any applicable variables. This procedure uses an object variable named ws to refer to a worksheet. Notice that object variables are assigned using the Set keyword. The third grouping represents the main body of the procedure—where the logic of the procedure is implemented. The final grouping dereferences any object variables and formally marks the end of the procedure.

To dereference an object variable is to explicitly free the memory associated with the variable. Dereferencing object variables is not technically required, but it is good programming practice. Remember, your goal should be to write code that clearly and explicitly expresses your true intentions.

By explicitly dereferencing an object variable, you indicate that you are through using the object. You'll experience some technical benefits as well, but they are just gravy for now.

Variable Loops: Do...Loop

You can use Do loops to create a repetitive code sequence that repeats until it finds a terminating condition. In fact, a terminating condition isn't even required. A Do.. .Loop without a terminating condition keeps repeating indefinitely. The Do.. .Loop has two variations. Here is the general syntax of the first variation:

Do [{While | Until} condition] ' your code [Exit Do] ' your code


Using Do.. .While causes the loop to continue while a condition is true. The Do.. .Until form loops until the condition becomes true. Exit Do allows you to exit the Do loop immediately from within the loop.

Here is the general syntax of the second variation:

' your code [Exit Do] ' your code Loop [{While | Until} condition]

The difference is that the first variation won't execute the statement block within the Do. Loop if the condition is false to begin with (assuming you're using Do.. .While). With the second variation, the statement block with the Do.. .Loop always executes at least once.

Do loops have the potential to hang your application in the form of an endless loop. It almost goes without saying then that you must take great care in ensuring that you choose a terminating condition that is a sure thing.

0 0

Post a comment