Height of y-axis

[PeterBunde]

Fellow sufferers!

I wish to adjust a graph so that the y-axis is only just long enough to accomodate . Also, the physical distance on the screen from 0 to 1 mark shall be fixed.

The

.Height =

adjusts the height of the graph itself. I need to adjust the y-axis (and consequently the height of the graph of course).

Thanks in advance.

BW Peter

Public Function popup_graph(urliurli As String)
Dim cht As Chart
    Set url_defect_array = defects_of_category(urliurli)
    Dim ser As Series
    Dim max_value As Long
    Set cht = Sheets("Approval (F3)").ChartObjects.Add(0, 0, 300, 300).Chart
    With cht
        .ChartType = xlColumnStacked
        Set ser = .SeriesCollection.NewSeries
        With ser
           .Name = "Open"
           .XValues = Array("A", "B", "C", "D")
           .Values = Array(url_defect_array("ARed"), url_defect_array("BRed"), url_defect_array("CRed"), url_defect_array("DRed"))
           .Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
        End With
        Set ser = .SeriesCollection.NewSeries
        With ser
           .Name = "ASK"
           .XValues = Array("A", "B", "C", "D")
           .Values = Array(url_defect_array("ABlue"), url_defect_array("BBlue"), url_defect_array("CBlue"), url_defect_array("DBlue"))
           .Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
        End With
        Set ser = .SeriesCollection.NewSeries
        With ser
           .Name = "FIX"
           .XValues = Array("A", "B", "C", "D")
           .Values = Array(url_defect_array("AAmber"), url_defect_array("BAmber"), url_defect_array("CAmber"), url_defect_array("DAmber"))
           .Format.Fill.ForeColor.RGB = RGB(255, 215, 0)
        End With
        charto = .Name
    End With
    Set Shp = ActiveSheet.Shapes(cht.Parent.Name)
    SetChartPosition
    
    max_value = WorksheetFunction.Max(Array(url_defect_array("ARed") + url_defect_array("ABlue") + url_defect_array("AAmber"), url_defect_array("BRed") + url_defect_array("BBlue") + url_defect_array("BAmber"), url_defect_array("CRed") + url_defect_array("CBlue") + url_defect_array("CAmber"), url_defect_array("DRed") + url_defect_array("DBlue") + url_defect_array("DAmber")))
        
    With Shp
    
    .Height = 40 * max_value ' resize 2.5 pt at 72 ppi.
    .Width = 288 ' resize 4.0 pt at 72 ppi.
  
    End With
    
End Function

 

[thisoldman]

I've tried these types of calculations before and they have a tendency to go astray as Excel is so helpful with auto-formatting – adjusting sizes to make axis labels "fit."

There is a Chart.PlotArea property. With a chart selected, go to the VB Immediate window and type in
ActiveChart.PlotArea.Select
and the grab-handles will appear on this object.

The PlotArea has a .Height, a Double measured in points, that includes axis labels. With PlotArea.Width, .Top, and .Left, you can position and size this object. PlotArea doesn't include the chart legend or title.

There is also PlotArea.InsideHeight, InsideWidth, .InsideTop and .InsideLeft which return the measurements in points, as Doubles, of the plot area without the axis labels.

I have to play quite a bit while recording and stepping through macros to get these PlotArea properties right.