What Is a Looping Macro?

Use VBA loops to execute code in Excel.

It’s a good idea to learn about looping macros if you’re thinking about creating macros for automation in Microsoft Excel. In Excel you can use a macro to automate a simple or complex task, such as renaming many sheets in an Excel workbook. A macro is created by adding a series of command lines and procedures using Visual Basic for Applications (VBA) code. VBA is a programming language available in all Microsoft Office applications and it’s used for automation.

Creating Macros

You can create a macro in a few different ways depending on your needs and experience. In Excel, you can record macros to save information about every step you make in your workbook. After the macro is recorded then you execute the macro to repeat the steps saved. You can also create new macros or make corrections and improvements to the macro by using the Visual Basic Editor.

Loop Types

You may want to execute or play a macro for a number of times to perform a particular task. In this case you will need some type of loop in your VBA code. In VBA, you can use the “While…Wend” statement to execute VBA c ode a number of times. You can also execute a macro using a “For…Next” loop which continuously executes commands for a specified number of times.

Using VBA

There are a few reasons why to consider using VBA programming in your Office applications. One of them is to interact with the user to help them get the results they’re looking for quickly. For example, you can create a user interface (UI) to retrieve information from the user and use in your VBA program. With VBA macros you can also interact with other Office applications such as Word or Access. You may want to create a looping macro to continuously monitor email in Outlook and import it to Excel.

Looping Macro Example

The following is an example of a looping macro that will loop 10 times and display the current date and time using the Immediate Window:

Dim X As Integer
Dim Counter As Integer
For X = 0 To 10
Debug.Print Now
Next X

In Excel you can create a looping macro using a “While” loop to add information to your worksheet. The following is an example of a looping macro that will loop 20 times and add the current date and time to the first 20 rows of the spreadsheet:

Counter = 1
While Counter <= 20
Range("A" & Counter).Select
Range("A" & Counter).Value = Now
Counter = Counter + 1
Wend

References

• MSDN: Getting Started with VBA
• Microsoft Office: About Macros in Excel
• MSDN: While Statement
• MSDN: For…Next Statement