Article ID: 141762 - View products that this article applies to.
This article was previously published under Q141762
When you create macros or applications in Microsoft Visual Basic for Applications, it is often necessary to run through a section of code several times. Visual Basic for Applications provides several methods with which to repeat, or "loop" through, a section of code.
This article provides sample code that illustrates the use of the various looping structures and algorithms that Visual Basic for Applications provides.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. When you are deciding which looping structure to use, there are several considerations to be aware of. For example:
Do I know exactly how many times I want to loop through the code?
If so, how many?
If not, is there a specific condition on which I want the macro to exit the loop? If the loop is conditional, do I want to test the condition before or after the code is executed?
Sample Visual Basic ProceduresThe following examples demonstrate the various looping structures available in Visual Basic for Applications. Unless otherwise indicated, the examples assume a cell or range of cells is selected. Some other things to note:
For...Each...NextThis example uses a For...Each...Next statement to loop through all the cells in a selected range (the comments show an alternative method):
For <variable> = <n1> to <n2>
Do...Until with Test at Beginning of LoopThis example uses a Do...Until loop to "walk" down a column of cells until the first empty cell is reached. Because the macro performs the test at the beginning of the loop, if the first cell is empty, the code inside the loop will not be run.
Do...Until with Test at End of LoopThis example also uses a Do loop, but it tests at the bottom of the loop. The first line of the macro tests the first cell. Without the first line of code to test the initial cell, the code would execute at least one time, because the loop tests at the bottom.
CAUTION: Do not branch into the body of a While...Wend loop without executing the While statement. Doing so may cause run-time errors or other problems that are difficult to locate.
The Do...Loop statement provides a more structured and flexible way to perform looping. The While...Wend loop is included in Visual Basic for Applications for backward compatibility.
While...Wend LoopNOTE: The While...Wend loop is included in Visual Basic for Applications for backward compatibility.
This example uses a While...Wend loop. This loop tests at the top of the loop only.
If...Then..GoTo LoopThis example creates a loop by using "If <condition> Then GoTo <line label>" structure. This structure is tested at the bottom of the loop.
For more information about looping structures, in the Visual Basic Editor, click the Office Assistant, type loops, and then click Search to view the topics returned.
For additional information about getting help with Visual Basic forApplications, click the article numbers below to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/305326/EN-US/ )OFFXP: Programming Resources for Visual Basic for Applications
(http://support.microsoft.com/kb/226118/EN-US/ )OFF2000: Programming Resources for Visual Basic for Applications
163435Microsoft Excel Visual Basic User's Guide, version 5.0, pages 143-151
(http://support.microsoft.com/kb/163435/EN-US/ )VBA: Programming Resources for Visual Basic for Applications
Article ID: 141762 - Last Review: January 19, 2007 - Revision: 3.4
Contact us for more help