Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Type mismatch
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Type mismatch
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Type mismatch
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Type mismatch
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Type mismatch
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Type mismatch
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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Type mismatch
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Type mismatch
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Type mismatch
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Type mismatch
On Sun, 10 Apr 2011 23:08:58 -0400, "Rick Rothstein" wrote:
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, 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 =========================== |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Type mismatch
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) |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Type mismatch
On Mon, 11 Apr 2011 13:31:54 -0400, "Rick Rothstein" wrote:
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) 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" :-| |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type mismatch: array or user-defined type expected | Excel Programming | |||
Type Mismatch: array or user defined type expected | Excel Programming | |||
Type mismatch using rnge as Range with Type 8 Input Box | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming |