• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What should the starting index of a for loop be: zero-based or one-based?

shahin

shahin

Active Member
Hi there vba giants! I got a question concerning for next loop. When will a loop be zero-based and when the same will be one-based? I'm seriously confused about it. Although my finding is that zero-based loop comes up while playing with array, I'm not sure at all. Any input on this will be highly appreciated. Forgive my ignorance and thanks in advance.
 
Last edited:
Hi, shahin!

Option Base [0,1] define the 1st element of arrays, default to 0, you can set it to 1 when required.
While user defined memory arrays (vectors or one dimension arrays) usually start at 1, collections and objects arrays usually start at zero.
So it's up to you to leave it at its default value (0) or change it to 1 if procedures in a module don't have a (0) -sub 0- element. Or don't do anything and start your loops at 0 or at 1 as I actually do.

Regards!
 
Thanks SirJB7, for your explicit answer. One thing still unclear, though! I guess, "for loop" takes 0 as its starting index when we use it in array not in general instances. Ain't it?
 
"for loop" takes 0 as its starting index
Click to expand...

No. For...Loop uses whatever the starting point you specify, then loops to end point you specify in increment.

Ex: Loop starts from 5 and increment by 1, to 20
Code: 
For i = 5 to 20
    Debug.Print i
Next

Ex2: Loop starts at 0 then increment by 2, up to 20.
Code: 
For i = 0 To 20 Step 2
    Debug.Print i
Next

What you are asking is whether the array element index start from 0 or 1.

This can be declared as SirJB7 indicated using Option Base 0 or 1. Default is 0.

However, there are some instances where variant array will start at 1 instead of 0.

For an example, when variant array is filled directly using Range. It will start at 1 and will be multi-dimensional array.
Code: 
Sub Demo()
Dim VA
VA = Range("A1:A10")
Debug.Print "Array row index start at "; LBound(VA, 1)
Debug.Print "Array row index end at"; UBound(VA, 1)
Debug.Print "Array column index start at "; LBound(VA, 2)
Debug.Print "Array column index end at "; UBound(VA, 2)

For i = LBound(VA, 1) To UBound(VA, 1)
    Debug.Print i & ", "; VA(i, 1)
Next
End Sub
 
Let me let you know why I got confused. There are some random values in column A and I tried to put the same values in column B for the sake of clarity using the below code.

when I tried like this, it works perfectly:
Code: 
Sub looping_clarity()
Dim i As Long, lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lrow
    Cells(i, 2) = Range("A" & i)
Next i
End Sub

But, it breaks showing "Method Range of Object Global failed" when I tried like this:
Code: 
Sub looping_clarity()
Dim i As Long, lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row

For i = 0 To lrow - 1
    Cells(i, 2) = Range("A" & i)
Next i
End Sub
 
Hi, shahin!
If i is 0, the sentence in the loop, Cells(i,2)=Range("A" & i), would try to retrieve the value of cell A0 and store it at cell B0... what confusion might be there?... those are cells/ranges that don't exist.
Regards!
 
Last edited:
This is the very answer I was expecting so long to remove my confusion. Now it all make sense to me. Thanks SirJB7, for making my day. Btw, don't get me wrong, I'm just a starter in vba.
 
Hi, shahin!
A piece of advice.
I always read you asking about how doing not very easy things with VBA like web crawling. In this case the code couldn't be any simpler, so if had stepped it line by line (F8) you would have been able to discover the wrong values of A0 and (0,2)... so the advice: start with things simple and easy, then add a bit of complexity, read more than write, try more than ask, if that doesn't work then you're welcome.
Regards!
 
OMG! What a compliment. You sir just made me blush. I took that as a blessing. I always feel guilty to ask about things I'm not familiar with, yet I do cause what I have learnt so far is by asking and making mistakes without having any background related to IT.
 
You must log in or register to reply here.
Back
Top