Why Loops?
The purpose of a loop is to get Excel to repeat a piece of code a certain number of times. How many times the code gets repeated can be specified as a fixed number (e.g. do this 10 times), or as a variable (e.g. do this for as many times as there are rows of data).Loops can be constructed many different ways to suit different circumstances. Often the same result can be obtained in different ways to suit your personal preferences. These exercises demonstrate a selection of different ways to use loops.There is two basic kinds of loops, both of which are demonstrated here:
- Do…Loop and
- For…Next loops.
The code to be repeated is placed between the key words.
Open the workbook Using Loops in VBA in Microsoft Excel.xls and take a look at the four worksheets. Each contains two columns of numbers (columns A and B). The requirement is to calculate an average for the numbers in each row using a VBA macro.
Now open the Visual Basic Editor (Alt+F11) and take a look at the code in Module1. You will see a number of different macros. In the following exercises, first run the macro then come and read the code and figure out how it did what it did.
You can run the macros either from the Visual Basic Editor by placing your cursor in the macro and pressing the F5 key, or from Excel by opening the Macros dialog box (ALT+F8) choosing the macro to run and clicking Run. It is best to run these macros from Visual Basic Editor by using Debug > Step Into (by pressing F8) so you can watch them as they work.
Instruction
If Developer Tab is not in the Ribbon..
- Open Excel.
- Go to VBA Editor (press Alt + F11)
- Go to Immediate Window. ( Ctrl + G)
- Write below Code.
- Application.ShowDevTools = True
How to Insert VBA code in Excel
- Go to Developer Tab > Code Group > Visual Basic
- Click Insert > Module.
- Will open a Blank Module for you.
- Write / Paste provided code in that Module
How to Run VBA code in Excel
- Select anywhere in between the Code, Sub… End Sub
- Click Run & Run Sub or F5
Exercise 1: Do… Loop Until…
The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is Column B is filled or NotEmpty.
Select cell C2 before run the macro as macro is based on ActiveCell.
Here’s the code:
Sub Loop1()
'This loop runs until there is nothing in the Previous column
Do
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub
This macro places a formula into the active cell, and moves into the next cell down. It uses LoopUntil to tell Excel to keep repeating the code until the cell in the adjacent column (column B) is empty. In other words, it will keep on repeating as long as there is something in column B.
=Average(RC[-1],RC[-2]) is same as =AVERAGE(B1,A1) with respect to C1, in R1C1 style, it’s trying to say, that, go for average of One Column left (c-1) and Two Column left (C-2)’ s data.
Delete the data from Column C and ready for the next exercise
Exercise 2: Do While… Loop
The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.
Select cell C2 before run the macro as macro is based on ActiveCell.
Sub Loop2()
'This loop runs as long as there is something in the Previous column
Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
The function IsEmpty = False means “Is Not Empty”
This macro does the same job as the last one using the same parameters but simply expressing them in a different way. In previous code, we are first running the Do… Loop code, then checking if criteria matched or not, where,
In this code, we are first checking the condition, and if matched then we are running the Do… Loop.
It uses Loop Until to tell Excel to if adjacent column (column B) is not empty, then only repeat the code.
Delete the data from Column C and ready for the next exercise
Exercise 3: Do While Not… Loop
The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.
Select cell C2 before run the macro as macro is based on ActiveCell.
Here’s the code:
Sub Loop3()
'This loop runs as long as there is something in the Previous column
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
This macro makes exactly the same decision as the last one but just expresses it in a different way. IsEmpty = False means the same as Not IsEmpty. Sometimes you can’t say what you want to say one way, so VBA often offers an alternative syntax.
IsEmpty(ActiveCell.Offset(0, -1)) = False & Not IsEmpty(ActiveCell.Offset(0, -1)), are same way to say, do the repeated task, until adjacent left cell is filled with something, or not Empty.
Delete the data from Column C and ready for the next exercise
Exercise 4: Including an IF statement
The object of this macro is as before, but without replacing any data that may already be there.
Move to Sheet2, select cell C2 and run the macro Loop4.
Sub Loop4()
' This loop runs as long as there is something in the Previous column
' It does not calculate an average if there is already something in the cell
Do
If IsEmpty(ActiveCell) Then
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub
The previous macros take no account of any possible contents that might already be in the cells into which it is placing the calculations. This macro uses an IF statement that tells Excel to write the calculation only if the cell is empty. This prevents any existing data from being overwritten.
The line telling Excel to move to the next cell is outside the IF statement because it has to do that anyway.
Exercise 5: Avoiding Errors
This macro takes the IF statement a stage further, and doesn’t try to calculate an average of cells that are empty.
First, look at the problem. Move to Sheet3, select cell C2 and run the macro Loop4.
Note that because some of the pairs of cells in columns A and B are empty, the =AVERAGE function throws up a #DIV/0 error (the Average function adds the numbers in the cells then divides by the number of numbers – if there aren’t any numbers it tries to divide by zero and you can’t do that!).
Sub Loop5()
' This loop runs as long as there is something in the NEXT column
' It does not calculate an average if there is already something in the cell
' nor if there is no data to average (to avoid #DIV/0 errors).
Do
If IsEmpty(ActiveCell) Then
If IsEmpty(ActiveCell.Offset(0, -1)) And IsEmpty(ActiveCell.Offset(0, -2)) Then
ActiveCell.Value = “”
Else
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
End If
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub
Note that this time there are no error messages because Excel hasn’t tried to calculate averages of numbers that aren’t there, and criteria checking cell has been changed from adjacent column (B0 to adjacent Column D, as for testing purpose, we have change some of Cell in column B to Empty.
In this macro there is a second IF statement inside the one that tells Excel to do something only if the cell is empty. This second IF statement gives excel a choice. Instead of a simple If there is an If and an Else. Here’s how Excel reads its instructions…
“If the cell has already got something in, go to the next cell. But if the cell is empty, look at the corresponding cells in columns A an B and if they are both empty, write nothing (“”). Otherwise, write the formula in the cell. Then move on to the next cell.”
Exercise 6: For… Next Loop
If you know, or can get VBE to find out, how many times to repeat a block of code you can use aFor… Next loop.
select cell C2 and then run the macro Loop6.
Sub Loop6()
' This loop repeats for a fixed number of times determined by the number of rows
' in the range
Dim i As Long
lastcell = Range("A" & Cells.Rows.Count).End(xlUp).Row
For i = 0 To lastcell
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Next i
End Sub
This macro doesn’t make use of an adjacent column of cells like the previous ones have done to know when to stop looping. Instead it counts the number of rows in Column A and find the last filled cell by using below method.
Range(“A” & Cells.Rows.Count).End(xlUp).Row
That means, it goes to the last cell in A column (In case of Excel 2007 prior, cells # A65536 ( 2^16), and for excel 2007 + Cell Number A10485776 ( 2 ^ 20)) and then in come One Step Up, with pressing Control Key.. End(XlUp) and uses the For… Next method to tell Excel to loop that number of times.
In between any stage, if need to exit the loop, we can use EXIT FOR keyword to exit the FOR LOOP.
Exercise 7: Getting the Reference From Somewhere Else
In the above code we have checked Column A, to set the No Of Repeating Time,
Range(“A” & Cells.Rows.Count).End(xlUp).Row
Instead of “A” we can use any other cell, to set the lastCell. By doing something like..
Range(“G” & Cells.Rows.Count).End(xlUp).Row
If you wanted to construct a loop that always ran a block of code a fixed number of times, you could simply use an expression like:
Instead of For i = 0 To LastCell , we can expression like of For i = 0 To 23. It will loop through Rows, and increase i form 0 to 23 / lastCell
Exercise 8: About Doing Calculations…
All the previous exercises have placed a calculation into a worksheet cell by actually writing a regular Excel function into the cell (and leaving it there) just as if you had typed it yourself. The syntax for this is:
ActiveCell.FormulaR1C1 = “TYPE YOUR FUNCTION HERE”
These macros have been using:
ActiveCell.FormulaR1C1 = “=Average(RC[-1],RC[-2])”
Because this method actuall change, just like regular functions – because they are regular functions. The calculating gets done in Excel because all that the macro did was to write the function.
If you prefer, you can get the macro to do the calculating and just write the result into the cell. VBA has its own set of functions, but unfortunately AVERAGE isn’t one of them. However, VBA does support many of the commoner Excel functions with its WorksheetFunction method.
On Sheet1 select cell C2 and run the macro Loop1.
Take a look at the cells you just filled in. Each one contains a function, written by the macro.
Now delete the contents from the cells C2:C20, select cell C2 and run the macro Loop8.
Here’s the code:
Sub Loop7()
Do
ActiveCell.Value = WorksheetFunction.Average(ActiveCell.Offset(0, -1).Value, _
ActiveCell.Offset(0, -2).Value)
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub
Take a look at the cells you just filled in. This time there’s no function, just the value. All the calculating was done by the macro which then wrote the value into the cell.
Excel is Fun.
