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.

 

[Ron Rosenfeld]

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

 

[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

 

[Walter Briscoe]

In message <[email protected]> of Sun, 10 Apr
2011 06:29:00 in microsoft.public.excel.programming, Ron Rosenfeld

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

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

 

[Ron Rosenfeld]

In message <[email protected]> of Sun, 10 Apr
2011 06:29:00 in microsoft.public.excel.programming, Ron Rosenfeld

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

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

 

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

 

[Ron Rosenfeld]

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

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

 

[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

 

[Ron Rosenfeld]

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,

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.

====================
Option Explicit
Sub foo()
Dim S, S1, S2, S3, S4, S5
Dim i As Long
Application.DisplayAlerts = False
For i = Sheets.Count To 2 Step -1
Sheets(i).Delete
Next i
Application.DisplayAlerts = True

Set S1 = Sheets.Add(Type:=xlChart)
Set S2 = Sheets.Add(Type:=xlDialogSheet)
Set S3 = Sheets.Add(Type:=xlExcel4IntlMacroSheet)
Set S4 = Sheets.Add(Type:=xlExcel4MacroSheet)
Set S5 = Sheets.Add(Type:=xlWorksheet)

On Error GoTo Handler
For i = 1 To Sheets.Count
Debug.Print Sheets(i).Name, Sheets(i).Type
Next i
Exit Sub

Handler: Debug.Print "Error with i = " & i, Err.Number, Err.Description
Resume Next


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

 

[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)

 

[Ron Rosenfeld]

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)

The Dialog sheets were replaced by userforms, but they were useful back in the day. I suppose they are only used now by those who have to maintain "legacy" systems.

Thanks for confirming my finding that the Help documentation is wrong.

For some purposes, one could also use the TypeName function. That function classifies both kinds of Macro sheets as Worksheet; but seems to identify a DialogSheet and a Chart satisfactorily.

Microsoft? Wrong? And here I thought it was "by design" :-|