 |
|
|
|
Workbook Download
This is a zipped Excel Workbook to go with this lesson.
My Personal Experience
While Loops are without doubt a very handy feature of VBA they are the most misused piece of code in VBA. In all the above examples the Loop will complete in less than a second. But in most real world situations you will need to Loop a lot lot more than 100 times this may be as high as 1000000 times or more! When you try to Loop any more than 1000 times and are writing to cells or some other operation you will notice a time lag. All too often I see code that Loops through thousands of Cells just to look for ten or more cells that meet the condition they are after. This is either due to ignorance and/or laziness. There are many alternatives to Loops in these cases that will do the same job, but about 100 times (or more!) quicker and more efficiently. But people tend to stick with what they know or the way they have always done things.
Don't Fall Into The Loop Yourself
I often use this expression to try and drive home the point that Loops can (and do!) draw people in. A lot of people, when they first get the hang of Loops, find they can use them for a whole range of solutions that they previously THOUGHT was not possible. This then draws them into the Loop where they themselves become trapped. I too used to do the same, but when I started Looping through thousands of cells just to find the few I wanted and it took anything from 10 secs to more than 5 mins, I thought that this just cannot be right. You see when you use Excels Find function (Edit>Find) it searches through ALL 16777215 cells and finds what you want in the blink of the eye, no matter which cell it is in. You have a list of data entries and you only want to see all the entries that start with the letter "A", you use AutoFilter and again in the blink of an eye you have them. So initially I started to try and fathom out exactly how these built in Function it did it. Then the penny dropped! Instead of trying to mimic the functions like Find, AutoFilter and the Worksheet Functions to name but a few, I decided I would work them into my code and leave the Loops out altogether. At first this this meant I had to 'Step outside the box' as they say and actually look at a problem from a different angle altogether. But now I will go to no ends to try and find an alternative to some Loop code that needs to Loop anymore than 1000 times.
Short Term Pain For Long Term Gain
There is no mistaking that trying to find an alternative to a Loop will mean sitting back and thinking a lot harder. It will also mean trying a lot of alternate methods to a Loop that wont work at all and/or give Run Time Errors etc. But believe me, persistence WILL pay off. Once you have done this quite a few times you will realise that Loops only need play a very small part in your programming. Then, like me, you will no doubt look for any other method except a Loop. To date I have been able to use an alternative to a Loop about 80% of the time. I often run into a problem that requires dealing with thousands of Cells, Rows etc. Years ago I would have used a Loop, but now a Loop is something I only use as a last resort. Don't misunderstand me, if I'm only dealing with hundreds rather than thousands and I cannot think of an alternative, I wont ponder on it for too long before using the Loop. But the heading of this paragraph is very true.
Sometimes You Just Have To Loop
As I have stated above, 80% of the time I can avoid a Loop, but there is that 20% when I have to bite the bullet and use one. If this is the case I will immediately try to think of a way I could narrow down the field to Loop through. Let's say I need to search through a range of 10000 cells (A1:D2050) that contain a mix of numbers, text and formulas and Clear all the cells that contain formulas and have a value of greater than 100. Firstly I'm only interested in Numeric cells and of those I'm only interested in Formula cells. So ideally I would like to narrow the field down to Numbers and Formulas. We can easily do this by setting a Range Object to all cells in the range A1:D2050 that are Formulas and Numeric. We do this by using Excels SpecialCells Method:
Sub LoopThroughNumericFormulas()
Dim rNumFormulas as Range
Set rNumFormulas = _
Range("A1:D2050").SpecialCells(xlCellTypeFormulas, xlNumbers)
End Sub
Now that we have narrowed the field down we can Loop through the cells in our Range Variable "rNumFormulas" and Clear all cells that are greater than 100. We would do this like below:
Sub LoopThroughNumericFormulas()
Dim rNumFormulas As Range
Dim r100OrGreater As Range
Set rNumFormulas = _
Range("A1:D2050").SpecialCells(xlCellTypeFormulas, xlNumbers)
For Each r100OrGreater In rNumFormulas
If r100OrGreater.Value > 100 Then r100OrGreater.Clear
Next r100OrGreater
Set rNumFormulas = Nothing
End Sub
This could potentially make our Loop run in less than 1 second as apposed to 5 to 10 seconds or even longer. There many other methods that I use which are similar to this. Below is a list of Excel built in features that can quite often be used either in place of, or to narrow down a Loop.
AutoFilter
AdvancedFormulas
Find
SpecialCells
Sort
SubTotals
AutoFill
WorksheetFunction
TextToColumns
Group
I have included with this lesson some examples of Loops and how they might be used. I will also include a Loop that CAN be narrowed down and done a far quicker way. Don't worry too much if you cannot achieve it, I would be very surprised if you can! The loop will take a while to run, but I want to show you a comparison.