Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlChart returning wrong value
Hi,
I added a chart to my workbook using the Sheets.Add command, with the "Type" parameter set to xlChart. But when I walk through the sheets in my workbook and check the "Type" of each, it tells me that my chart is an xlExcel4MacroSheet instead of an xlChart. According to help, xlChart has a value of -4109 and xlExcel4MacroSheet has a value of 3. When I query the Type property of the new Chart I created, VBA says the value is 3, not -4109. Seems like xlChart is set to the wrong value; should it be 3 rather than -4109? Then again, if I add a new sheet using the value of 3 for the type parameter, I get a new worksheet, not a chart. Anyone know what's going on? We were failing to process a lot of charts because the wrong value was coming back. Following is a code snippet showing my problem: Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" End Sub Sub CheckSheetType() Debug.Print ThisWorkbook.Sheets("NewChart").Type End Sub Immediate Window shows "3" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlChart returning wrong value
I ran this in Excel 2003 and didn't have an issue.
Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type End Sub Both came out Type = 3 When I tried it in Excel 2007 before saving the file, I got the same thing. Also no problem if the workbook was an .xlsm workbook. I tweaked my code a bit to see what I'd get for other things. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" Set x = ThisWorkbook.Sheets.Add(Type:=xlExcel4MacroSheet) x.Name = "Macrosheet" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type Debug.Print "Macrosheet", ThisWorkbook.Sheets("Macrosheet").Type Debug.Print "SHeet1", ThisWorkbook.Sheets("Sheet1").Type Debug.Print "Sheet2", ThisWorkbook.Sheets("Sheet2").Type Debug.Print "Sheet3", ThisWorkbook.Sheets("SHeet3").Type End Sub I only got -4167 for the regular worksheets. -- HTH, Barb Reinhardt "SixSigmaGuy" wrote: Hi, I added a chart to my workbook using the Sheets.Add command, with the "Type" parameter set to xlChart. But when I walk through the sheets in my workbook and check the "Type" of each, it tells me that my chart is an xlExcel4MacroSheet instead of an xlChart. According to help, xlChart has a value of -4109 and xlExcel4MacroSheet has a value of 3. When I query the Type property of the new Chart I created, VBA says the value is 3, not -4109. Seems like xlChart is set to the wrong value; should it be 3 rather than -4109? Then again, if I add a new sheet using the value of 3 for the type parameter, I get a new worksheet, not a chart. Anyone know what's going on? We were failing to process a lot of charts because the wrong value was coming back. Following is a code snippet showing my problem: Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" End Sub Sub CheckSheetType() Debug.Print ThisWorkbook.Sheets("NewChart").Type End Sub Immediate Window shows "3" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlChart returning wrong value
Have you tried using something like this (for separate worksheets in the
workbook Sub TestChart() Dim myChart As Chart For Each myChart In ThisWorkbook.Charts Debug.Print myChart.Name Next myChart End Sub If the Charts are embedded in the worksheets, it's a bit different. Dim myChart as ChartObject Dim WS as worksheet for each WS in thisworkbook.worksheets For each myChart in ws.chartobjects debug.print mychart.name next mychart Next WS -- HTH, Barb Reinhardt "SixSigmaGuy" wrote: Hi, I added a chart to my workbook using the Sheets.Add command, with the "Type" parameter set to xlChart. But when I walk through the sheets in my workbook and check the "Type" of each, it tells me that my chart is an xlExcel4MacroSheet instead of an xlChart. According to help, xlChart has a value of -4109 and xlExcel4MacroSheet has a value of 3. When I query the Type property of the new Chart I created, VBA says the value is 3, not -4109. Seems like xlChart is set to the wrong value; should it be 3 rather than -4109? Then again, if I add a new sheet using the value of 3 for the type parameter, I get a new worksheet, not a chart. Anyone know what's going on? We were failing to process a lot of charts because the wrong value was coming back. Following is a code snippet showing my problem: Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" End Sub Sub CheckSheetType() Debug.Print ThisWorkbook.Sheets("NewChart").Type End Sub Immediate Window shows "3" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlChart returning wrong value
But, according to the documentation in help, 3 is the wrong value. xlChart
is supposed to be equal to -4109, not 3. Three is the value associated with xlExcel4MacroSheet. Following is what help says: XlSheetType Enumeration Specifies the worksheet type. Version Information Version Added: Excel 2007 Name Value Description xlChart -4109 Chart xlDialogSheet -4116 Dialog sheet xlExcel4IntlMacroSheet 4 Excel version 4 international macro sheet xlExcel4MacroSheet 3 Excel version 4 macro sheet xlWorksheet -4167 Worksheet © 2006 Microsoft Corporation. All rights reserved. "Barb Reinhardt" wrote in message ... I ran this in Excel 2003 and didn't have an issue. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type End Sub Both came out Type = 3 When I tried it in Excel 2007 before saving the file, I got the same thing. Also no problem if the workbook was an .xlsm workbook. I tweaked my code a bit to see what I'd get for other things. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" Set x = ThisWorkbook.Sheets.Add(Type:=xlExcel4MacroSheet) x.Name = "Macrosheet" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type Debug.Print "Macrosheet", ThisWorkbook.Sheets("Macrosheet").Type Debug.Print "SHeet1", ThisWorkbook.Sheets("Sheet1").Type Debug.Print "Sheet2", ThisWorkbook.Sheets("Sheet2").Type Debug.Print "Sheet3", ThisWorkbook.Sheets("SHeet3").Type End Sub I only got -4167 for the regular worksheets. -- HTH, Barb Reinhardt "SixSigmaGuy" wrote: Hi, I added a chart to my workbook using the Sheets.Add command, with the "Type" parameter set to xlChart. But when I walk through the sheets in my workbook and check the "Type" of each, it tells me that my chart is an xlExcel4MacroSheet instead of an xlChart. According to help, xlChart has a value of -4109 and xlExcel4MacroSheet has a value of 3. When I query the Type property of the new Chart I created, VBA says the value is 3, not -4109. Seems like xlChart is set to the wrong value; should it be 3 rather than -4109? Then again, if I add a new sheet using the value of 3 for the type parameter, I get a new worksheet, not a chart. Anyone know what's going on? We were failing to process a lot of charts because the wrong value was coming back. Following is a code snippet showing my problem: Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" End Sub Sub CheckSheetType() Debug.Print ThisWorkbook.Sheets("NewChart").Type End Sub Immediate Window shows "3" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlChart returning wrong value
I already solved my problem by re-writing my code; the reason for my post
was that I'm just wondering if I found a bug in VBA that should be reported. Looking to see if others get the same results I did. "Barb Reinhardt" wrote in message ... Have you tried using something like this (for separate worksheets in the workbook Sub TestChart() Dim myChart As Chart For Each myChart In ThisWorkbook.Charts Debug.Print myChart.Name Next myChart End Sub If the Charts are embedded in the worksheets, it's a bit different. Dim myChart as ChartObject Dim WS as worksheet for each WS in thisworkbook.worksheets For each myChart in ws.chartobjects debug.print mychart.name next mychart Next WS -- HTH, Barb Reinhardt "SixSigmaGuy" wrote: Hi, I added a chart to my workbook using the Sheets.Add command, with the "Type" parameter set to xlChart. But when I walk through the sheets in my workbook and check the "Type" of each, it tells me that my chart is an xlExcel4MacroSheet instead of an xlChart. According to help, xlChart has a value of -4109 and xlExcel4MacroSheet has a value of 3. When I query the Type property of the new Chart I created, VBA says the value is 3, not -4109. Seems like xlChart is set to the wrong value; should it be 3 rather than -4109? Then again, if I add a new sheet using the value of 3 for the type parameter, I get a new worksheet, not a chart. Anyone know what's going on? We were failing to process a lot of charts because the wrong value was coming back. Following is a code snippet showing my problem: Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" End Sub Sub CheckSheetType() Debug.Print ThisWorkbook.Sheets("NewChart").Type End Sub Immediate Window shows "3" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlChart returning wrong value
What exactly are you trying to do? Maybe it can be done without using the
type. Barb Reinhardt "SixSigmaGuy" wrote: But, according to the documentation in help, 3 is the wrong value. xlChart is supposed to be equal to -4109, not 3. Three is the value associated with xlExcel4MacroSheet. Following is what help says: XlSheetType Enumeration Specifies the worksheet type. Version Information Version Added: Excel 2007 Name Value Description xlChart -4109 Chart xlDialogSheet -4116 Dialog sheet xlExcel4IntlMacroSheet 4 Excel version 4 international macro sheet xlExcel4MacroSheet 3 Excel version 4 macro sheet xlWorksheet -4167 Worksheet © 2006 Microsoft Corporation. All rights reserved. "Barb Reinhardt" wrote in message ... I ran this in Excel 2003 and didn't have an issue. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type End Sub Both came out Type = 3 When I tried it in Excel 2007 before saving the file, I got the same thing. Also no problem if the workbook was an .xlsm workbook. I tweaked my code a bit to see what I'd get for other things. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" Set x = ThisWorkbook.Sheets.Add(Type:=xlExcel4MacroSheet) x.Name = "Macrosheet" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type Debug.Print "Macrosheet", ThisWorkbook.Sheets("Macrosheet").Type Debug.Print "SHeet1", ThisWorkbook.Sheets("Sheet1").Type Debug.Print "Sheet2", ThisWorkbook.Sheets("Sheet2").Type Debug.Print "Sheet3", ThisWorkbook.Sheets("SHeet3").Type End Sub I only got -4167 for the regular worksheets. -- HTH, Barb Reinhardt "SixSigmaGuy" wrote: Hi, I added a chart to my workbook using the Sheets.Add command, with the "Type" parameter set to xlChart. But when I walk through the sheets in my workbook and check the "Type" of each, it tells me that my chart is an xlExcel4MacroSheet instead of an xlChart. According to help, xlChart has a value of -4109 and xlExcel4MacroSheet has a value of 3. When I query the Type property of the new Chart I created, VBA says the value is 3, not -4109. Seems like xlChart is set to the wrong value; should it be 3 rather than -4109? Then again, if I add a new sheet using the value of 3 for the type parameter, I get a new worksheet, not a chart. Anyone know what's going on? We were failing to process a lot of charts because the wrong value was coming back. Following is a code snippet showing my problem: Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" End Sub Sub CheckSheetType() Debug.Print ThisWorkbook.Sheets("NewChart").Type End Sub Immediate Window shows "3" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlChart returning wrong value
I'm trying to find out if there's a bug in the Excel VBA documentation or in
VBA itself wrt the value for xlChart; nothing more. "Barb Reinhardt" wrote in message ... What exactly are you trying to do? Maybe it can be done without using the type. Barb Reinhardt "SixSigmaGuy" wrote: But, according to the documentation in help, 3 is the wrong value. xlChart is supposed to be equal to -4109, not 3. Three is the value associated with xlExcel4MacroSheet. Following is what help says: XlSheetType Enumeration Specifies the worksheet type. Version Information Version Added: Excel 2007 Name Value Description xlChart -4109 Chart xlDialogSheet -4116 Dialog sheet xlExcel4IntlMacroSheet 4 Excel version 4 international macro sheet xlExcel4MacroSheet 3 Excel version 4 macro sheet xlWorksheet -4167 Worksheet © 2006 Microsoft Corporation. All rights reserved. "Barb Reinhardt" wrote in message ... I ran this in Excel 2003 and didn't have an issue. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type End Sub Both came out Type = 3 When I tried it in Excel 2007 before saving the file, I got the same thing. Also no problem if the workbook was an .xlsm workbook. I tweaked my code a bit to see what I'd get for other things. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" Set x = ThisWorkbook.Sheets.Add(Type:=xlExcel4MacroSheet) x.Name = "Macrosheet" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type Debug.Print "Macrosheet", ThisWorkbook.Sheets("Macrosheet").Type Debug.Print "SHeet1", ThisWorkbook.Sheets("Sheet1").Type Debug.Print "Sheet2", ThisWorkbook.Sheets("Sheet2").Type Debug.Print "Sheet3", ThisWorkbook.Sheets("SHeet3").Type End Sub I only got -4167 for the regular worksheets. -- HTH, Barb Reinhardt "SixSigmaGuy" wrote: Hi, I added a chart to my workbook using the Sheets.Add command, with the "Type" parameter set to xlChart. But when I walk through the sheets in my workbook and check the "Type" of each, it tells me that my chart is an xlExcel4MacroSheet instead of an xlChart. According to help, xlChart has a value of -4109 and xlExcel4MacroSheet has a value of 3. When I query the Type property of the new Chart I created, VBA says the value is 3, not -4109. Seems like xlChart is set to the wrong value; should it be 3 rather than -4109? Then again, if I add a new sheet using the value of 3 for the type parameter, I get a new worksheet, not a chart. Anyone know what's going on? We were failing to process a lot of charts because the wrong value was coming back. Following is a code snippet showing my problem: Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" End Sub Sub CheckSheetType() Debug.Print ThisWorkbook.Sheets("NewChart").Type End Sub Immediate Window shows "3" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlChart returning wrong value
FWIW, I verified the same problem on Excel 2003, I was on Excel 2007
previously. Anyone know what's going on here? If I create a chart with Type = -4109 (xlChart), why does VBA tell me the type is 3 (xlExcel4MacroSheet) after the chart is created? "SixSigmaGuy" wrote in message ... I'm trying to find out if there's a bug in the Excel VBA documentation or in VBA itself wrt the value for xlChart; nothing more. "Barb Reinhardt" wrote in message ... What exactly are you trying to do? Maybe it can be done without using the type. Barb Reinhardt "SixSigmaGuy" wrote: But, according to the documentation in help, 3 is the wrong value. xlChart is supposed to be equal to -4109, not 3. Three is the value associated with xlExcel4MacroSheet. Following is what help says: XlSheetType Enumeration Specifies the worksheet type. Version Information Version Added: Excel 2007 Name Value Description xlChart -4109 Chart xlDialogSheet -4116 Dialog sheet xlExcel4IntlMacroSheet 4 Excel version 4 international macro sheet xlExcel4MacroSheet 3 Excel version 4 macro sheet xlWorksheet -4167 Worksheet © 2006 Microsoft Corporation. All rights reserved. "Barb Reinhardt" wrote in message ... I ran this in Excel 2003 and didn't have an issue. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type End Sub Both came out Type = 3 When I tried it in Excel 2007 before saving the file, I got the same thing. Also no problem if the workbook was an .xlsm workbook. I tweaked my code a bit to see what I'd get for other things. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" Set x = ThisWorkbook.Sheets.Add(Type:=xlExcel4MacroSheet) x.Name = "Macrosheet" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type Debug.Print "Macrosheet", ThisWorkbook.Sheets("Macrosheet").Type Debug.Print "SHeet1", ThisWorkbook.Sheets("Sheet1").Type Debug.Print "Sheet2", ThisWorkbook.Sheets("Sheet2").Type Debug.Print "Sheet3", ThisWorkbook.Sheets("SHeet3").Type End Sub I only got -4167 for the regular worksheets. -- HTH, Barb Reinhardt "SixSigmaGuy" wrote: Hi, I added a chart to my workbook using the Sheets.Add command, with the "Type" parameter set to xlChart. But when I walk through the sheets in my workbook and check the "Type" of each, it tells me that my chart is an xlExcel4MacroSheet instead of an xlChart. According to help, xlChart has a value of -4109 and xlExcel4MacroSheet has a value of 3. When I query the Type property of the new Chart I created, VBA says the value is 3, not -4109. Seems like xlChart is set to the wrong value; should it be 3 rather than -4109? Then again, if I add a new sheet using the value of 3 for the type parameter, I get a new worksheet, not a chart. Anyone know what's going on? We were failing to process a lot of charts because the wrong value was coming back. Following is a code snippet showing my problem: Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" End Sub Sub CheckSheetType() Debug.Print ThisWorkbook.Sheets("NewChart").Type End Sub Immediate Window shows "3" |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlChart returning wrong value
It is confusing I know. Some objects can belong to different "Types" and it
seems that a chart sheet is one of them. When its type is returned it is not looked up in the tlb's Enum xlSheetType but is returned simply as a Long (unlike for all other sheet types). FWIW you can do this Dim xlShtType As XlSheetType type xlShtType = and look at the intellisense For sure you cannot rely on 3 to correctly distinguish your sheet type. For your purpose, to ensure you are working with a chart sheet either of the following should be reliable Dim cht As Chart On Error Resume Next Set cht = Nothing Set cht = ActiveSheet On Error GoTo 0 If Not cht Is Nothing Then MsgBox cht.Name End If If TypeName(ActiveSheet) = "Chart" Then MsgBox cht.Name End If Regards, Peter T "SixSigmaGuy" wrote in message ... FWIW, I verified the same problem on Excel 2003, I was on Excel 2007 previously. Anyone know what's going on here? If I create a chart with Type = -4109 (xlChart), why does VBA tell me the type is 3 (xlExcel4MacroSheet) after the chart is created? "SixSigmaGuy" wrote in message ... I'm trying to find out if there's a bug in the Excel VBA documentation or in VBA itself wrt the value for xlChart; nothing more. "Barb Reinhardt" wrote in message ... What exactly are you trying to do? Maybe it can be done without using the type. Barb Reinhardt "SixSigmaGuy" wrote: But, according to the documentation in help, 3 is the wrong value. xlChart is supposed to be equal to -4109, not 3. Three is the value associated with xlExcel4MacroSheet. Following is what help says: XlSheetType Enumeration Specifies the worksheet type. Version Information Version Added: Excel 2007 Name Value Description xlChart -4109 Chart xlDialogSheet -4116 Dialog sheet xlExcel4IntlMacroSheet 4 Excel version 4 international macro sheet xlExcel4MacroSheet 3 Excel version 4 macro sheet xlWorksheet -4167 Worksheet © 2006 Microsoft Corporation. All rights reserved. "Barb Reinhardt" wrote in message ... I ran this in Excel 2003 and didn't have an issue. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type End Sub Both came out Type = 3 When I tried it in Excel 2007 before saving the file, I got the same thing. Also no problem if the workbook was an .xlsm workbook. I tweaked my code a bit to see what I'd get for other things. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" Set x = ThisWorkbook.Sheets.Add(Type:=xlExcel4MacroSheet) x.Name = "Macrosheet" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type Debug.Print "Macrosheet", ThisWorkbook.Sheets("Macrosheet").Type Debug.Print "SHeet1", ThisWorkbook.Sheets("Sheet1").Type Debug.Print "Sheet2", ThisWorkbook.Sheets("Sheet2").Type Debug.Print "Sheet3", ThisWorkbook.Sheets("SHeet3").Type End Sub I only got -4167 for the regular worksheets. -- HTH, Barb Reinhardt "SixSigmaGuy" wrote: Hi, I added a chart to my workbook using the Sheets.Add command, with the "Type" parameter set to xlChart. But when I walk through the sheets in my workbook and check the "Type" of each, it tells me that my chart is an xlExcel4MacroSheet instead of an xlChart. According to help, xlChart has a value of -4109 and xlExcel4MacroSheet has a value of 3. When I query the Type property of the new Chart I created, VBA says the value is 3, not -4109. Seems like xlChart is set to the wrong value; should it be 3 rather than -4109? Then again, if I add a new sheet using the value of 3 for the type parameter, I get a new worksheet, not a chart. Anyone know what's going on? We were failing to process a lot of charts because the wrong value was coming back. Following is a code snippet showing my problem: Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" End Sub Sub CheckSheetType() Debug.Print ThisWorkbook.Sheets("NewChart").Type End Sub Immediate Window shows "3" |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlChart returning wrong value
Thanks Peter,
So, is this a bug in Excel? Since the documentation says one thing, but the code does another? BTW, using TypeName was the way we got around the problem. "Peter T" <peter-t@discussions wrote in message ... It is confusing I know. Some objects can belong to different "Types" and it seems that a chart sheet is one of them. When its type is returned it is not looked up in the tlb's Enum xlSheetType but is returned simply as a Long (unlike for all other sheet types). FWIW you can do this Dim xlShtType As XlSheetType type xlShtType = and look at the intellisense For sure you cannot rely on 3 to correctly distinguish your sheet type. For your purpose, to ensure you are working with a chart sheet either of the following should be reliable Dim cht As Chart On Error Resume Next Set cht = Nothing Set cht = ActiveSheet On Error GoTo 0 If Not cht Is Nothing Then MsgBox cht.Name End If If TypeName(ActiveSheet) = "Chart" Then MsgBox cht.Name End If Regards, Peter T "SixSigmaGuy" wrote in message ... FWIW, I verified the same problem on Excel 2003, I was on Excel 2007 previously. Anyone know what's going on here? If I create a chart with Type = -4109 (xlChart), why does VBA tell me the type is 3 (xlExcel4MacroSheet) after the chart is created? "SixSigmaGuy" wrote in message ... I'm trying to find out if there's a bug in the Excel VBA documentation or in VBA itself wrt the value for xlChart; nothing more. "Barb Reinhardt" wrote in message ... What exactly are you trying to do? Maybe it can be done without using the type. Barb Reinhardt "SixSigmaGuy" wrote: But, according to the documentation in help, 3 is the wrong value. xlChart is supposed to be equal to -4109, not 3. Three is the value associated with xlExcel4MacroSheet. Following is what help says: XlSheetType Enumeration Specifies the worksheet type. Version Information Version Added: Excel 2007 Name Value Description xlChart -4109 Chart xlDialogSheet -4116 Dialog sheet xlExcel4IntlMacroSheet 4 Excel version 4 international macro sheet xlExcel4MacroSheet 3 Excel version 4 macro sheet xlWorksheet -4167 Worksheet © 2006 Microsoft Corporation. All rights reserved. "Barb Reinhardt" wrote in message ... I ran this in Excel 2003 and didn't have an issue. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type End Sub Both came out Type = 3 When I tried it in Excel 2007 before saving the file, I got the same thing. Also no problem if the workbook was an .xlsm workbook. I tweaked my code a bit to see what I'd get for other things. Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" Set x = ThisWorkbook.Charts.Add x.Name = "NewChart1" Set x = ThisWorkbook.Sheets.Add(Type:=xlExcel4MacroSheet) x.Name = "Macrosheet" End Sub Sub CheckSheetType() Debug.Print "NewChart1", ThisWorkbook.Sheets("NewChart1").Type Debug.Print "NewChart", ThisWorkbook.Sheets("Newchart").Type Debug.Print "Macrosheet", ThisWorkbook.Sheets("Macrosheet").Type Debug.Print "SHeet1", ThisWorkbook.Sheets("Sheet1").Type Debug.Print "Sheet2", ThisWorkbook.Sheets("Sheet2").Type Debug.Print "Sheet3", ThisWorkbook.Sheets("SHeet3").Type End Sub I only got -4167 for the regular worksheets. -- HTH, Barb Reinhardt "SixSigmaGuy" wrote: Hi, I added a chart to my workbook using the Sheets.Add command, with the "Type" parameter set to xlChart. But when I walk through the sheets in my workbook and check the "Type" of each, it tells me that my chart is an xlExcel4MacroSheet instead of an xlChart. According to help, xlChart has a value of -4109 and xlExcel4MacroSheet has a value of 3. When I query the Type property of the new Chart I created, VBA says the value is 3, not -4109. Seems like xlChart is set to the wrong value; should it be 3 rather than -4109? Then again, if I add a new sheet using the value of 3 for the type parameter, I get a new worksheet, not a chart. Anyone know what's going on? We were failing to process a lot of charts because the wrong value was coming back. Following is a code snippet showing my problem: Sub AddNewChart() Dim x As Variant Set x = ThisWorkbook.Sheets.Add(Type:=xlChart) x.Name = "NewChart" End Sub Sub CheckSheetType() Debug.Print ThisWorkbook.Sheets("NewChart").Type End Sub Immediate Window shows "3" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT Returning 0 or wrong totals | Excel Worksheet Functions | |||
LOOKUP returning wrong values | Excel Worksheet Functions | |||
Formula returning wrong answer | Excel Worksheet Functions | |||
Formula returning wrong answer | Excel Worksheet Functions | |||
VLOOKUP returning wrong row | Excel Worksheet Functions |