PDA

View Full Version : internal order of sheets, CodeName



TheAntiGates
06-26-2006, 02:59 PM
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
06-26-2006, 03:47 PM
Hi Antigates,

Try testing it with results to the immediate window:
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

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

TheAntiGates
06-26-2006, 04:13 PM
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
06-26-2006, 10:08 PM
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
06-27-2006, 07:09 AM
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
06-27-2006, 07:54 AM
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
06-27-2006, 08:28 AM
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
06-27-2006, 09:49 AM
... 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
06-27-2006, 10:47 AM
Doh! Sorry, yes, you are all right. Not sure where my head was at, in a bit of a cloud today. :eek:

TheAntiGates
06-27-2006, 12:38 PM
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
06-27-2006, 12:39 PM
Actually, Ken, it's not the renaming, but reordering, right? (Move a sheet to see the difference.)

Yes, sorry. That's what I meant. :)