I am using Microsoft Office Excel 2003(11.8328.8329) SP3.
I like to type variables as tightly as possible.

I am building some code which is going to loop through Sheets.
To that end, I start with "For Each S in Sheets
Next S"

I start by declaring S with "Dim S" as I do not know its type.

View/Locals Window shows the type of S as Variant/Object/Sheet1.
When I replace the declaration of S with Dim S as Sheet1,
Foe Each S in Sheets gets a "Run-time error '13':" which is a "Type
Mismatch". The "best" I can do is "Dim S as Object".

When I look at the "For Each...Next Statement" help, I read
"element Required. Variable used to iterate through the elements of the
collection or array. For collections, element can only be a Variant
variable, a generic object variable, or any specific object variable.
...."

What is the definition of "specific object variable" and why does "Dim S
as Sheet1" not produce one of the right type?
I hope someone will quote from as authoritative a URL as possible.
Thanks.
--
Walter Briscoe

On Sun, 10 Apr 2011 08:48:44 +0100, Walter Briscoe wrote:

I am using Microsoft Office Excel 2003(11.8328.8329) SP3.
I like to type variables as tightly as possible.

I am building some code which is going to loop through Sheets.
To that end, I start with "For Each S in Sheets
Next S"

I start by declaring S with "Dim S" as I do not know its type.

View/Locals Window shows the type of S as Variant/Object/Sheet1.
When I replace the declaration of S with Dim S as Sheet1,
Foe Each S in Sheets gets a "Run-time error '13':" which is a "Type
Mismatch". The "best" I can do is "Dim S as Object".

When I look at the "For Each...Next Statement" help, I read
"element Required. Variable used to iterate through the elements of the
collection or array. For collections, element can only be a Variant
variable, a generic object variable, or any specific object variable.
..."

What is the definition of "specific object variable" and why does "Dim S
as Sheet1" not produce one of the right type?
I hope someone will quote from as authoritative a URL as possible.
Thanks.

I don't have a particular citation, but I'd wonder if what you are seeing has to do with the particular type of object that is Sheet1. This is not specifically defined as part of the Sheets collection. And it also seems to me that there can only be a single Sheet1 object in a workbook; so you really couldn't iterate through all of the Sheet1's, since there is only one.

If you want to iterate through all the worksheets in a workbook, you could use Dim S as Worksheet.

If you want to iterate through all of the sheets in a workbook, regardless of the type of sheet, you could do something like:

dim i as long
for i = 1 to Sheets.count
...do something to the sheet...
next i

In message of Sun, 10 Apr
2011 06:29:00 in microsoft.public.excel.programming, Ron Rosenfeld
writes
On Sun, 10 Apr 2011 08:48:44 +0100, Walter Briscoe
wrote:


[snipped why "Dim S as Sheet1: For Each S in Sheets ..." gets 1038]


I don't have a particular citation, but I'd wonder if what you are
seeing has to do with the particular type of object that is Sheet1.
This is not specifically defined as part of the Sheets collection. And
it also seems to me that there can only be a single Sheet1 object in a
workbook; so you really couldn't iterate through all of the Sheet1's,
since there is only one.

Agreed!


If you want to iterate through all the worksheets in a workbook, you
could use Dim S as Worksheet.

I should have seen that. I did not read Sheets Collection Object Help.


If you want to iterate through all of the sheets in a workbook,
regardless of the type of sheet, you could do something like:

dim i as long
for i = 1 to Sheets.count
...do something to the sheet...
next i

I am happy to do Dim S as Worksheet: for Each S in sheets ...
[If sheets contains any charts, I do not understand the data.]

Thanks.
--
Walter Briscoe

On Sun, 10 Apr 2011 14:50:19 +0100, Walter Briscoe wrote:

In message of Sun, 10 Apr
2011 06:29:00 in microsoft.public.excel.programming, Ron Rosenfeld
writes
On Sun, 10 Apr 2011 08:48:44 +0100, Walter Briscoe
wrote:


[snipped why "Dim S as Sheet1: For Each S in Sheets ..." gets 1038]


I don't have a particular citation, but I'd wonder if what you are
seeing has to do with the particular type of object that is Sheet1.
This is not specifically defined as part of the Sheets collection. And
it also seems to me that there can only be a single Sheet1 object in a
workbook; so you really couldn't iterate through all of the Sheet1's,
since there is only one.

Agreed!


If you want to iterate through all the worksheets in a workbook, you
could use Dim S as Worksheet.

I should have seen that. I did not read Sheets Collection Object Help.


If you want to iterate through all of the sheets in a workbook,
regardless of the type of sheet, you could do something like:

dim i as long
for i = 1 to Sheets.count
...do something to the sheet...
next i

I am happy to do Dim S as Worksheet: for Each S in sheets ...
[If sheets contains any charts, I do not understand the data.]

Thanks.


Glad to help. Thanks for the feedback.

I am happy to do Dim S as Worksheet: for Each S in sheets ...

If you are only looking at worksheets, then technically it would be better
to iterate the Worksheets collection rather than Sheets collection (that
way, if a chart sheet was ever added, you code would not choke on it)...

Dim S As Worksheet
For Each S In Worksheets
...etc...

Rick Rothstein (MVP - Excel)

In message of Sun, 10 Apr 2011 12:45:46 in
microsoft.public.excel.programming, Rick Rothstein <rick.newsNO.SPAM@NO.
SPAMverizon.net writes
I am happy to do Dim S as Worksheet: for Each S in sheets ...

If you are only looking at worksheets, then technically it would be
better to iterate the Worksheets collection rather than Sheets
collection (that way, if a chart sheet was ever added, you code would
not choke on it)...

Dim S As Worksheet
For Each S In Worksheets
...etc...

I am now informed about the Worksheets and thank you.
OTOH, my data is not expected to contain charts. If a chart is given to
me, I would be happier with a noisy failure than a quiet "success".
--
Walter Briscoe

On Sun, 10 Apr 2011 22:58:16 +0100, Walter Briscoe wrote:

I am now informed about the Worksheets and thank you.
OTOH, my data is not expected to contain charts. If a chart is given to
me, I would be happier with a noisy failure than a quiet "success".
--
Walter Briscoe

If you want to test for that gracefully, instead of just having things fail, you could try:

============
Option Explicit
Sub foo()
Dim S As Object

For Each S In Sheets
If S.Type < xlWorksheet Then
MsgBox ("You've got a non-worksheet in your workbook")
Else
... your code ...
End If
Next S

End Sub
=======================

This might not work in versions prior to 2007. In addition, the enumeration codes listed seem to be improperly implemented. A Chart type of sheet returns a value of "3" for Type, but that value is supposed to be XlExcel4MacroSheet. xlChart should be -4109

On Apr 10, 2:48*am, Walter Briscoe
wrote:
I am using Microsoft Office Excel 2003(11.8328.8329) SP3.
I like to type variables as tightly as possible.

I am building some code which is going to loop through Sheets.
To that end, I start with "For Each S in Sheets
Next S"

I start by declaring S with "Dim S" as I do not know its type.

View/Locals Window shows the type of S as Variant/Object/Sheet1.
When I replace the declaration of S with Dim S as Sheet1,
Foe Each S in Sheets gets a "Run-time error '13':" which is a "Type
Mismatch". The "best" I can do is "Dim S as Object".

When I look at the "For Each...Next Statement" help, I read
"element Required. Variable used to iterate through the elements of the
collection or array. For collections, element can only be a Variant
variable, a generic object variable, or any specific object variable.
..."

What is the definition of "specific object variable" and why does "Dim S
as Sheet1" not produce one of the right type?
I hope someone will quote from as authoritative a URL as possible.
Thanks.
--
Walter Briscoe

dim sh as worksheet
for each sh in thisworkbook.sheets
ddd
next sh