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.

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

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

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

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

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