Positioning of PlotArea within ChartArea in Powerpoint with VBA

Question

(Post updated with entire code, sorry about the beginner mistake)

Newbie to both coding and VBA here and I'm trying to adjust the PlotArea for a Chart in a presentation. I'm running this from Excel.

Creating and populating the Chart goes fine, sizing ChartArea is also no problems and formating all titles etc is also without problems.

When the Chart looks athe way I want it to, is the correct size and at the correct place, I want the PlotArea to be a precise size and in a precise location. Sizing goes well but the position does not work.

Here is the code that I use, Including populating the ChartData with dummy data and adding in a red box to show where I want the PlotArea to sit:

Sub CreateChart()

'Declare Excel Object Variables
Dim pptWorkBook As Excel.Workbook
Dim pptWorkSheet As Excel.Worksheet

'Declare PowerPoint Object Variables
Dim PPTApp As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTSlide As PowerPoint.Slide
Dim PPTShape As PowerPoint.Shape
Dim PPTChart As PowerPoint.Chart
Dim PPTChartData As PowerPoint.ChartData
Dim SldHeight, SldWidth As Integer
Dim ChrHeight, ChrWidth As Single
Dim PlotHeight, PlotWidth As Double

'Declare Excel Object Variable
Dim ExcRange As Range
    
'Create a new instance of Powerpoint
Set PPTApp = New PowerPoint.Application
    PPTApp.Visible = True

'Create a new Presentation within the Application
Set PPTPres = PPTApp.Presentations.Add

'Disable Snap-To-Grid
PPTPres.SnapToGrid = msoFalse

'Create a new slide within the Presentation
Set PPTSlide = PPTPres.Slides.Add(1, ppLayoutBlank)

'Find out size (points) of Slide
SldHeight = PPTPres.PageSetup.SlideHeight
SldWidth = PPTPres.PageSetup.SlideWidth

'Calculate Chart and Plot Size
ChrWidth = 954
ChrHeight = 525 - 106
PlotWidth = 866 - 95
PlotHeight = 437 - 106 - 20

'No screen updates
Application.ScreenUpdating = False

'Create a new Chart within the Slide, give it proper size
Set PPTShape = PPTSlide.Shapes.AddChart2(-1, xlColumnClustered, 0, 106, ChrWidth, ChrHeight, True)

'Minimize ChartData
PPTShape.Chart.ChartData.Workbook.Application.WindowState = -4140

'Set chartdata
Set PPTChartData = PPTShape.Chart.ChartData

'Set Workbook object reference
Set pptWorkBook = PPTChartData.Workbook

'Set Worksheet object reference
Set pptWorkSheet = pptWorkBook.Worksheets(1)

'Add Data
pptWorkSheet.ListObjects("Table1").Resize pptWorkSheet.Range("A1:B5")
pptWorkSheet.Range("b1").Value = "Items"
pptWorkSheet.Range("a2").Value = "Bikes"
pptWorkSheet.Range("a3").Value = "Accessories"
pptWorkSheet.Range("a4").Value = "Repairs"
pptWorkSheet.Range("a5").Value = "Clothing"
pptWorkSheet.Range("b2").Value = "1000"
pptWorkSheet.Range("b3").Value = "2500"
pptWorkSheet.Range("b4").Value = "4000"
pptWorkSheet.Range("b5").Value = "3000"

 
'Apply Style
With PPTShape.Chart
    .ChartStyle = 4
End With

'Remove title
With PPTShape.Chart
    .HasTitle = False
End With

'Format legend
With PPTShape.Chart
    .HasLegend = True
    .Legend.Position = xlLegendPositionTop
    .Legend.Top = 0
End With

'Add axis title
With PPTShape.Chart.Axes(xlValue)
    .HasTitle = True
    .AxisTitle.Text = "Dollars"
End With

'Remove gridlines
With PPTShape.Chart.Axes(xlValue)
    .HasMajorGridlines = False
    .HasMinorGridlines = False
End With
 
'Add data labels
PPTShape.Chart.ApplyDataLabels

'Set PlotArea position and size
With PPTShape.Chart.PlotArea
    .InsideLeft = 95
    .InsideTop = 20
    .InsideWidth = PlotWidth
    .InsideHeight = PlotHeight
End With

'Adding a red textbox with the same dimensions and position as the PlotArea
With PPTShape.Chart.Shapes.AddTextbox(msoTextOrientationDownward, 95, 20, PlotWidth, PlotHeight)
    .Line.Weight = 2
    .Line.DashStyle = msoLineLongDash
    .Line.ForeColor.RGB = RGB(255, 0, 0)
End With

'Quit
Set pptWorkSheet = Nothing
pptWorkBook.Application.Quit
Set pptWorkBook = Nothing
Set PPTChartData = Nothing
Set PPTChart = Nothing

'Screen updates
Application.ScreenUpdating = True


End Sub

Below you can see the result with dummy data. The red box is correct, the PlotArea is the right size but not in the right position. Am I misunderstanding something regarding the InsideLeft vs Left properties? I've been stuck here for hours now and I am not making any progress. A theory a colleague and I have is that the PlotArea is doing a Snap-To to something that can't be seen.

Any help is appreciated!

UPDATE: I changed the order of positioning and sizing of the PlotArea and it improved.

'Set PlotArea position and size
With PPTShape.Chart.PlotArea
    .InsideWidth = PlotWidth
    .InsideHeight = PlotHeight
    .InsideLeft = 95
    .InsideTop = 20
End With

The offset from the red box seems consistent and I'm sure it is a small thing I am missing somewhere. See attached image of the new result below.

UPDATE 2: Here is how I solved this. I'm not entirely sure it is correct logic, but it works at least.

I need to offset the PlotArea by 3.9 points. This seems to involve spacing for TickMarks. My assumption here is that the PlotArea position (.InsideTop and .InsideLeft etc) include TickMark width and height but lacks the means to adjust for this. My workaround looks like this:

    'Set the TickMark offset constant
    offSet = 3.9

    'Set PlotArea position and size
    With theShape.Chart.PlotArea
        .InsideWidth = PlotWidth
        .InsideHeight = PlotHeight
        .InsideLeft = 95 - offSet
        .InsideTop = 20 - offSet
    End With

As this is mostly guesswork, as far as a solution is concerned, any real answers and not workarounds would still be appreciated.

Answer 1

It seems you're trying to position the chart, not the plot area. Try something like this instead:

'Set PlotArea size and position
With PPTShape.Chart.PlotArea
    .InsideWidth = PlotWidth
    .InsideHeight = PlotHeight
    .Left = 60
    .Top = -25
End With