Description of Excel for Windows sub-procedures in Visual Basic for Applications (control structures) (843146)

MORE INFORMATION

Conditional Statements

Adding conditional statements and looping statements to your macros will make them more powerful and more flexible. If the logic of these control structures is handled correctly, you can trap things like invalid user input, varying ranges of data, and run-time errors. Additionally, these control structures help make the macro more stable and provide better fault tolerance for user input that is not valid. back to the top

Logical Structures

Logical structures direct the flow of your macro to certain sections of code, depending on existing conditions when your macro encounters the logical structure.

If . Then . Else

You can use the If.Then.Else structure to run a specific statement or a block of statements that is based on the condition that is tested by the If line.

Try this exercise:

Save and close any open workbooks, and then open a new workbook.
Type the following in Sheet1:
A1: 10
Start the Visual Basic Editor, and then insert a new module into your project.

Type the following code in the module.

Sub Logical_If()
If Sheet1.Range("A1").Value = 10 Then MsgBox "True"
End Sub

Run the Logical_If macro. You receive the message True because the value in cell A1 is 10.
Change the value of cell A1 to something other than10, and then run the Logical_If macro. No message box will be displayed.
Change the value of cell A1 in Sheet1 to a value of 10.

Add the following macro to the module that you inserted in step 3.

Sub Logical_If2()
'test the current value of cell A1 in Sheet1
If Sheet1.Range("A1").Value = 5 Then
MsgBox "A1 has a value of 5"
Sheet1.Range("A1").Value = 10   'change A1 to a value of 10
Else
MsgBox "A1 has a value other than 5"
Sheet1.Range("A1").Value = 5   'change A1 to a value of 5
End If
End Sub

Run the Logical_If2 macro. A message box will be displayed with the following message: A1 has a value other than 5
Click OK to dismiss the dialog box.
Run the Logical_If2 macro. A message box will be displayed with the message:

A1 has a value of 5
Click OK to dismiss the dialog box.

The first time that the macro was run, the value in cell A1 was 10; therefore the statements after the Else line were executed. Also, the value in cell A1 was changed to 5. Therefore, the second time that the macro was run, the statements following the Then line were executed.

If . Then . ElseIf

You can also have more than one test in your logical structure. In the If.Then.ElseIf structure, if the first test (on the If line) is not true, the macro will go to the ElseIf line to test the condition on this line.

Try this exercise:

Type the following in Sheet1:
A1: 5

Type the following in the module that contains the Logical_If macros:

Sub Logical_If3()
'first test 
If Sheet1.Range("A1").Value = 0 Then
MsgBox "A1 has a value of 0"
'second test if 
ElseIf Sheet1.Range("A1").Value = 5 Then the 
'first test is false
MsgBox "A1 has a value of 5"
End If
End Sub

Run the Logical_If3 macro. A message box will the display the following message:

A1 has a value of 5
Click OK to close the dialog box.

Select Case

Use the Select Case structure when you have several conditions to test. Typically, if you have more than two or three conditions, you put them in a Select Case structure.

Try this exercise:

Start the Visual Basic Editor, and then insert a new module into your project.

Type the following in the module:

Sub Logical_Select()
 Dim x As Integer
'prompts you to enter a number and then assigns the value 
'entered to the variable x
x = InputBox("Enter any number")  
Select Case x
Case 1
MsgBox "number entered is 1"
Case 2
MsgBox "number entered is 2"
Case 3
MsgBox "number entered is 3"
Case Else
MsgBox "number entered is not 1, 2, or 3"
End Select

Run the Logical_Select macro. You will be prompted to type a number in an input box.
After you type a number, click OK. If the number that you type is 1, 2, or 3, you receive a message that displays the number that you typed. If you type any other numbers, you receive a message that states that the number entered is not 1, 2, or 3.
Click OK to close the message.

back to the top

Looping Structures

With looping structures, you can run a group of macro statements repeatedly. Some loops repeat a set number of times. Other loops have logic built into them so that they stop when a condition is met. There are also loops that cycle through a collection of objects.

For .Next Loop

The For.Next looping structure is the simplest of the loops. This structure will cycle through the same code a specified number of times, and then the macro will go to the next line of code below the For.Next loop. This kind of looping structure is good if you know the exact number of times that you want to have some code repeated. If you have to have your loop somewhat more flexible, consider using one of the other looping structures that is described in this article.

