Microsoft KB Archive/843146

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

Article ID: 843146

Article Last Modified on 12/7/2006

-

APPLIES TO


 * Microsoft Excel 2002 Standard Edition

-



SUMMARY
''The article contains information about control structures in Microsoft Excel for Windows sub-procedures in Visual Basic for Applications.

There are three types of control structures:''
 * Logical structures direct the flow of your macro to certain sections of the code based on existing conditions when your macro hits the logical structure. There are three logical structures: If … Then … Else , If … Then … ElseIf and Select Case
 * With looping structures, you can run a group of macro statements repeatedly. There are two looping structures: For …Next Loop and Nested For...Next loops
 * Logical looping structures have controlling statements to decide when to end the loop. There are three controlling statements: Do…While Loop, Do…Until Loop and While…Wend

The article uses samples to demonstrate each kind of control structure.



INTRODUCTION
This article describes control structures in Excel for Windows sub-procedures in Visual Basic for Applications. This article describes these three types of control structures:
 * Logical structures
 * Looping structures
 * Logical looping structures

This article also provides some samples of each control structure for practice.



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:

 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(&quot;A1&quot;).Value = 10 Then MsgBox &quot;True&quot; 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.</li>  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(&quot;A1&quot;).Value = 5 Then MsgBox &quot;A1 has a value of 5&quot; Sheet1.Range(&quot;A1&quot;).Value = 10  'change A1 to a value of 10 Else MsgBox &quot;A1 has a value other than 5&quot; Sheet1.Range(&quot;A1&quot;).Value = 5  'change A1 to a value of 5 End If End Sub </li> Run the Logical_If2 macro. A message box will be displayed with the following message:

A1 has a value other than 5

</li> Click OK to dismiss the dialog box.</li> Run the Logical_If2 macro. A message box will be displayed with the message:

A1 has a value of 5

</li> Click OK to dismiss the dialog box.</li></ol>

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: <ol> Type the following in Sheet1:

</li>  Type the following in the module that contains the Logical_If macros:

Sub Logical_If3 'first test If Sheet1.Range(&quot;A1&quot;).Value = 0 Then MsgBox &quot;A1 has a value of 0&quot; 'second test if ElseIf Sheet1.Range(&quot;A1&quot;).Value = 5 Then the 'first test is false MsgBox &quot;A1 has a value of 5&quot; End If End Sub </li> Run the Logical_If3 macro. A message box will the display the following message:

A1 has a value of 5

</li> Click OK to close the dialog box.</li></ol>

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: <ol> Start the Visual Basic Editor, and then insert a new module into your project.</li>  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(&quot;Enter any number&quot;) Select Case x Case 1 MsgBox &quot;number entered is 1&quot; Case 2 MsgBox &quot;number entered is 2&quot; Case 3 MsgBox &quot;number entered is 3&quot; Case Else MsgBox &quot;number entered is not 1, 2, or 3&quot; End Select </li> Run the Logical_Select macro. You will be prompted to type a number in an input box.</li> 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.</li> Click OK to close the message.</li></ol>

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: <ol> Save and close any open workbooks, and then open a new workbook.</li> <li>Start the Visual Basic Editor, and then insert a module into your project.</li> <li> 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 </li> <li>Run the For_Next_Loop macro.</li></ol>

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: <ol> <li> 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 </li> <li>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:

</li></ol>

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: <ol> <li>Insert a new module into your project.</li> <li> Type the following code into the module:

Sub Do_While Counter=1 Do While Counter <= 5 MsgBox &quot;counter is &quot; & Counter Counter = Counter + 1 Loop End Sub </li> <li>Run the Do_While macro. You receive a message that displays the current value of the variable Counter.</li> <li>Click OK.</li></ol>

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

to

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: <ol> <li> Type the following code in the same module as the Do_While macro:

Sub Do_Until Counter = 1 Do MsgBox &quot;counter is &quot; & Counter Counter = Counter + 1 Loop Until Counter > 5 End Sub </li> <li>Run the Do_Until macro.</li></ol>

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

to

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:

<ol> <li> 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 &quot;Counter is &quot; & Counter Counter = Counter + 1 Wend End Sub </li> <li>Run the While_Wend macro.</li></ol>

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

Additional query words: Excel Sub-Procedures VBA Control Structures

Keywords: kbprogramming kbvba kbinfo KB843146

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.