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