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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mondrogan - 11 Mar 2007 18:04 GMT

Are these the same functions?

Reply to this Message

Don Guillett - 11 Mar 2007 18:09 GMT

Sheets can include chart sheets. Worksheets no

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Are these the same functions?

Reply to this Message

Mondrogan - 11 Mar 2007 18:27 GMT

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

> Sheets can include chart sheets. Worksheets no
>
>> Are these the same functions?

Reply to this Message

Don Guillett - 11 Mar 2007 19:37 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> So I should always use Sheets() rather than Worksheets()? Sounds like
> Worksheets() functionality is a subset of Sheets().
>
>> Sheets can include chart sheets. Worksheets no
>>
>>> Are these the same functions?

Reply to this Message

Harlan Grove - 11 Mar 2007 21:58 GMT

"Don Guillett" <dguille...@austin.rr.com> wrote...

>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.

Reply to this Message

KL - 11 Mar 2007 22:10 GMT

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

> "Don Guillett" <dguille...@austin.rr.com> wrote...
>>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.

Reply to this Message

KL - 11 Mar 2007 22:28 GMT

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.

Signature

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,
>
[quoted text clipped - 22 lines]
>> another way, worksheets are sheets that contain constants and formulas
>> an in which XLM functions called directly don't work.

Reply to this Message

Harlan Grove - 11 Mar 2007 23:40 GMT

"KL" <nospamlapink2...@pleasehotmail.com> wrote...

>HI again,
>
>guess you were right -

Do tell?

>the following won't show the macrosheets' names:
>
[quoted text clipped - 4 lines]
> 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.

Reply to this Message