Try this exercise:

Save and close any open workbooks, and then open a new workbook.
Start the Visual Basic Editor, and then insert a module into your project.

Type the following code into the module:

Sub For_Next_Loop()
Dim counter As Integer   'looping variable
For counter = 1 to 10
   'place the current value of counter into the 
   'corresponding cell in column A of Sheet1
   Sheet1.Cells(counter,1).Value = counter
Next counter
End Sub

Run the For_Next_Loop macro.

The macro places the numbers 1 through 10 in the range A1:A10 , in Sheet1.

Nested For...Next loops

You can also have a For.Next loop inside another For.Next loop. The simplest scenario where you might want to do this is when you want to loop through a two-dimensional range or through an array.

Try this exercise:

Type the following code into the same module sheet as the For_Next_Loop macro:

Sub Nested_For_Next()
'declare 2-dimensional array
Dim MyArray(1 To 2, 1 To 3) As Integer
'declare looping variables
Dim i As Integer, j As Integer
For i = 1 To 2
For j = 1 To 3
'populate the array with the product of i and j
MyArray(i, j) = i * j
MsgBox MyArray(i, j)
Next j
Next i
End Sub

Run the Nested_For_Next macro.

Every time that the macro cycles through the i loop, the j loop cycles through its statements. Therefore, the MyArray array is populated in the following order with its corresponding array element value:

Array element value value of i value of j
MyArray(1, 1) 1 1 1
MyArray(1, 2) 2 1 2
MyArray(1, 3) 3 1 3
MyArray(2, 1) 2 2 1
MyArray(2, 2) 4 2 2
MyArray(2, 3) 6 2 3

back to the top

Logical Looping Structures

Logical looping structures have controlling statements to decide when to end the loop. Where you put the controlling statement is critical, because a Do.Until loop will be executed at least one time, and a Do.While loop may not be executed at all, depending on the result of the logical test in the controlling statement.

Do.While Loop

The Do.While Loop has the controlling statement at the top of the loop. Depending on the conditions in your macro when this line is executed, the loop may not be executed at all.

Try this exercise:

Insert a new module into your project.

Type the following code into the module:

Sub Do_While()
Counter=1
Do While Counter <= 5
MsgBox "counter is " & Counter
Counter = Counter + 1
Loop
End Sub

Run the Do_While macro. You receive a message that displays the current value of the variable Counter.
Click OK.

Because the value of Counter is less than 5 when the Do.While loop is encountered, the loop is executed and continues to be executed as long as the value of Counter is less than or equal to 5. As soon as the value of Counter reaches 6, the Do.While loop is exited.

If you change the line

Counter = 1

Counter = 10

and then run the Do_While macro, no message box appears, because the code inside the loop is never executed.

Do.Until Loop

The Do.Until loop is similar to the Do.While loop, except that the logical test to end the Do.Until loop occurs after the loop has been executed at least one time.

Try this exercise:

Type the following code in the same module as the Do_While macro:

Sub Do_Until()
Counter = 1
Do
MsgBox "counter is " & Counter
Counter = Counter + 1
Loop Until Counter > 5
End Sub

Run the Do_Until macro.

Because the value of Counter is less than 5 when the Do.Until loop is encountered, the loop is executed and continues to be executed as long as the value of Counter is less than or equal to 5. As soon as the value of Counter reaches 6, the Do.Until loop is exited.

If you change the line

Counter = 1

Counter = 10

and then run the Do_Until macro, one message box will appear, because the code inside the loop is executed before the logical test.

While.Wend

The While.Wend looping structure is almost the same as the Do.While loop; the loop will continue as long as a condition is true.

Try this exercise:

Type the following code in the same module as the Do_While macro and the Do_Until macro:

Sub While_Wend()
Counter = 1
While Counter <= 5
MsgBox "Counter is " & Counter
Counter = Counter + 1
Wend
End Sub

Run the While_Wend macro.

Because the value of Counter is less than 5 when the While.Wend loop is encountered, the loop is executed and continues to be executed as long as the value of Counter is less than or equal to 5. As soon as the value of Counter reaches 6, the While.Wend loop is exited.

back to the top