VBA Loops and Conditions

 

« Back to videos

Additional Information:

The primary reason for using macros is that by using 10 lines of code, you can process 10,000 lines of data. A loop is basically an instruction to repeat the same steps until all the data has been processed. The For/Next loop lets you explicitly state how many times you wish to repeat the steps and so can be considered the most reliable.

If you are unsure how much data needs to be processed, a For/Next loop that says "do this 1000 times" may be inefficient as there may only be 10 rows of data. Alternatively, it may not process all the data, or it may process data that should be left untouched e.g. formulas for totaling data at the bottom of your table. It is worth stressing that you cannot use CTRL+Z to undo the actions of a macro. This is the argument for using the Do While loop featured in the video.

There is a close relative of the Do While loop, which is the Do Until loop. Logically they are interchangeable because you can either say Do While there is data to process, or Do Until there is no more data to process.

The reason we study conditions at the same time as loops is that you may not wish to treat 100 rows in exactly the same way. In the video, negative numbers are changed to zero. This could also be achieved using the formula "=IF(Result<0,0,Result)". Highlighting could be achieved using conditional formatting. The case for using Visual Basic comes if we want to do anything more sophisticated with negative results e.g. delete the rows or move the rows to a different table.

Until you introduce loops and goto statements, it is very easy to follow your code. That is to say that you work through the macro from top to bottom and process each line exactly once. When you can move backwards, it is possible you will get stuck in an infintely repeating sequence. If Excel stops responding, you can always press Escape to debug. Occasionally Excel will crash so please ensure you save your file before running loops.

Tabbing out the various sections of your code, as defined by loops and conditions, can simplify more complex macros. Not only does it make it clear where certain conditions apply, but it also ensures your combination of loops makes logical sense. If an IF statement starts within a certain For/Next loop, it must also end within that loop.

An example would be if a For/Next loop is used to analyze the first 100 rows and the IF statement confirms there is data present, the IF statement compounds on the For/Next loop. That's to say there must be data present and it must fall within the first 100 rows. Therefore, your "End If" statement, has to come before the end of the For/Next loop. Excel will not run a macro until errors of that nature are resolved.