All computer languages provide a mechanism for repeating the same, or similar, operations in an efficient way. VBA has two main structures that allow you to loop through the same code over and over again. They are the Do...Loop and the For...Next loop.

The Do...Loop is for those situations where the loop will be terminated when a logical condition applies, such as reaching the end of your data. The For...Next loop is for situations where you can predict in advance how many times you want to loop, such as when you want to enter expenses for the 10 people in your department.

VBA also has an interesting variation on the For...Next loop that is used to process all the objects in a collection — the For Each...Next loop. You can use it to process all the cells in a range or all the sheets in a workbook, for example.

To illustrate the use of a Do...Loop, construct a sub procedure to shade every second line of a worksheet, as shown in Figure 1-27, to make it more readable. You want to apply the macro to different report sheets with different numbers of products, so the macro will need to test each cell in the A column until it gets to an empty cell to determine when to stop.

C!n J

^ kJ 7

■ Of'pví- TI

Vi.r-Triïrrft Excel

- i)7 ¡p.

--S & HDITlt

Insert Page Layout

Formulas Dgis

ReVictV Vkeift Devetoper

& -







G H 1 J



1 1 Jan

Feb Mar

Apr May Jun

Jul Aug Sep

Oct Nov Dec

2 Productl

I lproduct2

J Products

5 |product4

6 Products

7 lproduct&

R Product7

9 iProductS

10 Products

11 |productlO

12 Productli

13 |productl2

11 Productif


16 Productif

17 |Praductl5

IS Productl7


20 Products

21 Product20






Figure 1-27

The first macro will select every other row and apply the formatting:

Sub ShadeEverySecondRow()

Range("A2").EntireRow.Select Do While ActiveCell.Value <> ""

Selection.Interior.Colorlndex = 15 ActiveCell.Offset(2, 0).EntireRow.Select Loop End Sub

ShadeEverySecondRow begins by selecting row 2 in its entirety. When you select an entire row, the leftmost cell (in column A) becomes the active cell. The code between the Do and Loop statements is then repeated While the value property of the active cell is not a zero-length string, that is, the active cell is not empty. In the loop, the macro sets the interior color index of the selected cells to 15, which is gray. Then the macro selects the entire row, two rows under the active cell. When a row is selected that has an empty cell in column A, the While condition is no longer true and the loop terminates.

You can make ShadeEverySecondRow run faster by avoiding selecting. It is seldom necessary to select cells in VBA, but you are led into this way of doing things because that's the way you do it manually, and that's what you get from the macro recorder.

The following version of ShadeEverySecondRow does not select cells, and it runs considerably faster. It sets up an index lRow, which indicates the row of the worksheet and is initially assigned a value of 2. The Cells property of the worksheet allows you to refer to cells by row number and column number, so when the loop starts, Cells(lRow,1) refers to cell A2. Each time around the loop, i is increased by two. You can, therefore, change any reference to the active cell to a Cells(lRow,1) reference and apply the EntireRowproperty to Cells(lRow,1) to refer to the complete row:

Sub ShadeEverySecondRow()

Dim lRow As Long

lRow= 2

Do Until IsEmpty(Cells(lRow, 1))

Cells(lRow, 1).EntireRow.Interior.ColorIndex =

= 15

lRow= lRow+ 2


End Sub

To illustrate some alternatives, two more changes have been made on the Do statement line in the previous code. Either While or Until can be used after the Do, so the test has been changed to an Until and you have used the VBA isEmpty function to test for an empty cell.

The IsEmpty function is the best way to test that a cell is empty. If you use If Cells (lRow,1) = "", the test will be true for a formula that calculates a zero-length string.

It is also possible to exit a loop using a test within the loop and the Exit Do statement, as follows, which also shows another way to refer to entire rows:

Sub ShadeEverySecondRow() Dim lRow as Long lRow= 0 Do lRow= lRow+ 2

If IsEmpty(Cells(lRow, 1)) Then Exit Do Rows(lRow).Interior.ColorIndex = 15 Loop End Sub

Yet another alternative is to place the While or Until on the Loop statement line. This ensures that the code in the loop is executed at least once. When the test is on the Do line, it is possible that the test will be false to start with, and the loop will be skipped.

Sometimes, it makes more sense if the test is on the last line of the loop. In the following example, it seems more sensible to test sPassWord after getting input from the user, although the code would still work if the Until statement were placed on the Do line:

Sub GetPassword()

Dim sPassWord As String, i As


i = 0


i = i + 1

If i > 3 Then

MsgBox "Sorry, Only three


Exit Sub

End If

sPassWord = InputBox(

"Enter Password")

