Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / July 2008

Tip: Looking for answers? Try searching our database.

xlChart returning wrong value

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
SixSigmaGuy - 26 Jul 2008 00:38 GMT
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"
Barb Reinhardt - 26 Jul 2008 01:40 GMT
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.
Signature

HTH,
Barb Reinhardt

> Hi,
>
[quoted text clipped - 25 lines]
>
> Immediate Window shows "3"
SixSigmaGuy - 26 Jul 2008 02:13 GMT
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.

>I ran this in Excel 2003 and didn't have an issue.
>
[quoted text clipped - 77 lines]
>>
>> Immediate Window shows "3"
Barb Reinhardt - 26 Jul 2008 02:17 GMT
What exactly are you trying to do?  Maybe it can be done without using the
type.

Barb Reinhardt

> 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
[quoted text clipped - 97 lines]
> >>
> >> Immediate Window shows "3"
SixSigmaGuy - 26 Jul 2008 02:32 GMT
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.

> What exactly are you trying to do?  Maybe it can be done without using the
> type.
[quoted text clipped - 108 lines]
>> >>
>> >> Immediate Window shows "3"
SixSigmaGuy - 26 Jul 2008 02:52 GMT
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?

> 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.
[quoted text clipped - 114 lines]
>>> >>
>>> >> Immediate Window shows "3"
Peter T - 26 Jul 2008 12:05 GMT
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

> 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
[quoted text clipped - 122 lines]
>>>> >>
>>>> >> Immediate Window shows "3"
SixSigmaGuy - 29 Jul 2008 03:47 GMT
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.

> 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
[quoted text clipped - 152 lines]
>>>>> >>
>>>>> >> Immediate Window shows "3"
Barb Reinhardt - 26 Jul 2008 01:43 GMT
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
Signature

HTH,
Barb Reinhardt

> Hi,
>
[quoted text clipped - 25 lines]
>
> Immediate Window shows "3"
SixSigmaGuy - 26 Jul 2008 02:15 GMT
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.

> Have you tried using something like this (for separate worksheets in the
> workbook
[quoted text clipped - 51 lines]
>>
>> Immediate Window shows "3"
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.