MS VBA Help has an article entitled
Using For Each...Next Statements
Which claims
The following code loops through each element in an array
and sets the value of each to the value of the index variable I.
Dim TestArray(10) As Integer, I As Variant
For Each I In TestArray
TestArray(I) = I
Next I
I can't make this work.
I extended the code to read
Sub MS_For_Each_Element_In_Array_Demo()
Dim TestArray(2) As Integer, I As Integer, Element As Variant
For Each Element In TestArray: Debug.Print Element;: Next Element:
Debug.Print
For Each Element In TestArray
TestArray(Element) = I
Debug.Print TestArray(Element);
I = I + 1
Next Element
Debug.Print
For Each Element In TestArray: Debug.Print Element;: Next Element:
Debug.Print
End Sub
Which delivered this to the immediate window:
0 0 0
0 1 2
2 0 0

Is it actually possible to make the For Each€¦Next construct correctly
populate the elements of an array, and if so, how please?

Hi

Are you sure that you read the article correctly? For..Each would
iterate through the *elements* of the array and not their indices.
Run:

Sub test()
Dim A(2) As Integer, e As Variant
A(0) = 3
A(1) = 5
A(2) = 8
For Each e In A
Debug.Print e
Next
End Sub

And you'll see 3,5,8 printed and not 0,1,2
If you want to set each array element equal to its index, use
For..Next:

For i = LBound(A) to UBound(A)
A(i) = i
Next i

HTH

-John Coleman


Lionel H wrote:

MS VBA Help has an article entitled
Using For Each...Next Statements
Which claims
The following code loops through each element in an array
and sets the value of each to the value of the index variable I.
Dim TestArray(10) As Integer, I As Variant
For Each I In TestArray
TestArray(I) = I
Next I
I can't make this work.
I extended the code to read
Sub MS_For_Each_Element_In_Array_Demo()
Dim TestArray(2) As Integer, I As Integer, Element As Variant
For Each Element In TestArray: Debug.Print Element;: Next Element:
Debug.Print
For Each Element In TestArray
TestArray(Element) = I
Debug.Print TestArray(Element);
I = I + 1
Next Element
Debug.Print
For Each Element In TestArray: Debug.Print Element;: Next Element:
Debug.Print
End Sub
Which delivered this to the immediate window:
0 0 0
0 1 2
2 0 0

Is it actually possible to make the For Each...Next construct correctly
populate the elements of an array, and if so, how please?

Hi

I looked and found that you *did* read the article correctly - but the
article is simply wrong or at least deeply misleading.
The code fragment
Dim TestArray(10) As Integer, I As Variant
For Each I In TestArray
TestArray(I) = I
Next I

Has the effect (here) of assigning 0 to each element of TestArray -
which is sort of silly since they are already initialized to 0
implicitly anyway. More importantly, if TestArray had previously been
given values,and any of those values are outside 0,1,...,10 then the
fragment
For Each I In TestArray
TestArray(I) = I
Next I

would give a subscript out of range error.

Whoever wrote that article must have been seriously short on caffiene
that day or something.

Sorry for suggesting that you misread it.

-John Coleman



John Coleman wrote:
Hi

Are you sure that you read the article correctly? For..Each would
iterate through the *elements* of the array and not their indices.
Run:

Sub test()
Dim A(2) As Integer, e As Variant
A(0) = 3
A(1) = 5
A(2) = 8
For Each e In A
Debug.Print e
Next
End Sub

And you'll see 3,5,8 printed and not 0,1,2
If you want to set each array element equal to its index, use
For..Next:

For i = LBound(A) to UBound(A)
A(i) = i
Next i

HTH

-John Coleman


Lionel H wrote:

MS VBA Help has an article entitled
Using For Each...Next Statements
Which claims
The following code loops through each element in an array
and sets the value of each to the value of the index variable I.
Dim TestArray(10) As Integer, I As Variant
For Each I In TestArray
TestArray(I) = I
Next I
I can't make this work.
I extended the code to read
Sub MS_For_Each_Element_In_Array_Demo()
Dim TestArray(2) As Integer, I As Integer, Element As Variant
For Each Element In TestArray: Debug.Print Element;: Next Element:
Debug.Print
For Each Element In TestArray
TestArray(Element) = I
Debug.Print TestArray(Element);
I = I + 1
Next Element
Debug.Print
For Each Element In TestArray: Debug.Print Element;: Next Element:
Debug.Print
End Sub
Which delivered this to the immediate window:
0 0 0
0 1 2
2 0 0

Is it actually possible to make the For Each...Next construct correctly
populate the elements of an array, and if so, how please?

John,
Thanks for the reply but:
1. The snippet which makes "the claim" in my original question is cut and
pasted from the article. It is not me misremebering what I read.
2. I am aware that for each iterates throught the elements not the indices,
that is why I did what I did in the extended example.
3. I do not want to fill the array with the indices (I can't imagine why
anyone would want to do that). It is a feature of the MS code snippet which I
chose not to alter in my extended example. I constructed an even longer
example for myself which should have transferred the contents of Array1 into
Array2, The results were equally random.

"John Coleman" wrote:

Hi

Are you sure that you read the article correctly? For..Each would
iterate through the *elements* of the array and not their indices.
Run:

Sub test()
Dim A(2) As Integer, e As Variant
A(0) = 3
A(1) = 5
A(2) = 8
For Each e In A
Debug.Print e
Next
End Sub

And you'll see 3,5,8 printed and not 0,1,2
If you want to set each array element equal to its index, use
For..Next:

For i = LBound(A) to UBound(A)
A(i) = i
Next i

HTH

-John Coleman


Lionel H wrote:

MS VBA Help has an article entitled
Using For Each...Next Statements
Which claims
The following code loops through each element in an array
and sets the value of each to the value of the index variable I.
Dim TestArray(10) As Integer, I As Variant
For Each I In TestArray
TestArray(I) = I
Next I
I can't make this work.
I extended the code to read
Sub MS_For_Each_Element_In_Array_Demo()
Dim TestArray(2) As Integer, I As Integer, Element As Variant
For Each Element In TestArray: Debug.Print Element;: Next Element:
Debug.Print
For Each Element In TestArray
TestArray(Element) = I
Debug.Print TestArray(Element);
I = I + 1
Next Element
Debug.Print
For Each Element In TestArray: Debug.Print Element;: Next Element:
Debug.Print
End Sub
Which delivered this to the immediate window:
0 0 0
0 1 2
2 0 0

Is it actually possible to make the For Each...Next construct correctly
populate the elements of an array, and if so, how please?

OK, my turn to apologise.
I'm still trying to use *element* as an index in my extended example so the
results are'nt random at all. I'm just feeding everything into A(0) so that
A(0) contains the last value of I and A(1) and A(2) never get populated. so,
going bacg to the original MS example, corrected thus it works:
Dim TestArray(10) As Variant, e As Variant, I As Integer
For Each e In InTestArray
TestArray(I) = I
I = I + 1
Next e
and I don't see the advantage over
Dim TestArray(10) As Variant, I As Integer
For I = 0 to Ubound(TestArray)
TestArray(I) = I
Next I


"Lionel H" wrote:

John,
Thanks for the reply but:
1. The snippet which makes "the claim" in my original question is cut and
pasted from the article. It is not me misremebering what I read.
2. I am aware that for each iterates throught the elements not the indices,
that is why I did what I did in the extended example.
3. I do not want to fill the array with the indices (I can't imagine why
anyone would want to do that). It is a feature of the MS code snippet which I
chose not to alter in my extended example. I constructed an even longer
example for myself which should have transferred the contents of Array1 into
Array2, The results were equally random.

"John Coleman" wrote:

Hi

Are you sure that you read the article correctly? For..Each would
iterate through the *elements* of the array and not their indices.
Run:

Sub test()
Dim A(2) As Integer, e As Variant
A(0) = 3
A(1) = 5
A(2) = 8
For Each e In A
Debug.Print e
Next
End Sub

And you'll see 3,5,8 printed and not 0,1,2
If you want to set each array element equal to its index, use
For..Next:

For i = LBound(A) to UBound(A)
A(i) = i
Next i

