Solved: Sheets vs Worksheets?

[TheAntiGates]

I have a workbook consisting of a chart sheet (created by F11 key) ("Chart1") and then followed by Sheet1 that has its data.
When on Sheet1, ActiveSheet.Index = 2 and Worksheets.Count = 1
ActiveSheet.Index exceeds Worksheets.Count !

ARRGH! Whose .Count is 2? And/or whose .Index is 1 for Sheet1? Is there a simple guide applicable here for the difference between Sheet and Worksheet, or Sheets and Worksheets?

[CatDaddy]

hidden sheets?

[Aflatoon]

Sheets includes Chart sheets and macro sheets (and any Excel5/95 modules); Worksheets is just worksheets, but the Index property of a worksheet is the index into the Sheets collection, not the Worksheets collection.

[TheAntiGates]

So ActiveSheet.Index goes with Sheets.Count, not Worksheets.Count. Thanks then.

[mikerickson]

Index is a property of a Sheet object. Every worksheet is also a sheet and so inherits those properties. The Index of a sheet is relative to its membership in the Sheets collection.


(BTW, if you think Hendrix could play, you should check out Chet Atkins)

[TheAntiGates]

Sheets includes Chart sheets and macro sheets (and any Excel5/95 modules); Worksheets is just worksheets, but the Index property of a worksheet is the index into the Sheets collection, not the Worksheets collection.

Thank you both for straightening this out. I believe that the resolution is to use Sheets.count, but actually I need to give some thought as to whether I really want to process those "chart sheets" anyway, which sways me to only want to use worksheets anyway. I'm not sure that on non-worksheets I want to go For Each c In Worksheets(i).UsedRange because I don't know if those chart sheets even "have" cells!! (in a traditionally accessible sense)

Activesheet.index was all that I know of. Is there a counterpart for Activesheet's position in WorkSheets()?

And Mike, thank you for your technical contribution. And send my regards to Les, Wes, Chet and Joe Pass. Their skills are beyond debate, but if you instead prefer to duke it out over superlative rock players ( here's one opinion ) go nuts

[TheAntiGates]

By the way, that comment of "inheriting" is very useful for me as I attempt to "get" why a worksheet property would enumerate as though it was a sheet property.

In trying to work out my own issue with knowing which worksheet I'm on, I was desperate enough to grope for activeworksheet.index. Nope, it doesn't exist of course.

If all else fails I might have to roll my own, and check activesheet.name against For Each Worksheet, and return the enumeration that matches. Pee-yeww to that.

[Shred Dude]

To guard aginst a Worksheet's name being changed by a user, I typically query the sheet.codename property instead of Sheet.name. If it's a sheet you're including in a workbook for example, just name it something descriptive in the IDE like "wksMySheet". Then if the user uses Rename to change the name on the Sheet's Tab, your code will still work.

[TheAntiGates]

For lack of anything better I'm settling on this function. If any one has more useful comment to add please do, though I'll now flip the thread marker.

I hadn't realized how much I've carelessly gotten away with sheets and worksheets. When I'm working with all sheets, I need to go
{For Each MyObj In Sheets} instead of {For Each worksheet In Sheets} (MyObj is declared as Object). And I can use
ActiveSheet.Type <> xlWorksheet 'or xlchart
to determine subsequent action.

When I'm working with WORKsheets, instead of activesheet.index, there's this:[vba]'Return ordinal position of the active sheet in the worksheets collection, or 0 if activesheet is not a worksheet.
'This is an "activesheet.index for worksheets, not sheets"
Function ActiveWorksheetIndex() As Integer
Dim ws As Worksheet, i As Long
For Each ws In Worksheets
i = i + 1
'Debug.Print i, ActiveSheet.Name, ws.Name, Worksheets(i).Name
If ActiveSheet.Name = ws.Name Then ActiveWorksheetIndex = i: Exit Function
Next
ActiveWorksheetIndex = 0
End Function[/vba]

[Aflatoon]

As a matter of curiosity, why would you require that function?

[TheAntiGates]

As a matter of curiosity, why would you require that function?

Fair question. For example, walking through a workbook to... find errors; find cells that print as ##### and need widening; find a specific number (or negatives, or numbers outside of some threshhold); finding a string; finding cells of a particular color (namely those I conditionally formatted to red). After years of refinement those macros are incredibly efficient and productive, and I use For Every ws in Worksheets for those, as I do not see usefulness in processing charts in those cases.

I even hunt and destroy dreaded clunky merged cells in that fashion. (I particularly detest their disruption of arrow key navigation )

Knowing the ordinal position is useful for reporting worksheets(i).name, and for processing "forward" with Worksheets(i) handling Worksheets(WorksheetIndex) through Worksheets(Worksheets.count)

[Aflatoon]

I don't really see the benefit over a for each loop personally, but for what you suggest would it not be more useful for your function to take a worksheet argument and return the index of that sheet rather than having to activate a sheet in order to use it?

[mikerickson]

If you already have a worksheet object ws, it can be refered to by
ws.Name instead of having to find its (relative to Workbooks) index and refering to it as Worksheets(i).Name

[TheAntiGates]

[vba]if bStartAtBeginning then
iStart=1
else
iStart=index of current worksheet
end if
for i = iStart to worksheets.count
Worksheets(i).Activate
For Each c In Worksheets(i).UsedRange
etc.
(If c.Font.Strikethrough Then show cell and msgbox as to whether to stop)
(If (c.Interior.ColorIndex = some color then...)
(If (c.Errors(1).Value then)
(If Left(c.Text, 1) = "#" then)[/vba]The tradeoffs of nonessential .activates are of course debatable. Using variable i permits a single loop, rather than a separate one for "for all ws." Point taken - "for all ws" and getting information from ws as needed would work when processing all sheets. But if processing the 14th through 16th only, if would still have to ask "Am I to the 14th yet?" under "for all ws."

Another aspect is that I don't know if "for all" is guaranteed to start at element [1] and proceed to the last one. For these operations, I want to always "move forward."

[rcharters]

This may help explain the different type of sheets. I believe you are looking for the line with variable e.

Create a workbook with multiple worksheet, chart sheets and Excel4 Macro Sheets. Then run this code and check the variables.

Sub SheetTypes()
'Windows.Item(1) is the workbook with focus
ThisWorkbook.Activate
a = Worksheets.Count
b = Charts.Count
c = ActiveWorkbook.Excel4MacroSheets.Count
d = Sheets.Count
e = Windows.Item(1).ActiveSheet.Index
Stop
End Sub

Hope this helps.

[TheAntiGates]

If you already have a worksheet object ws, it can be refered to by
ws.Name instead of having to find its (relative to Workbooks) index and refering to it as Worksheets(i).Name

Completely agreed. I use "i" in order to loop over only a portion of the Worksheet collection. Earlier, for brevity, I said that I was using "for all ws in worksheets" - but actually I'm not in the cases where I use "i." Moreover, before any loop begins, there wouldn't be a "ws" anyway; only ActiveSheet is known.

I appreciate all your useful thoughts on this.