VBA question: Sheets() vs. Worksheets()?

[Mondrogan]

Are these the same functions?

 

[Don Guillett]

Sheets can include chart sheets. Worksheets no

 

[Mondrogan]

So I should always use Sheets() rather than Worksheets()? Sounds like
Worksheets() functionality is a subset of Sheets().

 

[Don Guillett]

Sub countsheets()
MsgBox Sheets.Count 'counts all
MsgBox Worksheets.Count'omits chart sheets
End Sub

 

[Harlan Grove]

Sheets can include chart sheets. Worksheets no

Sheets also includes XLM macro sheets. Worksheets doesn't. Putting it
another way, worksheets are sheets that contain constants and formulas
an in which XLM functions called directly don't work.

 

[KL]

Hi Harlan,

I guess you meant to say the other way round: Worksheets() includes XLM sheets .

This works for me:

Sub test()
Worksheets.Add , , , xlExcel4MacroSheet
Worksheets.Add , , , xlExcel4IntlMacroSheet
End Sub

Regards,

KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[KL]

HI again,

guess you were right - the following won't show the macrosheets' names:

Sub test2()
Dim ws As Worksheet
For Each ws In Worksheets
MsgBox ws.Name
Next
End Sub

Interestingly, you can still create them using Worksheets collection.

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

Hi Harlan,

I guess you meant to say the other way round: Worksheets() includes XLM sheets .

This works for me:

Sub test()
Worksheets.Add , , , xlExcel4MacroSheet
Worksheets.Add , , , xlExcel4IntlMacroSheet
End Sub

Regards,

KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

Sheets also includes XLM macro sheets. Worksheets doesn't. Putting it
another way, worksheets are sheets that contain constants and formulas
an in which XLM functions called directly don't work.

 

[Harlan Grove]

HI again,

guess you were right -

Do tell?

the following won't show the macrosheets' names:

Sub test2()
Dim ws As Worksheet
For Each ws In Worksheets
MsgBox ws.Name
Next
End Sub

Simpler to test using Worksheets.Count.

Interestingly, you can still create them using Worksheets
collection.

Yet another inconsistency in Excel's object model. No wonder MSFT
provides no thorough Excel documentation - every few sections would
have to admit to such quirks.