Consulting

Results 1 to 5 of 5

Thread: Sleeper: Charting Hell

  1. #1
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location

    Sleeper: Charting Hell

    Any of you who have tried to format a chart using VBA will know what a pain in the ass it is.

    But do any of you know why
    [
    CODE]with FetchChart
    .PlotArea.Height = 200
    End With[/CODE]

    sets .PlotArea.Height to whatever Excel feels like,

    while

    With Sheets(FetchChart.Parent.Parent.Name).ChartObjects(FetchChart.Parent.Name).Chart
    .PlotArea.Height = 200
    End With
    sets it to almost the right value (+/- Excel's tweakings)?

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    ChartObjects are inherently parts of the worksheet. This means you must specify what worksheet they are on just as you would any other worksheet related object. The only difference being that you do not get the luxury as to assume you are dealing with the activesheet that we do with the other objects.

    Why? I have no idea. I would think that it was an oversight by MS because they didn't put forth the effort that they did into everything else.

  3. #3
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Yeah, it's a little strange. Most chart properties set fine with either method, but PlotArea seems to have issues. I'm having quite a few similar problems now that I'm tidying up my code (in Excel 2000) by replacing objects with references to the objects. Some of these new issues are just lack of updating (such as Chart.CopyAsPicture giving an outdated image) and can be fixed by inserting a Application.CalculateFull. Others, like the PlotArea just don't set correctly.

    A related question: Is there a direct way of knowing Chart.ChartTitle.Height and Chart.Axes(xlCategory).AxisTitle.Height? I'm thinking that there isn't. It's a problem when changing font size in the titles and then optimizing the plot size. Right now, my 'fix' is to:

    1. Set all the chart text objects to .AutoScaleFont = False
    2. Set the ChartObject.Width and .Height to something tiny
    3. Set the ChartObject.Width and .Height back to the correct size, so that Excel automatically reformats the whole thing.

    It works, but it would be nice to know the actual height values.

  4. #4
    MS Excel MVP VBAX Mentor Andy Pope's Avatar
    Joined
    May 2004
    Location
    Essex, England
    Posts
    344
    Location
    Here is some code to determine the chart titles width and heigth.
    The technique can be used with any of the chart label items.
    There is also code to adjust the plot area. This works as expected unless there is not enough room in the chartarea to achieve the specified height.


    Sub X()
    Dim sngHeight As Single
        Dim sngTitleLeft As Single
        Dim sngTitleTop As Single
        Dim sngTitleWidth As Single
        Dim sngTitleHeight As Single
    sngHeight = 200
        With ActiveChart
            MsgBox "Current plotarea height is " & .PlotArea.Height
            .PlotArea.Height = sngHeight
            MsgBox "and now plotarea height is " & .PlotArea.Height
    If .HasTitle Then
                sngTitleLeft = .ChartTitle.Left
                sngTitleTop = .ChartTitle.Top
                .ChartTitle.Left = .ChartArea.Width
                sngTitleWidth = .ChartArea.Width - .ChartTitle.Left
                .ChartTitle.Top = .ChartArea.Height
                sngTitleHeight = .ChartArea.Height - .ChartTitle.Top
                ' re position title
                .ChartTitle.Left = sngTitleLeft
                .ChartTitle.Top = sngTitleTop
                MsgBox "Chart Title Width = " & sngTitleWidth & Chr(10) _
                    & "Chart Title Height = " & sngTitleHeight
            End If
        End With
    End Sub
    Cheers
    Andy

  5. #5
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Thanks, Andy. I'll have to check that out later when I have a bit more time. Cheers!

Posting Permissions

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