chart "squaring"

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

:





0
nottrue (5)
6/21/2007 11:42:29 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
791 Views

Similar Articles

[PageSpeed] 25

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 :)

-- 
Thanks,
Christopher

This posting is provided "AS IS" with no warranties, and confers no rights. 


"PLP" wrote:

> 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
> 
> :
> 
> 
> 
> 
> 
> 
0
chscott1 (27)
6/22/2007 8:10:00 AM
> 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

..













0
nottrue (5)
6/22/2007 11:32:20 AM
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 



0
nottrue (5)
6/22/2007 12:46:17 PM
Reply:

Similar Artilces:

chart "squaring"
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,x...