+ Reply to Thread
Results 1 to 6 of 6

Charts with scaled axis

  1. #1
    Steve
    Guest

    Charts with scaled axis

    Can anyone recommend a procedure for creating large format scaled charts?

    Ideally, I'd like the y-axis to have a scale of 5 inches per unit and an
    x-axis of 1 inch per 500 units.

    Ive used the following macro to set the plot area, but i'd like to automate
    it a bit based on data ranges.

    Sub ResizePlotArea()
    '
    ' Resize Plot Area Macro
    ' Macro recorded 6/8/2006 by steve'

    '
    'Selection.Width =(desired width*72) +36 (36 dependant on axis font)
    'Selection.Height =(desired height*72)+25 (25 dependant on axis font)

    ActiveChart.PlotArea.Select
    Selection.Left = 20
    Selection.Top = 108
    Selection.Width = 360
    Selection.Height = 720
    End Sub

    Thanks.

  2. #2
    Andy Pope
    Guest

    Re: Charts with scaled axis

    Hi,

    Not sure whether the code you have works for you or not. But one thing I
    would point out, and I hope it's not too obvious, is that the plot area
    will be constrained by the chartarea. It will not raise an error but
    also it will not size the plotarea as intended.

    Perhaps start by increasing the chart area by 120% of the required plotarea.

    Cheers
    Andy

    Steve wrote:
    > Can anyone recommend a procedure for creating large format scaled charts?
    >
    > Ideally, I'd like the y-axis to have a scale of 5 inches per unit and an
    > x-axis of 1 inch per 500 units.
    >
    > Ive used the following macro to set the plot area, but i'd like to automate
    > it a bit based on data ranges.
    >
    > Sub ResizePlotArea()
    > '
    > ' Resize Plot Area Macro
    > ' Macro recorded 6/8/2006 by steve'
    >
    > '
    > 'Selection.Width =(desired width*72) +36 (36 dependant on axis font)
    > 'Selection.Height =(desired height*72)+25 (25 dependant on axis font)
    >
    > ActiveChart.PlotArea.Select
    > Selection.Left = 20
    > Selection.Top = 108
    > Selection.Width = 360
    > Selection.Height = 720
    > End Sub
    >
    > Thanks.


    --

    Andy Pope, Microsoft MVP - Excel
    http://www.andypope.info

  3. #3
    Steve
    Guest

    Re: Charts with scaled axis

    Thanks for the reply. I've observed the chart area constraint, so pointers
    that also include a one step process (VBA script(s)) are likely the route I
    need to investigate.

    "Andy Pope" wrote:

    > Hi,
    >
    > Not sure whether the code you have works for you or not. But one thing I
    > would point out, and I hope it's not too obvious, is that the plot area
    > will be constrained by the chartarea. It will not raise an error but
    > also it will not size the plotarea as intended.
    >
    > Perhaps start by increasing the chart area by 120% of the required plotarea.
    >
    > Cheers
    > Andy
    >
    > Steve wrote:
    > > Can anyone recommend a procedure for creating large format scaled charts?
    > >
    > > Ideally, I'd like the y-axis to have a scale of 5 inches per unit and an
    > > x-axis of 1 inch per 500 units.
    > >
    > > Ive used the following macro to set the plot area, but i'd like to automate
    > > it a bit based on data ranges.
    > >
    > > Sub ResizePlotArea()
    > > '
    > > ' Resize Plot Area Macro
    > > ' Macro recorded 6/8/2006 by steve'
    > >
    > > '
    > > 'Selection.Width =(desired width*72) +36 (36 dependant on axis font)
    > > 'Selection.Height =(desired height*72)+25 (25 dependant on axis font)
    > >
    > > ActiveChart.PlotArea.Select
    > > Selection.Left = 20
    > > Selection.Top = 108
    > > Selection.Width = 360
    > > Selection.Height = 720
    > > End Sub
    > >
    > > Thanks.

    >
    > --
    >
    > Andy Pope, Microsoft MVP - Excel
    > http://www.andypope.info
    >


  4. #4
    Steve
    Guest

    Re: Charts with scaled axis

    After I reread my earlier reply, it sounded rather snobby. Not at all what I
    intended.

    I found out the chart area constraint the hard way.

    Ideally if there were a way to calculate plot width and height from the max
    values of the axes as automaticly determined by excel via the formulas below,
    my problem would be solved, e.g.

    {4(max of axes) * 2 (desired scale,2in/unit) * 72 (pts/in.)} + 36 (sz of
    axis annotation)

    The 2,72,and 36 are all constants, all that needs to be done is extract the
    max value of the axis to compute the desired dimension.

    So can one do arithmatic in a recorded macro?

    Thanks.

    "Steve" wrote:

    > Thanks for the reply. I've observed the chart area constraint, so pointers
    > that also include a one step process (VBA script(s)) are likely the route I
    > need to investigate.
    >
    > "Andy Pope" wrote:
    >
    > > Hi,
    > >
    > > Not sure whether the code you have works for you or not. But one thing I
    > > would point out, and I hope it's not too obvious, is that the plot area
    > > will be constrained by the chartarea. It will not raise an error but
    > > also it will not size the plotarea as intended.
    > >
    > > Perhaps start by increasing the chart area by 120% of the required plotarea.
    > >
    > > Cheers
    > > Andy
    > >
    > > Steve wrote:
    > > > Can anyone recommend a procedure for creating large format scaled charts?
    > > >
    > > > Ideally, I'd like the y-axis to have a scale of 5 inches per unit and an
    > > > x-axis of 1 inch per 500 units.
    > > >
    > > > Ive used the following macro to set the plot area, but i'd like to automate
    > > > it a bit based on data ranges.
    > > >
    > > > Sub ResizePlotArea()
    > > > '
    > > > ' Resize Plot Area Macro
    > > > ' Macro recorded 6/8/2006 by steve'
    > > >
    > > > '
    > > > 'Selection.Width =(desired width*72) +36 (36 dependant on axis font)
    > > > 'Selection.Height =(desired height*72)+25 (25 dependant on axis font)
    > > >
    > > > ActiveChart.PlotArea.Select
    > > > Selection.Left = 20
    > > > Selection.Top = 108
    > > > Selection.Width = 360
    > > > Selection.Height = 720
    > > > End Sub
    > > >
    > > > Thanks.

    > >
    > > --
    > >
    > > Andy Pope, Microsoft MVP - Excel
    > > http://www.andypope.info
    > >


  5. #5
    Andy Pope
    Guest

    Re: Charts with scaled axis

    Yes you can use edited macro code to determine the values and perform
    calculations.

    Some like this to tell you axes details. Note that this will work for XY
    Scatter charts which have 2 value axis. For charts with categorical axis
    you will need to count the number of data points in your series.

    Sub X()

    Dim sngXMin As Single
    Dim sngXMax As Single
    Dim sngYMin As Single
    Dim sngYMax As Single
    Dim sngXFontSize As Single
    Dim sngYFontSize As Single

    With ActiveChart
    With .Axes(xlCategory, xlPrimary)
    sngXMin = .MinimumScale
    sngXMax = .MaximumScale
    sngXFontSize = .TickLabels.Font.Size
    End With
    With .Axes(xlValue, xlPrimary)
    sngYMin = .MinimumScale
    sngYMax = .MaximumScale
    sngYFontSize = .TickLabels.Font.Size
    End With
    End With

    End Sub

    Cheers
    Andy

    Steve wrote:
    > After I reread my earlier reply, it sounded rather snobby. Not at all what I
    > intended.
    >
    > I found out the chart area constraint the hard way.
    >
    > Ideally if there were a way to calculate plot width and height from the max
    > values of the axes as automaticly determined by excel via the formulas below,
    > my problem would be solved, e.g.
    >
    > {4(max of axes) * 2 (desired scale,2in/unit) * 72 (pts/in.)} + 36 (sz of
    > axis annotation)
    >
    > The 2,72,and 36 are all constants, all that needs to be done is extract the
    > max value of the axis to compute the desired dimension.
    >
    > So can one do arithmatic in a recorded macro?
    >
    > Thanks.
    >
    > "Steve" wrote:
    >
    >
    >>Thanks for the reply. I've observed the chart area constraint, so pointers
    >>that also include a one step process (VBA script(s)) are likely the route I
    >>need to investigate.
    >>
    >>"Andy Pope" wrote:
    >>
    >>
    >>>Hi,
    >>>
    >>>Not sure whether the code you have works for you or not. But one thing I
    >>>would point out, and I hope it's not too obvious, is that the plot area
    >>>will be constrained by the chartarea. It will not raise an error but
    >>>also it will not size the plotarea as intended.
    >>>
    >>>Perhaps start by increasing the chart area by 120% of the required plotarea.
    >>>
    >>>Cheers
    >>>Andy
    >>>
    >>>Steve wrote:
    >>>
    >>>>Can anyone recommend a procedure for creating large format scaled charts?
    >>>>
    >>>>Ideally, I'd like the y-axis to have a scale of 5 inches per unit and an
    >>>>x-axis of 1 inch per 500 units.
    >>>>
    >>>>Ive used the following macro to set the plot area, but i'd like to automate
    >>>>it a bit based on data ranges.
    >>>>
    >>>>Sub ResizePlotArea()
    >>>>'
    >>>>' Resize Plot Area Macro
    >>>>' Macro recorded 6/8/2006 by steve'
    >>>>
    >>>>'
    >>>>'Selection.Width =(desired width*72) +36 (36 dependant on axis font)
    >>>>'Selection.Height =(desired height*72)+25 (25 dependant on axis font)
    >>>>
    >>>> ActiveChart.PlotArea.Select
    >>>> Selection.Left = 20
    >>>> Selection.Top = 108
    >>>> Selection.Width = 360
    >>>> Selection.Height = 720
    >>>>End Sub
    >>>>
    >>>>Thanks.
    >>>
    >>>--
    >>>
    >>>Andy Pope, Microsoft MVP - Excel
    >>>http://www.andypope.info
    >>>


    --

    Andy Pope, Microsoft MVP - Excel
    http://www.andypope.info

  6. #6
    Steve
    Guest

    Re: Charts with scaled axis

    Thanks Andy. That is just what I was looking for.

    "Andy Pope" wrote:

    > Yes you can use edited macro code to determine the values and perform
    > calculations.
    >
    > Some like this to tell you axes details. Note that this will work for XY
    > Scatter charts which have 2 value axis. For charts with categorical axis
    > you will need to count the number of data points in your series.
    >
    > Sub X()
    >
    > Dim sngXMin As Single
    > Dim sngXMax As Single
    > Dim sngYMin As Single
    > Dim sngYMax As Single
    > Dim sngXFontSize As Single
    > Dim sngYFontSize As Single
    >
    > With ActiveChart
    > With .Axes(xlCategory, xlPrimary)
    > sngXMin = .MinimumScale
    > sngXMax = .MaximumScale
    > sngXFontSize = .TickLabels.Font.Size
    > End With
    > With .Axes(xlValue, xlPrimary)
    > sngYMin = .MinimumScale
    > sngYMax = .MaximumScale
    > sngYFontSize = .TickLabels.Font.Size
    > End With
    > End With
    >
    > End Sub
    >
    > Cheers
    > Andy
    >
    > Steve wrote:
    > > After I reread my earlier reply, it sounded rather snobby. Not at all what I
    > > intended.
    > >
    > > I found out the chart area constraint the hard way.
    > >
    > > Ideally if there were a way to calculate plot width and height from the max
    > > values of the axes as automaticly determined by excel via the formulas below,
    > > my problem would be solved, e.g.
    > >
    > > {4(max of axes) * 2 (desired scale,2in/unit) * 72 (pts/in.)} + 36 (sz of
    > > axis annotation)
    > >
    > > The 2,72,and 36 are all constants, all that needs to be done is extract the
    > > max value of the axis to compute the desired dimension.
    > >
    > > So can one do arithmatic in a recorded macro?
    > >
    > > Thanks.
    > >
    > > "Steve" wrote:
    > >
    > >
    > >>Thanks for the reply. I've observed the chart area constraint, so pointers
    > >>that also include a one step process (VBA script(s)) are likely the route I
    > >>need to investigate.
    > >>
    > >>"Andy Pope" wrote:
    > >>
    > >>
    > >>>Hi,
    > >>>
    > >>>Not sure whether the code you have works for you or not. But one thing I
    > >>>would point out, and I hope it's not too obvious, is that the plot area
    > >>>will be constrained by the chartarea. It will not raise an error but
    > >>>also it will not size the plotarea as intended.
    > >>>
    > >>>Perhaps start by increasing the chart area by 120% of the required plotarea.
    > >>>
    > >>>Cheers
    > >>>Andy
    > >>>
    > >>>Steve wrote:
    > >>>
    > >>>>Can anyone recommend a procedure for creating large format scaled charts?
    > >>>>
    > >>>>Ideally, I'd like the y-axis to have a scale of 5 inches per unit and an
    > >>>>x-axis of 1 inch per 500 units.
    > >>>>
    > >>>>Ive used the following macro to set the plot area, but i'd like to automate
    > >>>>it a bit based on data ranges.
    > >>>>
    > >>>>Sub ResizePlotArea()
    > >>>>'
    > >>>>' Resize Plot Area Macro
    > >>>>' Macro recorded 6/8/2006 by steve'
    > >>>>
    > >>>>'
    > >>>>'Selection.Width =(desired width*72) +36 (36 dependant on axis font)
    > >>>>'Selection.Height =(desired height*72)+25 (25 dependant on axis font)
    > >>>>
    > >>>> ActiveChart.PlotArea.Select
    > >>>> Selection.Left = 20
    > >>>> Selection.Top = 108
    > >>>> Selection.Width = 360
    > >>>> Selection.Height = 720
    > >>>>End Sub
    > >>>>
    > >>>>Thanks.
    > >>>
    > >>>--
    > >>>
    > >>>Andy Pope, Microsoft MVP - Excel
    > >>>http://www.andypope.info
    > >>>

    >
    > --
    >
    > Andy Pope, Microsoft MVP - Excel
    > http://www.andypope.info
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1