Thread: Is aspect ratio possible in an XY scatter

Heya,
Thanks to a tip from Andy Pope, I've made some pretty good head-way on a design-plan-mapping XY scatter plot. My question now is -

Is there a way to maintain aspect ratio through changing dimensions?

Ideally, I'd like to have the chart stay the same size, and assign dimensions accordingly on a 1 - 1 ratio from x to y. Being a plan for a fixed size of products, if it's say, 10' x 100', it should be a long narrow plan, not a x = y size chart, which portrays very distorted dimensions, IE 2' one way = 20' the other. If it's 10 x 10, or even 100 x 100, it looks great, but differences between the lengths and widths are returning wonky aspect ratios.

I'm using VBA through a command button, so if code is required, I can input it there so that it resizes on calculation. The length and width are plainly listed within my sheet.

If it's a simple format option that I can't find, well, I'm an idiot.

mew!

If you set both the axes to be min=0 and max=100 would your calculated values no appear correct?

Maybe this, Mew?

Code:

Sub SetSquareAxes(cht As Chart, dBuf As Double, dInc As Double, _
                  ByVal xMin As Double, ByVal xMax As Double, _
                  ByVal yMin As Double, ByVal yMax As Double)
    ' shg 2009-0220

    ' Sets the chart scales to
    '   o   be of equal span
    '   o   start and end on a multiple of dInc, and have dInc as the major unit
    '   o   contain all points with a minimum buffer distance of dBuf to the edges
    '   o   center the points in the plot area within the constraints above

    ' E.g.,

    '    SetSquareAxes Sheet1.ChartObjects(1).Chart, 100, 500, _
    '                  WorksheetFunction.Min(rngX.Value), _
    '                  WorksheetFunction.Max(rngX.Value), _
    '                  WorksheetFunction.Min(rngY.Value), _
    '                  WorksheetFunction.Max(rngY.Value)

    Static WF   As WorksheetFunction
    Dim xCtr    As Double
    Dim yCtr    As Double
    Dim dRad    As Double   ' half-dimension of bounding box
    Dim dDelta  As Double   ' common span of x and y scales

    ' verify cht is a scatterchart
    Select Case cht.SeriesCollection(1).ChartType
        Case xlXYScatter, xlXYScatterLines, xlXYScatterSmooth, _
             xlXYScatterLinesNoMarkers, xlXYScatterSmoothNoMarkers
        Case Else
            MsgBox "Chart type must be XY (Scatter)", vbOKOnly, "SetSquareAxes"
            Exit Sub
    End Select

    If WF Is Nothing Then Set WF = WorksheetFunction

    ' compute center and bounding box radius
    xCtr = (xMax + xMin) / 2#
    yCtr = (yMax + yMin) / 2#
    dRad = WF.Max(xMax - xCtr, yMax - yCtr) + dBuf

    ' compute the scale minima
    xMin = Int((xCtr - dRad) / dInc) * dInc
    yMin = Int((yCtr - dRad) / dInc) * dInc

    ' compute the common span and scale maxima
    dDelta = WF.Ceiling(WF.Max(xMax - xMin, yMax - yMin) + dBuf, dInc)
    xMax = xMin + dDelta
    yMax = yMin + dDelta

    With cht.Axes(xlCategory)
        .MinimumScale = xMin
        .MaximumScale = xMax
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = False
        .MajorUnit = dInc
    End With

    With cht.Axes(xlValue)
        .MinimumScale = yMin
        .MaximumScale = yMax
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = False
        .MajorUnit = dInc
    End With
End Sub

Thanks for the replies -

Andy - I don't know how to set the mins and max's, if I did, I'd set them both to the highest number between length and width, which is, essentially, all I need to do. min will always = 0 for both, if I could set max for both axes to the larger of the two between length and width, I'd be good to go.

SHG - I can't get that code to budge, either in my workbook, or in a fresh one. Although I'm progressing decently well with VBA, there are still some major rules of thumb I know I'm not familiar with.

Attached is my happy little project thus far.

Thanks by the way, to both of you. This is almost fully functional, and couldn't have done it without the help I've received over the last year from you two, and many others on here.

Oh, almost forgot, you'd enter the length and width, then click ye' ol' button. It figures out your product counts, and maps out the design from there. Only the cells which are tinted dark grey are unlocked in the regular version.

mew!

Mew,

See if the attached does what you want.

Dude,
You are the ossim.
Attached a command at the end of my code to run that macro, rather than bumbling with it now. I'll be taking this home to study your doings. Works perfectly.

Thanks to both of you, couldn't have got started without Andy, and woulda been dead in the water trying to figure that out without SHG.

mew!

Mew,

When you look at this, change some formulas on Sheet1:

A12: =MIN($A$17:$F$1000 (A:A,C:C,E:E ))

A13: =MAX($A$17:$F$1000 (A:A,C:C,E:E ))

B12: =MIN($A$17:$F$1000 (B:B,D:D,F:F ))

B13: =MAX($A$17:$F$1000 (B:B,D:D,F:F ))

k, will do, and one last thing...

Protecting page two doesn't allow the macro you gave me to work. I am once again chart-retarded, and don't know how to allow it to change the values while protected.

Protect the sheet (not Unprotect it) in code with the UserInterfaceOnly option set to True.

BTW, I'm so disappointed in you, Mew. Until you changed the location your profile, I thought you were from Grand Prairie, Texas.

I love to disappoint, just ask the ladies.

Code:

Worksheets("Grid Layout").Protect UserInterfaceOnly:=True

still gets me a method failed on :

Code:

.MinimumScale = xMin

which is, as you know, the first line attempting to change the chart.

It does indeed successfully protect the sheet with no pass required, which is fine (not worried about people intentionally messing with the code, more concerned about unintentional typing/clicking/etc.), but still hinders your ossim macro.

Or should I unprotect at the beginning, and re-protect at the end? That should work, no?

Apparently not...
Not sure how I'm managing to protect it with the unprotect command, but I seem to be finding a way.

mrrrrrrrrrrrrrrrrrrrow!

Did you change the chart axes? Your original plot didn't have them, and I put them back -- it needed them in order to set the scale.

Still can't get it to run with the userinterfaceonly in place, but

activesheet.unprotect

and off I go.

Thanks again.

Oh, and by the way, my Grande Prairie is a lot colder than your Grande Prairie :P