CODEWITHSUNDEEP
excelvba
Whatever22
Whatever22

Reputation: 46

For Each loop to assign a sequence numbers

I am trying to use a For Each loop to assign a sequence numbers (a) for each element (i) of an array that consists of 10 elements.

Sub test2()
    Debug.Print 1
    Dim TestArray(10) As Integer, i As Variant, a As Integer
    a = 0
    
    For Each i In TestArray
        a = i
        TestArray(i) = a
        Debug.Print "i = " & i & ", a = " & a
    Next i
End Sub

I expect each element of the loop will get the numbers from 0 to 9, but Debug.Print command shows in the Immediate window:

i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0
i = 0, a = 0

Upvotes: 0

Views: 458

Answers (4)

Naresh
Naresh

Reputation: 3034

First As you are trying to create an array of sequence numbers from 0 to 9 (10 numbers), the array should be TestArray(9) (being zero based array of 10 elements)

Second First line in the loop a=i (perhaps you are trying to assign first a value 0 to i) is unnecessary as it will be taken care by next line as explained below.

Third As you are using For each loop, you are interating with the element itself and not with it's index/position and hence the line TestArray(i) = a needs correction as i=a. in the array as well explained by others

Fourth you are missing a step to increment value of a like a = a + 1 which should be last line of the loop.

So, your corrected code will look like.

Sub test2()
    'Debug.Print 1
    Dim TestArray(9) As Integer, i As Variant, a As Integer
    a = 0
    For Each i In TestArray ' ie for each element in the array
        'a = i
        'TestArray(i) = a
        i = a 'assign value of a to i element (and not ith element)
        Debug.Print "i = " & i & ", a = " & a
        a = a + 1 ' New line to increment value of a
    Next i
End Sub

Result in immediate window will be

i = 0, a = 0
i = 1, a = 1
i = 2, a = 2
i = 3, a = 3
i = 4, a = 4
i = 5, a = 5
i = 6, a = 6
i = 7, a = 7
i = 8, a = 8
i = 9, a = 9

However, as suggested by @MathieuGuindon using for each loop will be slow for large array. One dimensional Sequence Array can be created with one line code(without loop TestArray = Evaluate("Transpose(Row(1:10))")) as below.

Sub test()
'Create array of sequence numbers 1 to 10
TestArray = Evaluate("Transpose(Row(1:10))")

'Check the results of the array created above
For i = LBound(TestArray) To UBound(TestArray)
    Debug.Print TestArray(i)
Next i
End Sub

You can also make it more flexible so that user can input size (10 or 100 elements) of the array in a variable using inputbox.

Upvotes: 1

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

The point is that each i is the value of an element of the array. So you do not use i as an index but as an item:

For Each i In TestArray

   a = i  ' a gets the value of the array item i
   i = a  ' i **IS* the value of the array element, not an index
   Debug.Print "i = " & i & ", a = " & a  ' yes, of course they are the same

Next i

More specifically, the loop variable a gets assigned the value of the array element. So assigning to i assigns to the loop variable, not to the array element.

The For Each statement does no specify an order in which the collection or array is processed. As far as the user is concerned, it could be random. For arrays it seems to be a sequential index. For a collection it could be the alphabetical order of some member. If you want to be sure of the order, then don't use For Each.

Assigning to the loop variable of a normal For loop changes the loop variable and so influences the number of iterations of the loop. Assigning to the loop variable of a For Each loop does not change the number of iterations of the loop; it just iterates over all elements.

Upvotes: 3

freeflow
freeflow

Reputation: 4355

Your code is working correctly. The problem is that you did not put any data in your array so every value will be zero. Put a breakpoint at the line a=i then step through your code using F8. You will see that your array has eleven items, all of which are zero.

Upvotes: 2

Gustav
Gustav

Reputation: 55906

An array is not a collection, so run a loop:

Sub test2()

    Dim TestArray(10) As Integer
    Dim i As Integer
    Dim a As Integer

    For i = LBound(TestArray) To UBound(TestArray)
        a = i
        TestArray(i) = a
        Debug.Print "i = " & i & ", a = " & a
    Next

End Sub

Upvotes: 2

Related Questions