For i = 1 To i

[lezawang]

Hi
I do not understand the for loop below. When I say

for i=1 to i

That is to me, I am assigning I to 1 and there will be no loop. I did step through but still can not see how "i" can be in the loop when it has been assigned to 1 and the max is i (to i). Can the code below be written in simpler coding? Thank you very much.

Sub AddSerialNumbers()


    Dim i As Integer
    
    On Error GoTo Last
    
    i = InputBox("Enter Value", "Enter Serial Numbers")
    
    For i = 1 To i
    
    ActiveCell.Value = i
    
    ActiveCell.Offset(1, 0).Activate
    
    Next i
    
Last:         Exit Sub


End Sub

Source: https://excelchamps.com/blog/useful-macro-codes-for-vba-newcomers/

 

[xenou]

I think the assumption is that you will enter a value larger than 1 in the input box. If you enter a negative number, or zero there is no loop. I think if you enter 1 then there is one iteration for vba - I could be wrong (start at 1 and stop at 1).

The variable I is being "re-purposed" (first to get a value from the user via an input box, then to act as a control variable in a loop).

There probably are better ways, yes. And for such a simple task, I'd really considering doing it in the UI manually in one of several ways pretty fast ways as well, without code However, if you are learning VBA then this illustrates a few concepts so no harm.

Edit: However, it also illustrates one bad practice, so harm done actually. You would never really want to activate cells in a code loop.

 

[Eric W]

That really is an example of bad programming. It works, but as you've seen, it's unclear what it's doing. Notice that in the next example on the link you posted, they changed the control variable to a j.

To understand how it works in this case, you have to know how the For/Next loop works. When the program first hits the For statement, it figures out the upper (and possibly lower) limit. Once it has figured out the upper limit, it never recalculates it again. Consider this code:

    x = 5
    For i = 1 To x
        Debug.Print i
        x = 20
    Next i

This will loop 5 times, not 20. So in your example, if the user enters 5 on the inputbox, the For assigns the upper limit to 5, then reassigns the i to 1. Then when i exceeds 5, the loop ends. But this is confusing, and in some languages it won't work. It's best to use a unique control variable in the loop, if only for clarity.

And to echo Xenou's point, activating cells in a loop is bad practice. Something like

Cells(i, "A") = i

is superior to activating cells. And if you update more than just a few cells, even that is too slow. You'd be better off writing your data to an internal array, then writing the entire array at one time.

Also, in looking down the other examples on that link, most of them can be improved quite a bit. For example, to insert n columns, it's better to do it at once, rather than insert 1 column n times.

 

[Rick Rothstein]

i = InputBox("Enter Value", "Enter Serial Numbers")

For i = 1 To i
[/code]

The limits for a For..Next loop are set before the loop iterates and do not change during the loop. So, if your loop had been this...

X = 10
For i = 1 To X

and you changed the value of X within the loop, that would not change the limits the loop iterates over... its upper limit was set at 10 before the loop began and will remain at 10 throughout the loop. You can see this with this quick demonstration...

Sub Demo()
  Dim i As Long, X As Long
  X = 10
  For i = 1 To X
    X = 1
    Debug.Print i
  Next
End Sub

Here X is set at 10 before the loop runs and then, within the loop, it is continually set to a value of 1... still, the output from the loop to the Immediate Window will be the numbers 1, 2, 3, etc. Now, with that said, your code sets the i variable to some user inputted value... that value is used to set the loops upper bound... then the loop executes with i taking on the values 1, 2, 3, etc. up to the value the user answered to the InputBox. As xenou said, the programmer chose to reuse the variable i instead of declaring a new variable for use in the loop.