Loop Until sPassWord =

= "XXX"

MsgBox "Welcome"

End Sub

GetPassword loops until the password XXX is supplied, or the number of times around the loop exceeds three.

For...Next Loop

The For...Next loop differs from the Do...Loop in two ways. It has a built-in counter that is automatically incremented each time the loop is executed, and it is designed to execute until the counter exceeds a predefined value, rather than depending on a user-specified logical test. The following example places the full file path and name of the workbook into the center footer for each worksheet in the active workbook:

Sub FilePathInFooter()

Dim i As Integer, sFilePath As String

sFilePath = ActiveWorkbook.FullName

For i = 1 To Worksheets.Count Step 1

Worksheets(i).PageSetup.CenterFooter =

= sFilePath

Next i

End Sub

Versions of Excel prior to Excel 2002 do not have an option to automatically include the full file path in a custom header or footer, so this macro inserts the information as text. It begins by assigning the FullName property of the active workbook to the variable sFilePath. The loop starts with the For statement and loops on the Next statement. i is used as a counter, starting at 1 and finishing when i exceeds Worksheets.Count, which uses the Count property of the Worksheets collection to determine how many worksheets there are in the active workbook.

The Step option defines the amount that i will be increased each time around the loop. Step 1 could be left out of this example, because a step of 1 is the default value. In the loop, i is used as an index to the Worksheets collection to specify each individual Worksheet object. The PageSetup property of the Worksheet object refers to the PageSetup object in that worksheet, so that the CenterFooter property of the PageSetup object can be assigned the sFilePath text.

The following example shows how you can step backwards. It takes a complete file path and strips out the filename, excluding the file extension. The example uses the FullName property of the active workbook as input, but the same code could be used with any file path. It starts at the last character in the file path and steps backwards until it finds the period between the filename and its extension, and then the backslash character before the filename. It then extracts the characters between the two:

Sub GetFileName()

Dim iBackSlash As Integer, iPoint As Integer Dim sFilePath As String, sFileName As String Dim i As Integer sFilePath = ActiveWorkbook.FullName For i = Len(sFilePath) To 1 Step -1

If Mid$(sFilePath, i, 1) = "." Then

iPoint = i

Exit For

End If

Next i

If iPoint = 0 Then iPoint = Len(sFilePath) + 1

For i = iPoint - 1 To 1 Step -1

If Mid$(sFilePath, i, 1) = "\" Then

iBackSlash = i

Exit For

End If

Next i

sFileName = Mid$(sFilePath, iBackSlash + 1, iPoint -

- iBackSlash - 1)

MsgBox sFileName

End Sub

The first For...Next loop uses the Len function to determine how many characters are in the sFilePath variable, and i is set up to step backwards, counting from the last character position, working toward the first character position. The Mid$ function extracts the character from sFilePath at the position defined by i and tests it to see if it is a period.

When a period is found, the position is recorded in iPoint and the first For...Next loop is exited. If the filename has no extension, no period is found and iPoint will have its default value of 0. In this case, the If test records an imaginary period position in iPoint that is one character beyond the end of the filename.

The same technique is used in the second For...Next loop as the first, starting one character before the period, to find the position of the backslash character, and storing the position in iBackSlash. The Mid$ function is then used to extract the characters between the backslash and the period.

For Each...Next Loop

When you want to process every member of a collection, you can use the For Each...Next loop. The following example is a rework of the FilePathInFooter procedure:

Sub FilePathInFooter()

Dim sFilePath As String, wks As Worksheet sFilePath = ActiveWorkbook.FullName For Each wks In Worksheets wks.PageSetup.CenterFooter = sFilePath Next wks End Sub

The loop steps through all the members of the collection. During each pass, a reference to the next member of the collection is assigned to the object variable wks.

The following example lists all the files in the root directory of the C: drive. It uses the Windows Scripting FileSystemObject to create a reference to the C drive root directory. The example uses a For Each...Next loop to display the names of all the files in the directory:

Sub FileList()

'Listing files with a For...Each loop

Dim objFSO As Object Dim objFolder As Object Dim objFile As Object

'Create a reference to the FileSystemObject

Set objFSO = CreateObject("Scripting.FileSystemObject")

'Create a folder reference

Set objFolder = objFSO.GetFolder("C:\")

'List files in folder

For Each objFile In objFolder.Files

MsgBox objFile.Name Next objFile

End Sub

The code uses techniques that are discussed in Chapter 19 to reference objects outside the Excel object model. If you test this procedure on a directory with lots of files, and get tired of clicking OK, don't forget that you can break out of the code with Ctrl+Break.

0 0

Post a comment