Listing Continued

Application.Wait Now + TimeValue("00:00:01") Next counter

Application.StatusBar = False End Sub

¡^ The LoopTest procedure works fine in Excel, but it will fail in the other Office applications because

® they don't implement the Wait method. If you need to get your code to delay for a short while, here's a simple procedure that does the trick:

Sub VBAWait(delay As Integer) Dim startTime As Long startTime = Timer

Do While Timer - startTime < delay DoEvents

Loop End Sub

Note the use of the DoEvents function inside the Do While...Loop structure.This function yields execution to the operating system so that events such as keystrokes and application messages are processed while the procedure delays.

Here are some notes on For...Next loops:

If you use a positive number for increment (or if you omit increment), end must be greater than or equal to start. If you use a negative number for increment, end must be less than or equal to start. If start equals end, the loop will execute once.

As with If...Then...Else structures, indent the statements inside a For...Next loop for increased readability.

To keep the number of variables defined in a procedure to a minimum, always try to use the same name for all your For...Next loop counters. The letters i through n traditionally are used for counters in programming. For greater clarity, you might want to use names such as "counter."

For the fastest loops, don't use the counter name after the Next statement. If you'd like to keep the counter name for clarity (which I recommend), precede the name with an apostrophe (') to comment out the name, like this:

For counter = 1 To 10

[ statements] Next 'counter

If you need to break out of a For...Next loop before the defined number of repetitions is completed, use the Exit For statement, described in the section "Using Exit For or Exit Do to Exit a Loop."

Using For Each...Next Loops 109

Using For Each...Next Loops

A useful variation of the For...Next loop is the For Each...Next loop, which operates on a collection of objects. You don't need a loop counter because VBA just loops through the individual elements in the collection and performs on each element whatever operations are inside the loop. Here's the structure of the basic For Each...Next loop:

For Each element In collection

[ statements] Next [ element]

element A variable used to hold the name of each element in the collection.

collection The name of the collection.

statements The statements to be executed for each element in the collection.

As an example, let's create a command procedure that converts a range of text into proper case (that is, the first letter of each word is capitalized). This function can come in handy if you import mainframe text into your worksheets because mainframe reports usually appear entirely in uppercase. This process involves three steps:

1. Loop through the selected range with For Each...Next.

2. Convert each cell's text to proper case. Use Excel's Proper() worksheet function to handle this:

WorksheetFunction(Proper(text)) text The text to convert to proper case.

3. Enter the converted text into the selected cell. This is the job of the Range object's Formula method:

object.Formula = expression object The Range object in which you want to enter expression.

expression The data you want to enter into object.

Listing 6.13 shows the resulting procedure, ConvertToProper. Note that this procedure uses the Selection object to represent the currently selected range.

0 0

Post a comment