lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
Hi
I do not understand the for loop below. When I say

Code:
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.

Code:
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/
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Last edited:
Upvote 0
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:

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.
 
Upvote 0
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...
Code:
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...
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top