Stay In The Loop With VBA

What is the difference between a recorded macro in Microsoft Excel and a VBA written procedure? The answer-None, as all Excel is doing when you record a macro is translate each keystroke or mouse click to VBA.

What is the difference between a recorded macro in Microsoft Excel and a VBA written procedure? The answer-None, as all Excel is doing when you record a macro is translate each keystroke or mouse click to VBA. There are a few operations that cannot be recorded in a Macro. The first is that a Macro cannot stop to accept user input, like a dialog box, input box or message box. Secondly, a recorded macro starts executing the VBA code in the procedure line by line until it reaches the end. It cannot perform a loop and re-execute the same code again. It's these loops that we will examine in this article.

Why do we need loops in our procedures? Using loops will make your VBA code more efficient and tidy as well as less code for you to write. For example let's say you needed to write the number 100 in a range of 10 cells. You could write the following code to 10 times.

ActiveCell = 100
ActiveCell.Offset(1, 0).Select

It works but you end up having to write 20 lines of the same code. Using a loop we could reduce the number of lines to 4 as follows:

For MyCount = 1 to 10
ActiveCell = 100
ActiveCell.Offset(1, 0).Select
Next MyCount

In this procedure we are using a For-Next loop which allows us to execute a block of code a specified number of times unconditionally. MyCount is a declared variable which acts as a counter. When Excel reaches the Next MyCount statement it evaluates the value of MyCount. If MyCount has reached 10 then the loop is terminated. If MyCount has not yet reached 10 then MyCount is automatically incremented by 1 and the loop begins again. There maybe occasions where the counter needs to be incremented by more than 1 or the counter should be decremented rather than incremented. This can be achieved by specifying a step level in the For statement eg:

For MyCounter = 1 to 10 step 2

Often it is necessary to perform a loop if a certain condition exists or doesn't exist. This is achieved by using the Do / While loop. For example, the following procedure will find the next empty cell in column A by performing a loop to move the active cell 1 row down WHILE the active cell is not empty:

Range("A1").Select
Do While ActiveCell <> ""
ActiveCell.Offset(1, 0).Select
Loop

Another method is to use the Do/ Until method which performs a loop until a condition is met. If we re-write the above procedure, the loop is performed to move the active cell 1 row down UNTIL the active cell is empty:

Range("A1").Select
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Loop

Another type of loop is called a For Each/ Next loop and is a special loop for looping through a collection. A collection is a group of objects of the same type. For example all the open workbooks form the Workbooks collection and all the worksheets in a given workbook are contained in the workbook's Worksheets collection. This is very useful when you want to perform the same action on all members of a collection eg. all worksheets but you don't know how many sheets there are in the workbook. So by looping through the collection with the For Each /Next loop, Excel controls how many times the loop is performed depending on how many objects in the collection. The following procedure writes today's date in cell A1 on every worksheet in the active workbook:

Dim MySheet As Worksheet
For Each MySheet In Worksheets
MySheet.Range("A1") = Date
Next MySheet

The procedure starts by declaring an Object Variable (MySheet)which will represent a worksheet in the collection. The procedure then starts the For Each loop by assigning the first worksheet in the collection to MySheet and writes the date in cell A1 of the worksheet represented by MySheet. The last statement in the procedure moves on to the next sheet in the collection and performs the loop again until all sheets in the collection have been processed.

Using one of the different types of loops examined in this article will not only make your VBA code run quicker and easier to read but will save you many hours of code writing and debugging.