Using Loops to Repeat Code fontstuff（使用循环fontstuff重复代码）.pdf

Microsoft Office VBA Fact Sheet: Code Loops Using Loops to Repeat Code Why You Need Loops The macro recording tools in Microsoft Word and Microsoft Excel make easy work of creating simple coding tasks, but running a recorded macro is just that – you do a job once. If you need to do the job again on another document, paragraph or worksheet you have to run the macro again. To do that automatically you need a code loop. A loop is created by a set of code statements that cause a piece of code to be repeated over and over again, as many times as necessary. There are different kinds of loop to suit different situations. The code required is usually quite simple and, once mastered, will help you unleash the true power of VBA. The examples shown here use Microsoft Excel, although the techniques are the same for any program that supports VBA. Looping through a Collection (For Each…Next) A programs object model contains a number of collections of objects. The Excel Application object contains a collection of workbooks. The Workbook object contains a collection of worksheets. The Worksheet object contains collections of cells, columns, rows etc. VBA uses a For Each…Next loop to iterate through each member of a collection. The code starts by declaring a variable which represents the object you want to examine (i.e. the member of the collection). This example (Listing 1) loops through the worksheets in a workbook to determine if one with a particular name already exists. If not, it creates one with that name. Listing 1 Sub AddSheet_1() Dim objSheet As Worksheet For Each objSheet In ActiveWorkbook.Sheets If objSheet.Name = NewSheet Then Exit Sub Next objSheet Worksheets.Add ActiveSheet.Name = NewSheet End Sub The statement For Each instructs the program that you wish the code to repeat as many times as there are objects in the collection (unless