VBA Resize Chart Area and Plot Area

Check this code:

Sub Create_Column_Chart_xx()

  Dim objChart As ChartObject
  Dim myChtRange As Range
  Dim myDataRange As Range
  
  With ActiveSheet
    Set myChtRange = Range("B9:C23")
    Set myDataRange = Range("B2:C3")
    Set objChart = .ChartObjects.Add( _
        Left:=myChtRange.Left, Top:=myChtRange.Top, _
        Width:=myChtRange.Width, Height:=myChtRange.Height)
    With objChart.Chart
        .ChartArea.AutoScaleFont = False
        .ChartType = xlColumnClustered
        .SetSourceData Source:=myDataRange
        .SeriesCollection(1).Name = Range("B1")
        .SeriesNameLevel = xlSeriesNameLevelCustom
        .HasLegend = False
        
        .HasTitle = True
        
        .ChartArea.RoundedCorners = False
        .ChartArea.Format.Line.Visible = True
        .ChartArea.Format.Line.Weight = 6
        .ChartArea.Format.Line.ForeColor.RGB = RGB(0, 176, 80)
        .SetElement (msoElementPrimaryValueGridLinesNone)
        ' X-Axis
        .HasAxis(xlCategory) = True
            .Axes(xlCategory).Format.Line.Visible = msoFalse
            .Axes(xlCategory).MajorTickMark = xlNone
            .Axes(xlCategory).TickLabelPosition = xlLow
            .Axes(xlCategory).TickLabels.Font.Name = "Arial Narrow"
            .Axes(xlCategory).TickLabels.Font.Size = 12
            .Axes(xlCategory).TickLabels.Font.Bold = True
        ' Y-Axis
        .HasAxis(xlValue) = True
            .Axes(xlValue).Format.Line.Visible = msoFalse
            .Axes(xlValue).Format.Fill.Visible = msoFalse
            .Axes(xlValue).MaximumScale = 3000
            .Axes(xlValue).MinimumScale = -3000
            .Axes(xlValue).MajorTickMark = xlNone
            .Axes(xlValue).TickLabelPosition = xlNone

        objChart.Chart.PlotArea.Select
        objChart.Chart.PlotArea.Left = -4 * 0.75
        objChart.Chart.PlotArea.Width = objChart.Width
        objChart.Chart.PlotArea.Top = (objChart.Height - objChart.Chart.PlotArea.Height) / 2
        ActiveCell.Activate
    End With
  End With
End Sub

Artik

Hi Artik,

very nice!

1. How did you calculate this value?

objChart.Chart.PlotArea.Left = -4 * 0.75

2. Your code works perfectly with an unformatted, standard Chart Area.
Might there be a way to add a custom border weight into the calculation?
Maybe even as a variable?

So let's say:
- Cell Width = 65 Pixels
- Chart Area Width = 2 Cells Wide = 130 Pixels
- Border Weight = 6 Pixels

Without any resizing, the Chart Area covers the neighboring cells, left and right, with half of the Border Weight = 3 Pixels.
So I resize the Chart Area to exactly and only cover the selected Cell Range.
This is the first thing to do:

' ChartArea Position
        ' Margin Left: 65 Px
        objChart.Left = 68 ' =65+3(LineWeight/2)
        ' ChartArea Width
        ' Width: 2x65 = 130
        objChart.Width = 124 ' =130-6(LineWeight)

So now the Chart Width is resized from 130 Pixels wide to 124 Pixels wide.

Second thing to do: Resize the Plot Area Width accordingly.

So might there be a way to say:
a.
“Chart Area Width = Chart Area Width - 2x Border Width/2” (with Border Width 6 this would be: 2x6=12/2=6)
And then say:
b.
"Plot Area Width = Chart Area Width:

objChart.Chart.PlotArea.Width = objChart.Width

The problem is the combination of resizing the chart area first and then resizing the plot area in there.
So the “Plot Area resize procedure” needs to know, that the Chart Area has been resized.
Is this even possible? Or does it need two separate Macros for Excel to not be confused?

The idea is to align a number of charts with wider borders without overlapping the neighboring cells.
And then have all Plot Areas perfectly aligned in those Chart Areas.

Thanks again!
Toni

Finally managed to resize the Plot Area's Width to exactly sit between the Chart Borders.
But hitting the right number for .PlotArea.Left was a matter of trial and error. (Not too satisfying.)
And this example is based on a cell width of 47 Pixels and needs to be adapted to your cell width.
Which means for now: record a Macro, move the Plot Area and go from there.

And: I've realised, that this only works with Ticklabels Font Size = 8 or less.
I've placed "AutoScaleFont = False" in 4 different places:
.ChartTitle
.ChartArea
.Ticklabels x-Axis
.Ticklabels y-Axis
Still, the Plot Area's Size shrinks, starting with Ticklabels Font Size = 9.

So the first question is more or less solved, if you have a "standard" cell width and have some patience,
you can dial into the right .PlotArea.Left value. Should anybody have an actual mathematical solution, I'd be all ears.
I wonder, if the Border Weight can somehow be included into the equation.

So now, what's left to solve, is AutoScaleFont.
What am I missing?

Thank you!

Tweaked around a bit more, got the values for Font Size 10:

objChart.Chart.PlotArea.Left = -3 * 0.75
objChart.Chart.PlotArea.Width = 84.6

Aren't these strange?

Do not use absolute values. All dimensions can be calculated.
Try the following code:

Sub Create_Column_Chart_xx()

    Dim objChart As ChartObject
    Dim myChtRange As Range
    Dim myDataRange As Range

    Const LINEWGHT As Single = 6

    With ActiveSheet
        Set myChtRange = .Range("B9:C23")
        Set myDataRange = .Range("B2:C3")

        Set objChart = .ChartObjects.Add( _
                       Left:=myChtRange.Left + LINEWGHT / 2, Top:=myChtRange.Top + LINEWGHT / 2, _
                       Width:=myChtRange.Width - LINEWGHT, Height:=myChtRange.Height - LINEWGHT)

        With objChart.Chart
            .ChartArea.AutoScaleFont = False
            .ChartType = xlColumnClustered
            .SetSourceData Source:=myDataRange
            .SeriesCollection(1).Name = Range("B1")
            .SeriesNameLevel = xlSeriesNameLevelCustom
            .HasLegend = False
            .HasTitle = False

            .ChartArea.RoundedCorners = False
            .ChartArea.Format.Line.Visible = True
            .ChartArea.Format.Line.Weight = LINEWGHT
            .ChartArea.Format.Line.ForeColor.RGB = RGB(0, 176, 80)
            .SetElement (msoElementPrimaryValueGridLinesNone)
            ' X-Axis
            .HasAxis(xlCategory) = True
            .Axes(xlCategory).Format.Line.Visible = msoFalse
            .Axes(xlCategory).MajorTickMark = xlNone
            .Axes(xlCategory).TickLabelPosition = xlLow
            .Axes(xlCategory).TickLabels.Font.Name = "Arial Narrow"
            .Axes(xlCategory).TickLabels.Font.Size = 12
            .Axes(xlCategory).TickLabels.Font.Bold = True
            ' Y-Axis
            .HasAxis(xlValue) = True
            .Axes(xlValue).Format.Line.Visible = msoFalse
            .Axes(xlValue).Format.Fill.Visible = msoFalse
            .Axes(xlValue).MaximumScale = 3000
            .Axes(xlValue).MinimumScale = -3000
            .Axes(xlValue).MajorTickMark = xlNone
            .Axes(xlValue).TickLabelPosition = xlNone
            objChart.Chart.PlotArea.Select

            'It is certain that the PlotArea is maximally shifted to the left edge,
            'because it will not go beyond the ChartObject area.
            objChart.Chart.PlotArea.Left = -objChart.Width

            'It is certain that the width of the PlotArea is maximized,
            'because the right edge will definitely not go beyond the ChartObject area
            objChart.Chart.PlotArea.Width = objChart.Width + 10

            objChart.Chart.PlotArea.Top = (objChart.Chart.ChartArea.Height - objChart.Chart.PlotArea.Height) / 2

            ActiveCell.Activate
        End With
    End With
End Sub

Artik

Hi Artik.

This is totally amazing!

So sweet, to have the border weight included in the chart area setting.
And not having to deal with counting pixels anymore is so much more fun.

Two things:
1. Where to place AutoScaleFont = False?
Unfortunately, the Plot Area still resizes, depending on the Label Font Size.
2. Might it also be possible, as an option, to have the Plot Area exactly touch the border?
With "zero air"? So that each bar would be exactly centered in their spaces?
I was able to do this manually, but with absolute values only.

@Andy: I've tried both codes, both work for me.
Should there be a difference in the outcome?

Thanks Artik, thanks Andy!

Thanks Andy

Sorry, not to touch the cell edges, to touch the border.
I've been able to do this by recording a Macro and use those absolute values.
So just wondering, if this could also go into the equation somehow.

Like this (for super narrow spaces):

Ok. So the chart object is the plot area.
The code does exactly that, thanks again! But only, if the axis labels are turned off (visible=false).

I've placed "AutoScaleFont = False" in 4 different places:

.ChartTitle
.ChartArea
.Ticklabels x-Axis
.Ticklabels y-Axis

Still, the Plot Area's Size is affected by the Ticklabels Font Size.

Ok, tried that as well, same "bug": the plot area only stays in place with x-axis labels off.
So maybe just go with Textbox Labels?

A bit strange: I've been applying the code in different worksheets with similarly narrow cell widths.
In some cases, the plot area jumps, in others not.
So in some cases textbox labels will do, in others the axis labels work just fine (with mentioned font sizes).

Thank you very much Artik!
Thank you very much Andy!