HTH

-John Coleman


Lionel H wrote:

MS VBA Help has an article entitled
Using For Each...Next Statements
Which claims
The following code loops through each element in an array
and sets the value of each to the value of the index variable I.
Dim TestArray(10) As Integer, I As Variant
For Each I In TestArray
TestArray(I) = I
Next I
I can't make this work.
I extended the code to read
Sub MS_For_Each_Element_In_Array_Demo()
Dim TestArray(2) As Integer, I As Integer, Element As Variant
For Each Element In TestArray: Debug.Print Element;: Next Element:
Debug.Print
For Each Element In TestArray
TestArray(Element) = I
Debug.Print TestArray(Element);
I = I + 1
Next Element
Debug.Print
For Each Element In TestArray: Debug.Print Element;: Next Element:
Debug.Print
End Sub
Which delivered this to the immediate window:
0 0 0
0 1 2
2 0 0

Is it actually possible to make the For Each...Next construct correctly
populate the elements of an array, and if so, how please?

Lionel H wrote:
OK, my turn to apologise.
I'm still trying to use *element* as an index in my extended example so the
results are'nt random at all. I'm just feeding everything into A(0) so that
A(0) contains the last value of I and A(1) and A(2) never get populated. so,
going bacg to the original MS example, corrected thus it works:
Dim TestArray(10) As Variant, e As Variant, I As Integer
For Each e In InTestArray
TestArray(I) = I
I = I + 1
Next e
and I don't see the advantage over
Dim TestArray(10) As Variant, I As Integer
For I = 0 to Ubound(TestArray)
TestArray(I) = I
Next I

Not only are there no advantages - there two distict disadvantages:
1) it makes the code less readable
2) e isn't actually used - it just fuctions to make sure that the loop
is iterated the appropriate number of times - which is what the For ..
Next loop is for. As it is, it does 2 assignments per pass through the
loop (not counting the TestArray(I) = I) instead of the 1 which the
For-Next loop does, leading to slower code.

All this talk about array indices reminds me of:
http://xkcd.com/c163.html

Have a good day.

-John Coleman



"Lionel H" wrote:

John,
Thanks for the reply but:
1. The snippet which makes "the claim" in my original question is cut and
pasted from the article. It is not me misremebering what I read.
2. I am aware that for each iterates throught the elements not the indices,
that is why I did what I did in the extended example.
3. I do not want to fill the array with the indices (I can't imagine why
anyone would want to do that). It is a feature of the MS code snippet which I
chose not to alter in my extended example. I constructed an even longer
example for myself which should have transferred the contents of Array1 into
Array2, The results were equally random.

"John Coleman" wrote:

Hi

Are you sure that you read the article correctly? For..Each would
iterate through the *elements* of the array and not their indices.
Run:

Sub test()
Dim A(2) As Integer, e As Variant
A(0) = 3
A(1) = 5
A(2) = 8
For Each e In A
Debug.Print e
Next
End Sub

And you'll see 3,5,8 printed and not 0,1,2
If you want to set each array element equal to its index, use
For..Next:

For i = LBound(A) to UBound(A)
A(i) = i
Next i

HTH

-John Coleman


Lionel H wrote:

MS VBA Help has an article entitled
Using For Each...Next Statements
Which claims
The following code loops through each element in an array
and sets the value of each to the value of the index variable I.
Dim TestArray(10) As Integer, I As Variant
For Each I In TestArray
TestArray(I) = I
Next I
I can't make this work.
I extended the code to read
Sub MS_For_Each_Element_In_Array_Demo()
Dim TestArray(2) As Integer, I As Integer, Element As Variant
For Each Element In TestArray: Debug.Print Element;: Next Element:
Debug.Print
For Each Element In TestArray
TestArray(Element) = I
Debug.Print TestArray(Element);
I = I + 1
Next Element
Debug.Print
For Each Element In TestArray: Debug.Print Element;: Next Element:
Debug.Print
End Sub
Which delivered this to the immediate window:
0 0 0
0 1 2
2 0 0

Is it actually possible to make the For Each...Next construct correctly
populate the elements of an array, and if so, how please?