Excel VBA – Looping Part 2
In Looping Part 1 ( http://wp.me/p2EAVc-aW ) I showed you how to write LOOPS using DO UNTIL/WHILE…LOOP. They are relatively simple to put together and easy to test and debug when things go wrong.
In this blog I am going to show you the FOR…NEXT loop.
This type of loop lets you control the number of loops more easily, as well as working out by itself when to stop rather than setting an end point in the DO type loops. They tend to be more efficient and therefore quicker. The downside to them for novices is that the cursor does not physically move from one cell to another, or from one sheet to another as all the movement is done in the background. This means you can’t run the DEBUG tool as normal and watch what it does to see where it goes wrong and you have to rely on the IMMEDIATE window to question the VB EDITOR to find out what is going on.
Creating a loop with a set number of loops;
Let’s say I want to generate random numbers for my weekly lottery ticket for which I only need 6 numbers. I need a loop that only goes round 6 times and shows me a number each time.
First we need a suitable variable to count the loops.
Dim x as Byte
Now for the loop;
For x = 1 to 6 ‘(stop after 6 loops)
Next x (automatically adds 1 to the variable at the end of each loop)
In terms of coding this is very simple. All I need to do now is add my code to generate random numbers and display them in a message box;
For x = 1 to 6
MsgBox “Lucky number ” & WorksheetFunction.Randbetween(1,49),vbInformation, “Lottery Numbers”
As numbers are generated randomly we would need to build into the code something to handle duplicate values but to keep it simple here I’m just generating 6 random numbers without any further checks.
Note that normally the message box code would all be on one line or perhaps on a couple of lines with an underscore (line continuation marker) at the end of line 1.
If you are unsure about how message boxes work refer to my blog Excel VBA Message Boxes ( http://wp.me/p2EAVc-3 )for more information.
So that is one simple application of the FOR…NEXT type loop. How about scrolling through a bunch of cells?
Looping through a range of cells;
Let’s say I have some data and I want to apply a bit of formatting. I could create a DO UNTIL type loop moving from row to row and then column to column, but a FOR…NEXT loop will be far more efficient.
I want to highlight all cells with a value under 100 by making the font red. I need code that will select the entire table, and then check each cell to see if each cell is under 100 and change values to a red font.
Dim c as Range ‘create an object variable to represent each cell in the selected range
Range(“A1”).CurrentRegion.Select ‘(selects all contiguous cells around A1)
For Each c in Selection
If c.Value <100 then ‘(tests if the value in each cell is less than 100)
c.Font.Color = vbRed
Next c ‘(loops through all cells in the selected range until it runs out)
So this is much easier than trying to work out when to go back to the top of a column and then move across one and so on and so forth.
Below is a video showing a For Each…Next loop in action showing how to use the IMMEDIATE WINDOW to find out what the code is doing and where.
Looping through multiple sheets;
I can apply the same logic to any number of sheets in a workbook.
Dim Ws as Worksheet
For Each Ws in ActiveWorkbook.WorkSheets
If WorksheetFunction.Counta(Ws.Cells) = 0 Then
‘(test if all cells are blank/empty)
So how does this work?
First we need an object variable (Ws) to represent any worksheet in the workbook. This is needed because we don’t necessarily know how many worksheets are in the current workbook or what they are called, so we need a way of referencing each sheet without being specific (i.e. hard coding names/numbers). And this is one of the issues you will have if anything goes wrong. The worksheet you are in is the active sheet, but you never move away from it. All the movement from sheet to sheet happens in the background, so in STEP INTO DEBUG mode you can’t see which sheet is being tested at any point while the macro is running. You have to rely on the IMMEDIATE WINDOW. To open the IMMEDIATE WINDOW select it from the VIEW menu or press CTRL + G.
The IMMEDIATE WINDOW lets you question the VB EDITOR. The only downside to this is you have to ask the questions in VBA! So, to find out which sheet is currently being checked the code above you would need to ask;
Remember you cannot ask about the ActiveSheet as it is always the same one – whichever sheet you started in.
Looping through multiple workbooks;
Having seen how to loop through multiple worksheets, this logic can be extended to do the same with multiple workbooks.
Dim Ws as Worksheet, Wb as Workbook
For Each Wb in ActiveWorkbooks
For Each Ws in Wb.Worksheets
If WorksheetFunction.Counta(Ws.Cells) = 0 Then
Now we have one object variable for the worksheets and one for the workbooks. The code will look for any open workbooks, and for each one it finds it will scroll through all the worksheets to see if they are blank or not and then move on to the next open workbook until it runs out of workbooks to check. By using object variables there is no need to hard code names etc. relating to the open workbooks or worksheets. Remember, if you need to check which workbook or worksheet is being checked by the code you will need to do this in the IMMEDIATE WINDOW.
One word of warning: if PERSONAL.XLSB is open it will treat it as an open workbook and test its sheets too. Problem is PERSONAL.XLSB only has one sheet and it is blank as this workbook is never used other than to write code to. If the code tries to delete its one and only worksheet it will crash as workbooks cannot exist without any worksheets. To get around this you need an IF statement before starting the loops to test the name of the current workbook.
So there you have the basics of loops in VBA:
- DO UNTIL/WHILE…LOOP
Both have their uses. DO loops tend to be easier to create and test, but FOR…NEXT loops are more efficient but tend to require object variables to get the most out of them and are a little trickier to test using the IMMEDIATE WINDOW.
It’s now down to you to put these into practice and apply them to whatever project you are working on.