about ARRAY variable

[Marek]

people, maybe my question sounds very stupid, but i try to ask you

i have array variable

for exampl.

dim ddd(2)
ddd(0)="aaa"
ddd(1)="bbb"
ddd(2)="ccc"


for each cc in ddd

msgbox cc.index

PEOPLE !, is there any property on array using which i can ge
current index of array ?????????????????


I dont want to use For statement.....

i simply want to know how to get INDEX of array


next



thank

 

[Bernie Deitrick]

Marek,

You can either keep track of the index:

Sub TryNow2()
Dim ddd(0 To 2) As String
Dim cc As Variant
Dim myCount As Integer

ddd(0) = "aaa"
ddd(1) = "bbb"
ddd(2) = "ccc"

myCount = LBound(ddd) - 1

For Each cc In ddd
myCount = myCount + 1
MsgBox "Element " & myCount & " is " & cc & "."
Next cc

End Sub

OR, simply use the index when stepping through:

Sub TryNow()
Dim i As Integer
Dim ddd(0 To 2)

ddd(0) = "aaa"
ddd(1) = "bbb"
ddd(2) = "ccc"

For i = LBound(ddd) To UBound(ddd)
MsgBox "Element " & i & " is " & ddd(i) & "."
Next i
End Sub

HTH,
Bernie
MS Excel MVP

 

[Myrna Larson]

Hi, Bernie:

If the index is needed, I would certainly use that index as the variable in
the For/Next loop. My problem with 'For Each' is that I'm not completely
confident as to the order in which the members of the collection are
processed. Does it always start at the bottom and move up, or from the top
moving down? I haven't seen any documentation on this point, have you?

Marek,

You can either keep track of the index:

Sub TryNow2()
Dim ddd(0 To 2) As String
Dim cc As Variant
Dim myCount As Integer

ddd(0) = "aaa"
ddd(1) = "bbb"
ddd(2) = "ccc"

myCount = LBound(ddd) - 1

For Each cc In ddd
myCount = myCount + 1
MsgBox "Element " & myCount & " is " & cc & "."
Next cc

End Sub

OR, simply use the index when stepping through:

Sub TryNow()
Dim i As Integer
Dim ddd(0 To 2)

ddd(0) = "aaa"
ddd(1) = "bbb"
ddd(2) = "ccc"

For i = LBound(ddd) To UBound(ddd)
MsgBox "Element " & i & " is " & ddd(i) & "."
Next i
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Myrna,

I have never seen any documentation as to how Excel moves through arrays
when 'For Eaching' them, but in my experience, it always starts at the lower
bound and progresses up - the two subs that I posted return them in the same
order. Seems to work that way with cells, worksheets, workbooks, etc.

Of course, under closer questioning, I would admit "If Myrna doesn't know
for sure, then I certainly don't!" ;-)

Bernie