VBA tip – please don’t leave your Next blank

Include the variable

In a For Next loop you don’t have to include the variable in the Next statement. But ….

This means the code below is ok.

Sub ForNextExample1()
 
Dim i
 
    For i = 1 To 12
 
        Selection(1).Offset(0, i) = i
 
    Next
 
End Sub

In a single For Next loop this may not be an issue. If you have multiple nested For Next loops it can make it hard to track which Next is matched to which For. Also if you miss a Next then again tracking is more difficult.

Sub ForNextExample2()
 
Dim i, r, c
 
    For i = 1 To 12
        For r = 1 To 10
            For c = 1 To 5
                Selection(1).Offset(r * i, c * i) = i
            Next
        Next
    Next
 
End Sub

So please include the variable in the Next statement.

It takes next to no time and makes your code more readable and trackable.

Sub ForNextExample3()
 
Dim i, r, c
 
    For i = 1 To 12
        For r = 1 To 10
            For c = 1 To 5
                Selection(1).Offset(r * i, c + i) = i
            Next c
        Next r
    Next i
 
End Sub

Added 17 November 2021

Wow! Rick Rothstein Excel MVP added a comment below that blew my mind!

You can combine multiple Next statements into a single statement.

So our example becomes.

Sub ForNextExample4()
 
Dim i, r, c
 
  For i = 1 To 12
   For r = 1 To 10
     For c = 1 To 5
      Selection(1).Offset(r * i, c + i) = i
  Next c, r, i
 
End Sub

The sequence used is in reverse order – thanks Rick for sharing an amazing technique.

This is not the standard layout, but it is amazing it works.

Added 1 September 2020.

Alfred posted a comment that I thought was worth adding to this post.

“If you ever have to work with someone else’s VBA code that has a great number of blank NEXT lines, consider using NotePad++.
It support a VB language setting which will indent all the FOR/NEXT structures for you.
NotePad++ is freeware and a life saver.
For one of my projects I had to refactor over 1000 links of VBA that contained “naked” NEXT lines.”

Thanks Alfred.

Related Posts

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

8 thoughts on “VBA tip – please don’t leave your Next blank

  1. If you ever have to work with someone elses VBA code that has a great number of blank NEXT lines, consider using NotePad++.
    It support a VB language setting which will indent all the FOR/NEXT sructures for you.
    NotePad++ is freeware and a life saver.
    For one of my projects I had to refactor over 1000 links of VBA that contained “naked” NEXT lines.

  2. The sample code contain a common error.

    Dim i, r, c

    As it stands, these variables are declared as Variant.

    If you really do want to declare them as a Variant, then explicitly state it.

  3. If you try by selecting hide column and sub sequent column, after selecting them and drag to downwards, all hidden columns will appear.

  4. If you try by selecting hide column and sub sequent column, after selecting them and drag to downwards, all hidden columns will appear. This was tried my self and found it is working.

  5. Here is something that may surprise you and your readers… you do NOT need a Next statement for each of your For statements… you can do it with just one single Next statement. Here is a “typical” nested loop structure…

    Sub AllNextKeywords()
    Dim X As Long, Y As Long, Z As Long
    For X = 1 To 2
    For Y = 1 To 2
    For Z = 1 To 2
    Debug.Print X & Y & Z
    Next Z
    Next Y
    Next X
    End Sub

    This can be written using only one Next statement like this…

    Sub OnlyOneNextKeyword()
    Dim X As Long, Y As Long, Z As Long
    For X = 1 To 2
    For Y = 1 To 2
    For Z = 1 To 2
    Debug.Print X & Y & Z
    Next Z, Y, X
    End Sub

    Now your comment processor is going to eliminate all of my leading spaces (for formatting the code) so you will not see how awkward this looks until you put them back in. Note… I am NOT recommending anyone do this, I am just pointing it out for interest-sake that it can be done. Also note that the loop variables in the single Next statement must be listed in the “reverse” order of the loops as they close the loops for innermost to outermost.