For Each Type mismatch

[Walter Briscoe]

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.

 

[Donald Guillett]

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.

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

 

[Rick Rothstein]

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)

 

[Walter Briscoe]

In message <[email protected]> of Sun, 10 Apr 2011 12:45:46 in
microsoft.public.excel.programming, Rick Rothstein <rick.newsNO.SPAM@NO.
SPAMverizon.net> writes

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

 

[Rick Rothstein]

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

Here is another possible way to do that...

Sub foobar()
Dim WS As Worksheet
If Excel4MacroSheets.Count + Charts.Count Then
MsgBox "You've got one or more non-worksheets in your workbook"
End If
For Each WS In Worksheets
'... your code ...
Next
End Sub

Rick Rothstein (MVP - Excel

 

[Rick Rothstein]

Any insight as to why the Enumerations for xlSheetType

don't seem to match what is in Help? In particular, both
the regular macro sheets and chart sheets return a type
of 3? And the xlDialogSheet type doesn't support the
Type property.

I'm not sure what an xlDialogSheet is (apparently if is a left-over from
Excel 5.0 which is before my time with Excel), so I cannot address that one.
Does anyone still use them?

As for your Help file question... let's be nice to Microsoft and simply say
that sometimes they are wrong.<g>

I did notice that the Type for Macro and Chart sheets both evaluate to 3...
that is definitely confusing as they are obviously different things
completely. Sorry, I don't have insight as to why; but at least their
collections are independent and individually enumerable, so that leaves an
option to get around the problem.

Rick Rothstein (MVP - Excel)