internal order of sheets, CodeName

[TheAntiGates]

It has been my observation that each of these 3 methods moves in the obviously expected visual sequence, but I'm trying to see it "officially" stated:
1. for each wksht in worksheets
2. while true : doStuff : ActiveSheet.Next.Select : wend
3. For I = ActiveSheet.Index To Worksheets.Count : dostuff : next

Are each guaranteed to follow the order that you "see the tabs below the Excel sheets" ?

[Ken Puls]

Hi Antigates,

Try testing it with results to the immediate window:
[vba]Sub test()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
Debug.Print "Worksheet Name: " & vbTab & wks.Name & vbTab & "Worksheet Codename: " & vbTab & wks.CodeName
Next wks
End Sub[/vba]

I certainly does in this instance, and I believe would be consistent.

[TheAntiGates]

I apologize. Let me rephrase, if it changes anything

It been my observation, after years and years and years, and tens of thousands of spreadsheets, 7.37 billion lines of code, and thoroughly testing all known and alien life forms, using test data sets consisting of real, imaginary, rational and irrational numbers,...

that each of these 3 methods moves in the obviously expected visual sequence, but I'm trying to see it "officially" stated. Is the an "official" statement available?

Maybe there's not Anyway, sorry that I was unclear. I'll make sure to use real lots of bold next time, ROFL...

[Ken Puls]

LOL!

Maybe we'll get Zack to pop in and say so... he's an MVP, so that would make it official, wouldn't it?

[Zack Barresse]

Well.. I'm definitely not an OFFICIAL voice or standpoint. I may be an MVP right now, but I don't really have any official affiliation with Microsoft other than they gave me a piece of paper.

For all intensive purposes, yes. The definitive order is that which is listed in your VBEIDE under your Project hierarchy. This *should* be from left to right as you're looking at your worksheet tabs; remembering of course that if you use the Worksheets objects it will skip chart sheets, etc.

Now if you're looking for a best practices method, that can be a tricky one too. I'm of the opinion that anytime we don't have to use an actual object, our code will usually run faster. But if you are accessing that object, that may not always be the case. I generally run the first example posted unless I have some caddy-wompus incrementaion.

[TheAntiGates]

Actually, the 3rd "seems" strongest - using the working piece Worksheets(i) (which I didn't mention above). It would go Worksheets(1), 2, 3, etc. (actually it would start at the active sheet, as I showed the code loop atop).

I feel that "for each" is preferable, and I'd LIKE to believe that it goes 1,2,3,4 ... and I'd LIKE to believe that .Next moves similarly ... and that the sequence shown in the VBE is identical to that physically shown in the main interface...

[Bob Phillips]

For all intensive purposes, yes. The definitive order is that which is listed in your VBEIDE under your Project hierarchy. This *should* be from left to right as you're looking at your worksheet tabs; remembering of course that if you use the Worksheets objects it will skip chart sheets, etc.

I believe that the phrase is '... for all intents and purposes ...'

But more importantly, I don't think those two statements are the same. The order in Project Explorer is codename alphabetic, the order in the workbook is however you place them.

They are processed in index order, which is always the order in which you see the sheet tabs, moving a sheet changes its index.

[Ken Puls]

... I don't think those two statements are the same.

Agreed. And Bob nailed it. That's what I was trying to display with the code I gave above. If you rename a couple of sheets, then run it, you'll see the order it goes in. (Index order)

[Zack Barresse]

Doh! Sorry, yes, you are all right. Not sure where my head was at, in a bit of a cloud today.

[TheAntiGates]

Actually, Ken, it's not the renaming, but reordering, right? (Move a sheet to see the difference.)

AAR I have a language problem with the original post so how 'bout we just jerk this whole thread, and maybe I can find another way to ask the question.

Thanks for the great ideas, until this post and thread evaporate!

[Ken Puls]

Actually, Ken, it's not the renaming, but reordering, right? (Move a sheet to see the difference.)

Yes, sorry. That's what I meant.