What's the difference(s) between using Sheets("Sheet1") vs Worksheets("Sheet1") in VBA?

[cmowla]

Hi everyone. At the end of this post, I thought I was doing right by the the original poster by mentioning that he/she can reduce the quantity of code by mentioning:

And by the way, note that:

Worksheets("sheet5")

and

Sheets("sheet5")

are equivalent. (Just to shorten your code from here on out, if you want!)

But then Fluff posted with assurance that:

Also Sheets & Worksheets are not necessarily equivalent.

In response, I mentioned the following, to try to think of why Fluff mentioned "necessarily".

Unless someone is going to use them to do a Worksheet count:

Where:

MsgBox ThisWorkbook.Sheets.Count

counts the number of both the Worksheets and chart sheets, whereas:

MsgBox ThisWorkbook.Worksheets.Count

gives you just the number of the Worksheets,

Worksheets vs Sheets - ExcelBaby

If you are a beginner of Excel VBA, you will be confused with Worksheets and Sheets. What is the difference between Worksheets and Sheets? Can they be

www.excelhowto.com

yes, they are equivalent because you cannot name a chart sheet the same name as a Worksheet without Excel asking you if you want to embed the chart in the sheet that you want to move it to.

So unless I am missing something, for this purpose of passing something into it to get a sheet object, they are equivalent.

If I'm missing something, can you please explain what? (To eliminate confusion, as that seems to be your motivation for posting that response?)

But Fluff basically mentioned that the purpose of his/her post was to tell me that I was wrong but not tell me why, as it was off-topic.

My motivation was to point out the fact that you were making inaccurate & misleading statements.
And I stand by what I said Worksheets & Sheets are NOT necessarily the same.

Now let's wait for the OP to respond, rather than getting sidetracked.

Okay, fair enough. But now since it is on topic, can someone tell me what I'm missing? Clearly my above reasoning implies that if you are going to be looping through all Sheets in a Workbook, that will cause problems if you want to get a range from that Sheet.

MsgBox Sheets("Chart1").Range("A1").Value

So the question is therefore, if you know that the code you write is referring to a Worksheet (and therefore sheetName that is passed is a Worksheet name . . . which fits the context of the "note" that I posted in that thread), what are the difference(s) between using:

Worksheets(sheetName)

and

Sheets(sheetName)

 

[offthelip]

the Worksheets object only includes workhseets, not chart sheets. the Sheets object include worksheets and chart sheets. So often it makes no difference.

 

[My Aswer Is This]

I have been using Excel for a long time. What is a Chart sheet?

 

[Michael M]

@My Aswer Is This
A chart sheet is one which has been created simpy to house a chart from data on another sheet....usually created by pressing F11 onm a standard keyboard after selecting the data required

 

[offthelip]

I create them by moving an existing chart: by right clicking on the selected chart and then selecting move to new sheet

 

[My Aswer Is This]

Learn something every day. I have sheets with charts on them but never made a chart sheet.

 

[Michael M]

We're never too old !!...

 

[cmowla]

the Worksheets object only includes worksheets, not chart sheets. the Sheets object include worksheets and chart sheets. So often it makes no difference.

That's exactly what I gathered also. Thanks for the confirmation!

But anyway,

I of course have come across issues when you loop through all Sheets (instead of Worksheets) in a Workbook. For example, I have used the following sub to determine which Worksheet is the last Worksheet in a Workbook (even if chart sheets are the last sheet(s) in the Workbook), but I would get an error since I looped through .Sheets instead of .Worksheets:

Sub Test__Last_Worksheet_Index_Name()
MsgBox Last_Worksheet_Index_Name
End Sub
Function Last_Worksheet_Index_Name()

Dim tempval As String, i As Integer

Last_Worksheet_Index_Name = ""

With ActiveWorkbook
    i = .Sheets.Count
    For i = .Sheets.Count To 1 Step -1
        On Error GoTo Next_Sheet
        tempval = .Sheets(i).Range("A1").Value
        Last_Worksheet_Index_Name = .Sheets(i).Name
        Exit For
Next_Sheet:
    Next i
End With

End Function

But if one is searching for a Worksheet and uses Worksheets(i) instead, no loop (or error handling) is required to get the same result:

Sub Test__Last_Worksheet_Index_Name2()
MsgBox Last_Worksheet_Index_Name2
End Sub
Function Last_Worksheet_Index_Name2()
Last_Worksheet_Index_Name2 = ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Name
End Function

(If you all are wondering why I used error handling to do the loop, it's because when a chart sheet is created from a table of data in a Worksheet, and that chart is moved into its own sheet, both the chart sheet and the sheet which the data (if updated, also updates the chart) are a sheet type of -4100. Surprisingly, this is not one of the types listed here! XlSheetType enumeration (Excel) VBA should have (in theory) returned a value of -4109 for the chart sheet, but it didn't! But on a chart sheet, if you query the value of a range, it will throw an error (hence the assignment before the On Error Resume Next).

But the point is, this issue has nothing to do with the fact that Sheets(sheetName) = Worksheets(sheetName) when we KNOW that sheetName is a Worksheet. This issue is when looping through all of a Workbook's Worksheets and/or chart sheets.

Learn something every day. I have sheets with charts on them but never made a chart sheet.

I have seen a former client put the chart in its own sheet strictly for ease of printing, I believe. But yeah, until that point, same here.

Also, from Worksheets vs Sheets - Excel How To in the past there were also macro and dialogue sheets too!

 

[Rick Rothstein]

the Worksheets object only includes workhseets, not chart sheets. the Sheets object include worksheets and chart sheets. So often it makes no difference.

While probably no one uses them any more, there is another type of sheet that Sheets includes which Worksheets does not... Macro sheets (real old school type item). You can add one to your worksheet by pressing CTRL+F11 but I'm pretty sure you will not have any idea what to do with it once you do that.

 

[My Aswer Is This]

So since we are asking questions here. My question is why create a Chart Sheet? I have charts on a normal Excel Worksheet and all works well. So why create a Chart sheet? I hardly ever see anyone on the Forum ask about Chart Sheets.