Loop Comparison Theory Discussion: For.Nxt vs. For Each

[YellowLabPro]

Good day everyone,
It has been quite a while since I have been on the board, hope everyone is well.

I would like to better understand the difference(s) between the two Loop methods- For Next and For Each.

The only difference I see between the two is the range for which the loop runs in is established differently.

This is a very naive understanding and welcome assistance.

Best,

Doug

[Bob Phillips]

Doug,

For Each is also a For ... Next. I think that what you are referring is a

[vba]

For Each x In y
[/vba]

as against

[vba]

For i = lower To higher
[/vba]

The former can only apply to items in an array or a collection, and the For loop is iterating through each item.

The second is looping through a counted number of items. It can process the items in a collection as well, but you don't automatically get the object within that collection, you need to elicit them separately. The big advantage of course is that you have an index that you can work with to do other things.

[YellowLabPro]

Hello Bob,
Yes, you have restated my question regarding the two styles of loops correctly.
Maybe my limited exposure to loops is partially the reason I don't see much of a difference.

For Each...Next
For Each Cel in Range, Where the range is "A4:A" & LastRow
vs.
For i = Cells(Rows.Count, "A").end(xlup). Row to 4

The two different loops appear to be the same range to perform whatever instructions on are the same. Do I understand this correctly? If so is there a difference between the two in this particular scenario?

[Bob Phillips]

Yes they are intrinsically the same, but there are differences.

In the former you implicitly create a cell object referring to the cell in the range being processed, in the latter you don't.

In the former, you have to extract the index number, in the second you have created it so you have instant access to it.

Also consider these two

[vba]

Sub DougsLoops()
Dim itm, ary
Dim sTemp As String
Dim i As Long, j As Long

ary = [{1,2,3;"a","b","c";99,98,97}]

For Each itm In ary
sTemp = sTemp & vbNewLine & itm
Next itm
MsgBox "For Each loop: " & vbNewLine & sTemp

sTemp = ""
For i = LBound(ary, 1) To UBound(ary, 1)
For j = LBound(ary, 2) To UBound(ary, 2)
sTemp = sTemp & vbNewLine & ary(i, j)
Next j
Next i
MsgBox "For Each loop: " & vbNewLine & sTemp

End Sub
[/vba]

[YellowLabPro]

Ok-
So good, this puts this to rest- that in my example there is no real difference, intrinsically speaking; I have been wondering about this for a while now.

Yes I have some notion that in the former method that it creates an object, and this gets me the properties of said object, a range object, does this buy me anything in this example?

Where would a good example be to use one loop rather than the other?
From my example it does not seem advantageous one way or the other.

In your example I see that first loop plucks out a value from the list depending on its postition in the list, the second reads them sequentially across and then down the range-- Since they are both For Each Loops, I do not see if there is a connection..... between a For Each Loop and a For Next Loop.... Sorry- I'm a bit thick some times :-(

"I am going to get out and get a run in- I will be back in about 1.5 hours. If you don't mind I would like to continue w/ this as time permits for you."

Thanks Bob,

Doug

[Bob Phillips]

Very simplistically speaking, and it has to be so as real-world situations always need an independent design decision, I tend to us For Each when I want to process every item in the set , and where I don't care in which order I process them, or wher I do not know the size of the set and do not need to know the size.

Where I want more control, and/or here is a processing dependency, and/or I care about the size of the data set, I would tend to setup my own loop controls, and use these.

[Bob Phillips]

Weather is rubbish here, so I'm going nowhere until this evening. I will check-out Skype from about 5pm my time.