98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article VBA articles
Stay In The Loop With VBA
Sun 18th October 2009
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.
Author is a freelance copywriter. For more information on access vba excel, please visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-618-stay-in-loop-with-vba.html
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsFresh Egg Digital Marketing
HR Manager Lucy Bunn Emotional Intelligence at Work Really like the way the trainer had evidently researched Fresh Egg and made examples relevant to us. Made us more engagaed and the training seem more relevant because we could see exactly how it fitted. Reynolds Catering Supplies
Engagement Executive Amy Buckley Business Writing Amazing training, Karen is very knowledgable. Doherty IT Solutions Ltd
Technical Director Caleb Mills Intermediate Manager Skills - Part Two Enjoyed Alastair’s approach, very disarming and honest. Really helped people open up and explore their core values and examine reasons for their weaknesses and insecurities. Thank you! |
PUBLICATION GUIDELINES