4 Mistakes You Can Avoid when Programming Excel Macros with VBA

 

2. Breaking Instead of Looping

One of the most common problems newer programmer have when they start writing code is properly dealing with loops. And since so many people who use Excel VBA are very much code newbies, poor looping is epidemic.

Looping is very common in Excel because often you are processing data values down an entire row or a column, so you need to loop to process all of them. New programmers often want to just break out of a loop (either a For loop or a While look) instantly when a certain condition is true.

computer science computer science computer science computer science computer science computer science

excel-vba5

computer science computer science computer science computer science computer science computer science

You can ignore the complexity of the code above, just note that inside the inner IF statement, there’s an option to exit the For loop if the condition is true.  Here’s a simpler example:

For x = 1 To 20
    If x = 6 Then Exit For
    y = x + intRoomTemp
Next i

New programmers take this approach because it’s easy. When a condition occurs that you’re waiting for in order to quit a loop, the temptation to just immediate jump out of it is strong, but don’t do it.

More often than not, the code that comes after that “break” is important to process, even the last time through the loop before exiting. A much cleaner and more professional way to handle conditions where you want to leave a loop halfway through, is just to include that exit condition in something like a While statement.

While (x>=1 AND x<=20 AND x<>6)
    For x = 1 To 20
        y = x + intRoomTemp
    Next i
Wend

This allows for a logical flow of your code, with the last run through when x is 5, and then gracefully exiting once the For loop counts up to 6. No need to include awkward EXIT or BREAK commands mid-loop. computer science computer science computer science computer science computer science computer science

Prev2 of 5Next

Leave a Reply

Your email address will not be published. Required fields are marked *