Sub SelectionType() Select Case TypeName(Selection) Case "Range" Select Case Selection.Count Case 1 MsgBox "One cell is selected" Case Else MsgBox Selection.Rows.Count & " rows" End Select Case "Nothing" MsgBox "Nothing is selected" Case Else MsgBox "Something other than a range" End Select End Sub
This procedure also demonstrates the use of Case Else, a catchall case. You can nest Select Case constructs as deeply as you need, but make sure that each Select Case statement has a corresponding End Select statement.
This procedure demonstrates the value of using indentation in your code to clarify the structure. For example, take a look at the same procedure without the indentations:
Sub SelectionType() Select Case TypeName(Selection) Case "Range" Select Case Selection.Count Case 1 MsgBox "One cell is selected" Case Else MsgBox Selection.Rows.Count & " rows"Case "Nothing" MsgBox "Nothing is selected" Case Else MsgBox "Something other than a range" End Select End Sub
Fairly incomprehensible, eh?
Looping blocks of instructions
Looping is the process of repeating a block of instructions. You might know the number of times to loop, or the number may be determined by the values of variables in your program.
The following code, which enters consecutive numbers into a range, demonstrates what is considered to be a bad loop. The procedure uses two variables to store a starting value (StartVal) and the total number of cells to fill (NumToFill). This loop uses the GoTo statement to control the flow. If the iCount variable, which keeps track of how many cells are filled, is less than the value of NumToFill, the program control loops back to DoAnother.
Sub BadLoop() Dim StartVal As Integer Dim NumToFill As Integer Dim iCount As Integer StartVal = 1 NumToFill = 100 ActiveCell.Value = StartVal iCount = 1 DoAnother: ActiveCell.Offset(iCount, 0).Value = StartVal + iCount iCount = iCount + 1 If iCount < NumToFill Then GoTo DoAnother Else Exit Sub End Sub
This procedure works as intended, so why is it an example of bad looping? Programmers generally frown on using a GoTo statement when not absolutely necessary. Using GoTo statements to loop is contrary to the concept of structured coding. (See the What is structured programming? sidebar.) A GoTo statement makes the code much more difficult to read because representing a loop using line indentations is almost impossible. In addition, this type of unstructured loop makes the procedure more susceptible to error. Furthermore, using lots of labels results in spaghetti codecode that appears to have little or no structure and has a tangled flow.
Because VBA has several structured looping commands, you almost never have to rely on GoTo statements for your decision-making.
For-Next loops
The simplest type of a good loop is a For-Next loop. Its syntax is as follows:
For counter = start To end [Step stepval] [instructions] [Exit For] [instructions] Next [counter]
What is structured programming?
Hang around with programmers, and sooner or later you'll hear the term structured programming. You'll also discover that structured programs are considered superior to unstructured programs.
So, what is structured programming, and can you do it with VBA?
The basic premise of structured programming is that a routine or code segment should have only one entry point and one exit point. In other words, a body of code should be a stand-alone unit, and program control should not jump into or exit from the middle of this unit. As a result, structured programming rules out the GoTo statement. When you write structured code, your program progresses in an orderly manner and is easy to followas opposed to spaghetti code, in which a program jumps around.
A structured program is easier to read and understand than an unstructured one. More important, it's also easier to modify.
VBA is a structured language. It offers standard structured constructs, such as If-Then-Else and Select Case and the For-Next, Do Until, and Do While loops. Furthermore, VBA fully supports modular code construction.
If you're new to programming, form good structured programming habits early.
The following is an example of a For-Next loop that doesn't use the optional Step value or the optional Exit For statement. This routine executes the Sum = Sum + Sqr(Count) statement 100 times and displays the result, that is, the sum of the square roots of the first 100 integers.
Sub SumSquareRoots() Dim Sum As Double Dim Count As Integer Sum = 0 For Count = 1 To 100 Sum = Sum + Sqr(Count) Next Count MsgBox Sum End Sub
In this example, Count (the loop counter variable) starts out as 1 and increases by 1 each time the loop repeats. The Sum variable simply accumulates the square roots of each value of Count.
CAUTION
When you use For-Next loops, it's important to understand that the loop counter is a normal variablenothing special. As a result, it's possible to change the value of the loop counter in the block of code executed between the For and Next statements. Changing the loop counter inside a loop, however, is a bad practice and can cause unpredictable results. You should take precautions to ensure that your code doesn't change the loop counter.
You can also use a Step value to skip some values in the loop. Here's the same procedure rewritten to sum the square roots of the odd numbers between 1 and 100:
Sub SumOddSquareRoots() Dim Sum As Double Dim Count As Integer Sum = 0 For Count = 1 To 100 Step 2 Sum = Sum + Sqr(Count) Next Count MsgBox Sum End Sub
In this procedure, Count starts out as 1 and then takes on values of 3, 5, 7, and so on. The final value of Count used in the loop is 99. When the loop ends, the value of Count is 101.
A Step value in a For-Next loop can also be negative. The procedure that follows deletes rows 2, 4, 6, 8, and 10 of the active worksheet:
Sub DeleteRows() Dim RowNum As Long For RowNum = 10 To 2 Step -2 Rows(RowNum).Delete Next RowNum End Sub
You may wonder why we used a negative Step value in the DeleteRows procedure. If you use a positive Step value, as shown in the following procedure, incorrect rows are deleted. That's because the rows below a deleted row get a new row number. For example, when row 2 is deleted, row 3 becomes the new row 2. Using a negative Step value ensures that the correct rows are deleted.
Sub DeleteRows2() Dim RowNum As Long For RowNum = 2 To 10 Step 2 Rows(RowNum).Delete Next RowNum End Sub
Sub DeleteRows2() Dim RowNum As Long For RowNum = 2 To 10 Step 2 Rows(RowNum).Delete Next RowNum End Sub
The following procedure performs the same task as the BadLoop example at the beginning of the Looping blocks of instructions section. We eliminate the GoTo statement, however, converting a bad loop into a good loop that uses the For-Next structure.
Sub GoodLoop() Dim StartVal As Integer Dim NumToFill As Integer Dim iCount As Integer StartVal = 1 NumToFill = 100 For iCount = 0 To NumToFill - 1 ActiveCell.Offset(iCount, 0).Value = StartVal + iCount Next iCount End Sub
For-Next loops can also include one or more Exit For statements in the loop. When this statement is encountered, the loop terminates immediately, and control passes to the statement following the Next statement of the current For-Next loop. The following example demonstrates the use of the Exit For statement. This procedure determines which cell has the largest value in Column A of the active worksheet:
Sub ExitForDemo() Dim MaxVal As Double Dim Row As Long MaxVal = Application.WorksheetFunction.Max(Range("A:A")) For Row = 1 To 1048576 If Cells(Row, 1).Value = MaxVal Then Exit For End If Next Row MsgBox "Max value is in Row " & Row Cells(Row, 1).Activate End Sub
The maximum value in the column is calculated by using the Excel MAX function, and the value is assigned to the MaxVal variable. The For-Next loop checks each cell in the column. If the cell being checked is equal to MaxVal, the Exit For statement terminates the loop, and the statements following the Next statement are executed. These statements display the row of the maximum value and activate the cell.
NOTE
The ExitForDemo procedure is presented to demonstrate how to exit from a For-Next loop. However, it's not the most efficient way to activate the largest value in a range. In fact, a single statement does the job.
Range("A:A").Find(Application.WorksheetFunction.Max _ (Range("A:A"))).Activate
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. Here's an example that uses nested For-Next loops to initialize a 10 × 10 × 10 array with the value 1. When the procedure is finished, each of the 1,000 elements in MyArray contains 1.
Sub NestedLoops() Dim MyArray(1 to 10, 1 to 10, 1 to 10) Dim i As Integer, j As Integer, k As Integer 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 ' [More code goes here] End Sub
Do While loops
This section describes another type of looping structure available in VBA. Unlike a For-Next loop, a Do While loop executes as long as a specified condition is met.
A Do While loop can have either of two syntaxes. Here's the first:
Do [While condition] [instructions] [Exit Do] [instructions] Loop
Here's the second:
Do [instructions] [Exit Do] [instructions] Loop [While condition]
As you can see, VBA lets you put the While condition at the beginning or the end of the loop. The difference between these two syntaxes involves the point at which the condition is evaluated. In the first syntax, the contents of the loop may never be executed. In the second syntax, the statements inside the loop are always executed at least one time.
The following examples insert a series of dates into the active worksheet. The dates correspond to the days in the current month, and the dates are entered in a column beginning at the active cell.
NOTE
These examples use some VBA date-related functions:
Date returns the current date.
Month returns the month number for a date supplied as its argument.
DateSerial returns a date for the year, month, and day supplied as arguments.
The first example demonstrates a Do While loop that tests the condition at the beginning of the loop: The EnterDates1 procedure writes the dates of the current month to a worksheet column, beginning with the active cell.
Sub EnterDates1() ' Do While, with test at the beginning Dim TheDate As Date TheDate = DateSerial(Year(Date), Month(Date), 1) Do While Month(TheDate) = Month(Date) ActiveCell = TheDate TheDate = TheDate + 1 ActiveCell.Offset(1, 0).Activate Loop End Sub
This procedure uses a variable, TheDate, which contains the dates that are written to the worksheet. This variable is initialized with the first day of the current month. Inside the loop, the value of TheDate is entered into the active cell, TheDate is incremented, and the next cell is activated. The loop continues while the month of TheDate is the same as the month of the current date.
The following procedure has the same result as the EnterDates1 procedure, but it uses the second Do While loop syntax, which checks the condition at the end of the loop.
Sub EnterDates2() ' Do While, with test at the end Dim TheDate As Date TheDate = DateSerial(Year(Date), Month(Date), 1) Do ActiveCell = TheDate TheDate = TheDate + 1 ActiveCell.Offset(1, 0).Activate Loop While Month(TheDate) = Month(Date) End Sub
Do While loops can also contain one or more Exit Do statements. When an Exit Do statement is encountered, the loop ends immediately, and control passes to the statement following the Loop statement.
Do Until loops
The Do Until loop structure is similar to 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. Here's the first way:
Do [Until condition] [instructions] [Exit Do] [instructions] Loop
Here's the second way:
Do [instructions] [Exit Do] [instructions] Loop [Until condition]
The two examples that follow perform the same action as the Do While date entry examples in the previous section. The difference in these two procedures is where the condition is evaluated (at the beginning or the end of the loop). Here is the first example:
Sub EnterDates3() ' Do Until, with test at beginning Dim TheDate As Date TheDate = DateSerial(Year(Date), Month(Date), 1) Do Until Month(TheDate) <> Month(Date) ActiveCell = TheDate TheDate = TheDate + 1 ActiveCell.Offset(1, 0).Activate Loop End Sub
Here is the second example: