Excel VBA For...Next

Question

I'm a VBA newcomer. I wonder why does sum1() show 10 while sum2() show 11? Thanks in advance.

Sub sum1()
  Dim x As Integer
  x = 0
  For x = 5 To 5
      x = x + x
      MsgBox x
  Next 
End Sub

Sub sum2()
  Dim x As Integer
  x = 0
  For x = 5 To 5
      x = x + x
  Next
  MsgBox x
End Sub

Answer 1

A vba for-loop increments the index variable at the end of each iteration. That is what the Next keyword does. In sum1() You get:

Sub sum1()
  Dim x As Integer
  x = 0 ' x is 0
  For x = 5 To 5 ' x  set to 5
     x = x + x 'x gets 5+5=10
  Next 'x gets 10+1=11
  MsgBox x 'display x=11
End Sub

but in sum2() you get:

Sub sum2()
  Dim x As Integer
  x = 0 ' x is 0
  For x = 5 To 5 ' x  is 5
     x = x + x 'x gets 5+5=10
     MsgBox x 'display x=10
  Next 'x gets 10+1 and is now 11
End Sub

I agree with @MitchWheat, it usually not good practice to modify your index variable while inside a loop. A better approach would be this:

Sub sum3()
    Dim x as Integer
    Dim i as Integer
    x= 1
    For i = 5 To 5
      x = x + x
    Next
    MsgBox x
End Sub

Answer 2

For loops increment the loop variable at the end of the loop.

In the second code snippet, the for loop increments x from 10 to 11, and then you display it.

Whereas the first code snippets increments x AFTER you display it. This is not something specific to VBA.

To see this, run:

Sub sum1_1()
  Dim x As Integer

  x = 0
  For x = 5 To 5
      x = x + x
      MsgBox x
  Next 

  MsgBox x
End Sub

As an aside: It's not best practice to modify loop counters from inside a loop. It can lead to code that is hard to understand.