Loop variable ends with a value one above expected

Question

In VBA, running in Excel, I am running a basic loop that fills an array with values. The code is as below.

What I find curious, is the value of Counter starts at 0, yet ends at 7, rather than 6. I can note this when I'm looking in the Locals window and running the code step-by-step. It seems the value becomes 7 on its last instance of running 'Next'

Is this normal, or is there something I'm doing wrong?

It doesn't seem to change the outcome here, but if I'm using more complicated code, I want to be sure this is what I should be expecting.

Sub ArrayLoop()

    Dim myArray(6) As String
    Dim Counter As Integer

    For Counter = 0 To 6
        myArray(Counter) = Range("A1").Offset(Counter, 0).Value
    Next

End Sub

0 is 1 and 6 would be 7. 0 is 1 in arrays.

Davesexcel
– Davesexcel

2015-09-20 20:18:59 +00:00
Commented Sep 20, 2015 at 20:18 — Davesexcel
– Davesexcel, Commented Sep 20, 2015 at 20:18

paul bica · Accepted Answer · 2015-09-20 20:26:20Z

1

This is normal: last iteration of the loop increments Counter to 7 and triggers the exit from loop

Counter = 7 ( > 6 )

There are algorithms based on the exit value of the Counter:

Option Explicit

Sub ArrayLoop()

    Dim myArray(6) As String
    Dim Counter As Integer

    For Counter = 0 To 6
        myArray(Counter) = Range("A1").Offset(Counter, 0).Value
    Next

    Do
        Debug.Print Counter     '---> 7, 6, 5, 4, 3, 2, 1
        Counter = Counter - 1
    Loop While Counter > 0

    Debug.Print Counter         '---> 0

End Sub

edited Sep 20, 2015 at 20:26

answered Sep 20, 2015 at 20:20

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3005775 · Accepted Answer · 2015-09-20 21:14:43Z

0

remember that arrays are zero indexed. So technically the 7th time through the loop it should give you some sort of out of bounds error. Although there is technically nothing wrong with the way you are adding your values to the array.

Also remember that counter increments +1 each time after the loop is completed.

if you wanted it to only use 6 values you would have to change to

For Counter = 0 To 5
    myArray(Counter) = Range("A1").Offset(Counter, 0).Value
Next

this would start at 0 the first time through the loop add the value to the array with a 0 index, then increase the index each time +1 and increase the offset by 1 row each time through the loop. And it would only run the loop 6 times.

answered Sep 20, 2015 at 21:14

user3005775

3061 gold badge3 silver badges14 bronze badges

Collectives™ on Stack Overflow

Loop variable ends with a value one above expected

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related