VBA Loops

  • Do Loops

    A key-press macro requires the user to press a hot key each time it runs. In many situations, you would like a macro to repeat a sequence of steps until all of the records are processed, whether the records are few or many. This is a perfect place to use a loop. Watch these videos on loops and then skim the reading below to make sure you've learned each concept. In the videos below, we show you two main forms of looping (Do and For), each with a couple variations. Each type of loop is better suited to certain situations, although any type of loop can be used for nearly any type of situation (it just might not be the "best" way to code it). In this class, you are welcome to have a "favorite" loop type and just use that one everywhere.

    The Do Until loop executes until something is true as follows:

    Do Until [Condition = True]
    [Instructions]
    Loop

    The figure below shows a sub routine using the Do Until loop.

    The Supply Chain
  • For Loops

    The For Each loop executes the loop once for each item specified:

    For Each [item]
    [Instructions]
    Next [item]

    For example, if there are four employees, For Each can be set to run for each employee in column B, as shown in the figure below. For Each handles the movement within the range, so that .Select is not needed to move the ActiveCell at the end of the loop. Notice also that we can use the unit ‘employee' to refer to both the current cell and its contents. Lastly, notice that the For Each loop uses "Next" instead of "Loop" to wrap the loop. In this example, Next refers to the next employee. Incidentally, we can use any word in place of "employee" (except keywords reserved by VBA). For example, I could have used "JoJo" or "Boog" or "EmpType" etc.

    The Supply Chain

    Notice that we specified the employee range with the For Each syntax. The following line specified cell B2 as the top of the employee range and finds the end of the range with the phrase Range("B2").End(xlDown)

    For Each employee In Range(Range("B2"), Range("B2").End(xlDown))

    Once the employee range has been defined, the loop will work through each record in the range.

  • Infinite Loops

    A loop can be used to set the macro to run continuously. For example, the code in the figure below instructs VBA to keep running until one equals zero. Because this will never be true, this particular loop will run forever (or at least until Excel crashes) because there is no mechanism to stop it.

    
    Do Until 1 = 0
        [code goes here]
    Loop
    

    This is sometimes referred to as an infinite loop. An infinite loop occurs if Excel cannot recognize the condition that terminates the loop. You need to know how to break out of an infinite loop.

    To break out of a VBA Macro infinite loop in Excel for Windows, press ESC twice (or just hold it down).

    To break out of an infinite loop in Excel 2011 for OS X, press + . (the apple button along with the period button).

    If your infinite loop bogs down your processor to the point that it can't execute other tasks, CTRL + BREAK won't work. This occurs because the keys pressed are simply entered into the processor queue as another task that will be executed after the loop terminates. Since the loop cannot terminate, the keystrokes won't get processed.

    One simple way to ensure that CTRL + BREAK will always work is to include the DoEvents function inside your loop (usually at the end of the loop as shown below). DoEvents tells the processor to process other tasks in its queue before executing the code in the loop again. When DoEvents is included CTRL + BREAK will get processed before the loop repeats. The figure below contains an example of a loop that includes DoEvents.

    CAUTION: DoEvents is helpful while programming and debugging. But you should remove DoEvents after you have the loop working properly because it slows the code down considerably. For example, in one of the author's tests, a program with DoEvents took over 4x longer than the same program after DoEvents was removed.

  • Practice