Changing diagram size to specific size in mm with VBA

Question

I have an existing Excel file I need to work with. It contains a line graph where measurement results are plotted. All the referencing is done via names. The graph is called "ChartResult".

Obviously Excel discriminates the graph area (the "outer") and the plot area (the "inner") where the graph is plotted. Please correct me if I'm wrong, also learning the preferred english nomenclature would be of great help.

My goal is to print the (page containing the) table so that the division/auxiliary lines have a specific distance from each other. My thinking was that if I define the scale of the axis (the max and min values) and define a size of the graph I would acheive this goal. However in Excel I can only type in the size of the whole graph area, which is the outer thing, so not helpful when I want to define the size of the graph, the inner thing.

I started using VBA to acheive this but haven't been succesful:

Sub Groesse_eingeben()
ActiveSheet.ChartObjects("ChartResult").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.PlotArea.Select
Selection.Left = 0
Selection.Top = 0
Selection.Width = 200
Selection.Height = 200
End Sub

This code is changing the size an position of the graph but not to what I expected it to be. Is the input of Selection.XY in pixels or mm? I naively assumed mm but my graph becomes smaller than 200x200 mm, around 60x60 mm.

Thank you! Chris

System: Microsoft® Excel® 2016 MSO (Version 2204 Build 16.0.15128.20128) Win10 Pro 21H2

Update: Ok, the input size is points. But how to specify the exact size of the graph?

On the screenshot there are two dotted borders: One, the inner, is the actual size of the visible graph the other is the size of the graph object. To cause more confusion: Both are within the graph area, which I called "outer" area above :) How can I input the exact numbers for the actual visible graph?

Answer 1

You can control the outer width of the chart (ChartObject) with the .Width property, and the inner width of the chart with the .Chart.PlotArea.Width property.

Here is a sub that takes a chart and widths as inputs, and updates the chart:

Private Sub SetChartWidths(Ch As ChartObject, OuterWidth As Long, InnerWidth As Long)

    ' Set the outer width of the chart
    Ch.Width = OuterWidth
    
    ' Set the inner width (plot area width)
    Ch.Chart.PlotArea.Width = InnerWidth

End Sub

EDIT START

And here is how you can use the sub:

Private Sub UseSubroutine()

    ' Store the chart object in a variable
    Dim LineChart As ChartObject
    Set LineChart = ThisWorkbook.Sheets("Sheet1").ChartObjects(1)
    
    ' Run the sub
    SetChartWidths Ch:=LineChart, OuterWidth:=200, InnerWidth:=150

End Sub

If you're having trouble using the SetChartWidths sub, and it is in a different Module than the code you're calling it from, you can remove the Private from the front to change the Scope of the sub.

EDIT END

As for what widths to use, that will be up to you.