Michael Alexander - Excel 2019 Power Programming with VBA

VBA is a structured language. It offers standard structured constructs, such as If- Then- Elseand Select Caseand the For- Next, Do Until, and Do Whileloops. 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- Nextloop that doesn't use the optional Stepvalue or the optional Exit Forstatement. 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 Sumvariable simply accumulates the square roots of each value of Count.

When you use For- Nextloops, it's important to understand that the loop counter is a normal variable—nothing special. As a result, it's possible to change the value of the loop counter in the block of code executed between the Forand Nextstatements. 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 Stepvalue 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, Countstarts out as 1 and then takes on values of 3, 5, 7, and so on. The final value of Countused in the loop is 99. When the loop ends, the value of Countis 101.

A Stepvalue in a For- Nextloop 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 Stepvalue in the DeleteRowsprocedure. If you use a positive Stepvalue, 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 Stepvalue 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

The following procedure performs the same task as the BadLoopexample at the beginning of the “Looping blocks of instructions” section. We eliminate the GoTostatement, however, converting a bad loop into a good loop that uses the For- Nextstructure.

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- Nextloops can also include one or more Exit Forstatements in the loop. When this statement is encountered, the loop terminates immediately, and control passes to the statement following the Nextstatement of the current For- Nextloop. The following example demonstrates the use of the Exit Forstatement. 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 MAXfunction, and the value is assigned to the MaxValvariable. The For- Nextloop checks each cell in the column. If the cell being checked is equal to MaxVal, the Exit Forstatement terminates the loop, and the statements following the Nextstatement are executed. These statements display the row of the maximum value and activate the cell.

The ExitForDemoprocedure is presented to demonstrate how to exit from a For- Nextloop. 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- Nextloops inside other For- Nextloops. Here's an example that uses nested For- Nextloops to initialize a 10 × 10 × 10 array with the value –1. When the procedure is finished, each of the 1,000 elements in MyArraycontains –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

This section describes another type of looping structure available in VBA. Unlike a For- Nextloop, a Do Whileloop executes as long as a specified condition is met.

A Do Whileloop 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 Whilecondition 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.

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 Whileloop that tests the condition at the beginning of the loop: The EnterDates1procedure 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 TheDateis entered into the active cell, TheDateis incremented, and the next cell is activated. The loop continues while the month of TheDateis the same as the month of the current date.