Consulting

Results 1 to 9 of 9

Thread: When does a Chart object become a ChartObject object

  1. #1

    Question When does a Chart object become a ChartObject object

    Guys,
    I am having problem differentiating between Chart object and ChartObject object. Please looking into the code shown below and oblige me with explanation.

    Sub Macro1()
    ActiveWorkbook.Worksheets("Raw Data").Activate
        With ActiveSheet.Shapes.AddChart.Select  
            With ActiveChart
                .ChartType = xlXYScatterSmoothNoMarkers
                .HasAxis(xlValue, xlPrimary) = True
                .HasAxis(xlCategory, xlPrimary) = True
                .SeriesCollection.NewSeries
                .SeriesCollection(ActiveChart.SeriesCollection.Count).Name = "=""Whatever"""
                .SeriesCollection(ActiveChart.SeriesCollection.Count).XValues = "='Sheet1'!$D$2:$D$133"  'x-axis
                .SeriesCollection(ActiveChart.SeriesCollection.Count).Values = "='Sheet1'!$E$2:$E$133"   'y-axis
                
                ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Select
                    With Selection.Format.Line
                            .Style = msoLineSingle
                            .Weight = 4
                            .Visible = msoTrue
                            .Transparency = 0
                            .ForeColor.RGB = RGB(255, 0, 0)
                            .DashStyle = msoLineSolid
                    End With
    
    
    ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Activate
            With ActiveChart
                .Width = 300 'The following four lines give error message that says "Method or Data Member Not Found"
                .Left = 300
                .Height = 300
                .RoundedCorners = True
                
                .HasTitle = True
                .ChartTitle.Text = "Blah blah blah"
                .ChartTitle.Select
                With Selection
                    .Orientation = xlHorizontal
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlBottom
                End With
        End with
    End Sub
    I thought the command "ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Activate" would return a ChartObject object. That doesn't seem to be the case. My questions are:-
    1. Does the ChartObjects collection contain objects that are not necessarily ChartObject objects?
    2. When does a Chart object become a ChartObject object?


    Thanks.

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Why do you think that is not the case? Intellisense would tell you there is no Width property. Recording a macro will give the syntax if needed. There is a difference between chart and plot area.

    ActiveChart.ChartArea.Select
        ActiveSheet.Shapes("Chart 1").ScaleWidth 1.0760456274, msoFalse, _
            msoScaleFromTopLeft
        ActiveSheet.Shapes("Chart 1").ScaleHeight 1.1590214067, msoFalse, _
            msoScaleFromTopLeft
        ActiveChart.PlotArea.Select
        Selection.Width = 356.428
        Selection.Height = 227.532
    Try selecting the chart manually and then in the Immediate Window of the VBE, type: ActiveChart.
    The period would open the intellisense options.

  3. #3
    Thank you Sir. What does the ChartObjects collection contain - Chart objects or ChartObject objects? In other words, what does a command such as ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count) return? I thought it would return a ChartObject object and a ChartObject object ought to have the .Width, .Height, .Left property. I am really confused about this.

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count) would return one chart object from the collection of ChartObjects. Always use Option Explicit as the first line of code in a Module, Sheet object, etc. Be sure that Auto List Members is selected in your VBE menus Tools > Options.

    In the VBE, you can use F2 to browse commands. Press F1 for a selected command or in the code editor, with cursor in or next to a command word or object, press F1 for detailed help. But as I said, the macro editor is your friend if you are not familiar with a command. Of course you can always use Microsoft to view references like chartobject and chartobjects. http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx

  5. #5
    Again, thank you. How does one get a ChartObject object? That is, which collection would return a ChartObject object?

    Talking of recording macros, I have tried to record the steps of formatting a ChartArea or a PlotArea; like changing the background color, forecolor, pattern etc. The macro did not record any of the formatting steps. All I got was ActiveChart.Activate. Has anybody experienced this behavior or am I missing something?

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    The ChartObjects collection contains ChartObjects and your code above returns a ChartObject, but you then refer to ActiveChart.

    A ChartObject is the container for a Chart that is embedded in worksheets.
    Be as you wish to seem

  7. #7
    Thank you Sir. The confusion is a bit clearer now. The "ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Select" returns a ChartObject object, but the ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Activate returns a Chart object. I am only wondering now why there is a ChartObject object instead of just having to deal with a Chart object.

    Macro recording does not record the steps in formatting ChartArea or PlotArea. I am using Office 2007. Had anybody had this experience or is there another way to record the steps?

  8. #8
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    Select and Activate don't return anything at all.

    The ChartObject is essentially a containing shape so you use that to change the dimensions/location of the chart within the sheet. It would not make sense for a Chart to have these properties when it is located as a chart sheet.

    The macro recorder is broken in 2007 and fails to record most steps taken with the new (at the time) chart model.
    Be as you wish to seem

  9. #9
    Apology for using the word "returns". It is just the way I see it in my mind's eyes. Your explanation makes the issue even clearer.

    Regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •