chart "squaring"

[PLP]

I'm trying to "square" a chart...
I mean:
given 2 points (P1 and P2) with coordinates
P1(x1,y1) and P2(x2,y2), I want set the vertical (y) distance between P2 and
P1 equal to the horizontal distance between the points (on the monitor, in
pixels).

I start with a chart, and after drawing the chart (let's say with
..plotarea.width = k) I want resize it to fit my need.

From a matematichal point of view, the problem is very simple.
What I want is that:
(y2-y1)
-------- = 1
(x2-x1)

So (y2-y1) = (x2-x1).
Obviously you have to consider the value in pixel of both y2, y1 and x2,x1
pairs.
The distance in pixel is:
(y2-y1) =((y2actualvalue - y1actualvalue)* (.MaximumscaleY -
..MinimumscaleY))/.InsideHeight

I have to change the .plotarea.insidewidth, let's say from k to k'.
In excel vba language it should be something like this:

..PlotArea.InsideWidth = ((y2 -y1) * ActiveChart.PlotArea.InsideHeight *
(MaximumscaleX - MinimumscaleX)) / ((MaximumscaleY - MinimumscaleYs) *
(x2 -x1) )

(I know the formula is not correct, it's just to give you an idea; I wrote
the right formula in tha actual subroutine)

The problem is that I cannot change the .plotarea.insidewidth value but only
the .plotarea.widht value.
There is some relation between the .insidewidth and the .width value?
I tryed guessing width = .insidewidth + left but it does'nt work.....
Then I tryed with .insidewidth + 2 * left (maybe there is a right
also ), but it doesn't work again.

The best result I reached so far is using a Do while... loop with a little
change in the width of just one pixel for time, and then verifying the new
(y2-y1)/(x2-x1) ratio.
Is quite good, but non perfect....
Any ideas?

Tkx

Pierluigi

:

 

[Guest]

Hi Pierluigi, it's been a while since my algebra days, so pardon me if I
misunderstand but if I think you want one "unit" on the y-axis to always be
the same number of pixels as one unit on the x-axis, regardless of how the
chart is resized?

The PlotArea.Width is the InsideWidth plus the area the axis needs to draw
the axis tickmarks and labels. You can see this extra width when you drag the
plot area to resize.

In Excel 2003 and previous, resizing the chart would cause the fonts to
enlarge which could create a difference in Width minus the InsideWidth. This
shouldn't happen in Excel 2007 since the fonts no longer "Auto Scale" when
the chart is resized. Do you think this autoscaling of fonts might be part
of the problem? The InsideWidth doesn't seem to be adjustable to me either.

What if you turn off font scaling via ActiveChart.ChartArea.AutoScaleFont =
False. Or formatting the Chart Area and turning of Auto Scale Fonts on the
font tab? I think that should keep the difference between Width and
InsideWidth constant and then you can adjust PlotArea.Width to fit your
algorithm.

It sounds like you have found a pretty creative workaround though

 

[PLP]

Hi Pierluigi, it's been a while since my algebra days, so pardon me if I

misunderstand but if I think you want one "unit" on the y-axis to always
be
the same number of pixels as one unit on the x-axis, regardless of how the
chart is resized?

Hi Christopher.
Not exactly.
I have a xlCategory axes (X axes) and a xlValue Axis (Y axes).
Let's say xlValue axis .minimumscale is 2 and the xlValue axis .maximumscale
is 10.
And let's say I have 250 plotted values.
So on the xlCategory axes I have numbers from 1 to 250.
Sometimes I want the xlCategory distance (in pixels) between the (let's say)
200th value and the (let's say) 150th value is let's say D (it means that
the xlValue for the 200th point is the value of the 150th point value plus D
pixels).
Sometimes I want the xlCategory distance is D' pixels, because the xlValue
are different and the new "distance" is D' pixels.
Obviously also the xlCategory pair of value could change, it depends from
the data.
So for every data callections I have a different "squared" chart, a
different pair of xlCategory values to which correspond a different pair of
xlValue values...

The PlotArea.Width is the InsideWidth plus the area the axis needs to draw
the axis tickmarks and labels. You can see this extra width when you drag
the
plot area to resize.

Is it possible to know the pixels width of this extra areas?
Is it = (.plotarea.width - .plotarea.inside.width) ?
--- dissolvence ----
Yes it is... I tried just now.... ))))
TKX!!!

What if you turn off font scaling via ActiveChart.ChartArea.AutoScaleFont
=
False. Or formatting the Chart Area and turning of Auto Scale Fonts on the
font tab? I think that should keep the difference between Width and
InsideWidth constant and then you can adjust PlotArea.Width to fit your
algorithm.

Well, I'll try this approach and I'll tell you!

It sounds like you have found a pretty creative workaround though

Tkx.
BTW I found this page (http://peltiertech.com/Excel/Charts/SquareGrid.html)
And here I found the same kind of approach of minimum adjiustaments.
So I think is the best way to keep the "error" minimum (I mean the little
difference -little less than one pixel- between (y2-y1) and the new
(x2-x1)....
But there is always an "error"... :-(
Anyway the try-and-verifying method is quite long in time, so I want to try
the other way...
But the try-and-verifying, at least, is funny because if you let the
Application.ScreenUpdating = True, you get on the screen a "cartoon" of the
chart resizing itself....

Thank you again.

Pierluigi

..

 

[PLP]

So, the SOLUTION!!!
Quite good, error very very little!
Disclaimer: I traslated the sub from the italian to english, so may be there
is some errors....
But the skeleton is good (I think )

sub quarechart()
Dim TanTheta as Single ' Theta is the angle between the two points
P2(x2,y2) and P1(x1,y1)
Dim FirstY as Single, SecondY as Single
Dim FirstX as Single, SecondX as Single
Dim LargeOut as Single, TallOut as Single
Dim LargeIn as Single, TallIn as Single
Dim Base as Single, Height as Single
Dim LastYScale as Single, FirstYScale as Single
Dim LastXscale as Single, FirstXscale as Single
Dim BGFB1 as Single, BGFB2 as Single

Application.ScreenUpdating = False
Sheets("chart1").activate

' Let's block autoscalefont and set our font
' for y axis
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = False
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
' and for x axis
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = False
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

'let's block automatic scaling
With ActiveChart.Axes(xlValue)
.MaximumScaleIsAuto = False
.MinimumScaleIsAuto = False
End With


'Original size
LargeIn = ActiveChart.PlotArea.InsideWidth
TallIn = ActiveChart.PlotArea.InsideHeight
LargeOut = ActiveChart.PlotArea.Width
TallOut = ActiveChart.PlotArea.Height
' two variant for the rest. BG is for Bill Gates. FB is for..... use
your imagination
BGFB1 = LargeOut-LargeIn
BGFB2 = TallOut-TallIn

FirstY= 'put here your actual value for the first Y
SecondY= ''put here your actual value for the second Y

FirstX= 'put here your actual value for the first X
SecondX= 'put here your actual value for the second X
' usually the X values are the row numbers of the column in which data
are store, less the number of the first row
' from which data starts plus 1. This is true if the data are stored in
columns, obviously

With ActiveChart.Axes(xlValue)
LastYScale = .MaximumScale
FirstYScale = .MinimumScale
End With

LastXScale = 'this is the last row of data (if in columns)
FirstXScale= 'this is the first row of data (if in columns)

TanTheta = (SecondY - FirstY) / (SecondX- FirstX)
Select Case TanTheta
Case is < 1 'the chart is too large
.PlotArea.Width = ((SecondY - FirstY) * TallIn *
(LastXScale - FirstXScale + 1)) / ((LastYScale - FirstYScale) * (SecondX -
FirstX)) + BGFB1
'let's verify
LargeIn = ActiveChart.PlotArea.InsideWidth
TallIn = ActiveChart.PlotArea.InsideHeight
Base = ((SecondX - FirstX) * LargeIn) /
(LastXScale-FirstXScale+ 1)
Height = ((SecondY - FirstY) * TallIn) / (LastYScale -
FirstYScale)
MsgBox "base " & base & " Height" & Height

Case is > 1 'the chart is too tall
' the new .plotarea.Height is simple to find, the formula is
similar to the previous one; but here we use BGFB2 as costant.


End Select
endsub