Align PlotArea with Range

[RJD1986]

Hello all

My question is about the PlotArea. I want set the PlotArea to a certain range (See pic below). So if the S-Curve period is 11 days, 11 columns need to be divided over the PlotArea.InsideWidth. If the S-Curve period is 40 days, 40 columns need to be divided over the PlotArea.InsideWidth. I want this, so I can put a custum data table in the cells under the PlotArea. This way the data in the cells en de data in the graph are aligned. In the picture below (done by hand) I used snap on grid and the PlotArea alignes with the range I want. Only problem is, I want to do this in VBA.

I want the PlotArea.Insidewith to be 1350 (or around that) al the time, only the column.width differs.

10 columns = 1350
15 columns = 1350
44 columns = 1350

For Example

15 columns of 20 width (cell.width = 108.75)
If I select the 15 columns and use Selection.Width I get 1631.25 (15 x 108.75)
If I select the PlotArea and use ActiveChart.PlotArea.InsideWidth I get 1636.25

Even though I used snap to grid! So it doesn't seem to correspond with eachother.

Long story short, I'm a bit lost in al this. I hope I made myself clear. Thanks in advance!

 

[RJD1986]

No thoughts at all?

 

[RJD1986]

I've know looked up the values corresponding to 10/15/20/25/etc. columns. It's a work around, but it works for now.

I still hope some Excel guru has the answer for me on how to devide a certain amound of columns over the PlotArea.

 

[RJD1986]

Can someone please tell me if the above is possible?

 

[RJD1986]

Bump!

 

[Jon Peltier]

What you need to do is align the plot area inside the chart with the range outside the chart. It's a bit tricky because you can modify the plot area's dimensions, but these are larger than the plotting rectangle, because they allow room for axis labels. However, you can measure the plotting rectangle's dimensions using PlotArea.InsideLeft, .InsideWidth, etc.

Here's some code that will align the plot area. I've written the algebra in comments.

Sub AlignPlotAreaLeftAndWidthToSheetColumns()
  Dim i As Long
  Dim cht As Chart
  Dim rng As Range
  Dim shp As Shape
  ' range dimensions
  Dim dRngLeft As Double
  Dim dRngWidth As Double
  ' chart position
  Dim dChtLeft As Double
  ' plot area outside dimensions
  Dim dPltLeft As Double
  Dim dPltWidth As Double
  ' plot area inside dimensions
  Dim dPltInsideLeft As Double
  Dim dPltInsideWidth As Double
  ' margin between plot area inside and outside
  Dim dPltMarginLeft As Double
  Dim dPltMarginWidth As Double
  Dim dPltChtOffset As Double

  Set cht = ActiveChart
  Set rng = ActiveSheet.Range("H:L")

  dRngLeft = rng.Left
  dRngWidth = rng.Width

  dChtLeft = cht.ChartArea.Left
  
  ' there's an offset between the left edge of the chart and .left=0
  ' move plot area left as far as possible, it stops at edge of chart
  ' offset is PlotArea.Left
  With cht.PlotArea
    .Left = -dChtLeft
    dPltChtOffset = -.Left
  End With

  ' do it twice
  For i = 1 To 2
    With cht.PlotArea
      dPltLeft = .Left
      dPltWidth = .Width
      dPltInsideLeft = .InsideLeft
      dPltInsideWidth = .InsideWidth
    End With

    dPltMarginLeft = dPltInsideLeft - dPltLeft
    dPltMarginWidth = dPltWidth - dPltInsideWidth

    ' temporarliy shrink plot area, make sure there's room to move it as far right as specified
    cht.PlotArea.Width = cht.PlotArea.Width / 2

    ' plotarea.insideleft = chartarea.left + plotarea.left + plotarea-left-margin + plotarea-offset = range.left
    ' plotarea.left = range.left - chartarea.left - plotarea-left-margin - plotarea-offset
    cht.PlotArea.Left = dRngLeft - dChtLeft - dPltMarginLeft - dPltChtOffset

    ' plotarea.insidewidth = plotarea.width - plotarea-width-margin = range.width
    ' plotarea.width = range.width + plotarea-width-margin
    cht.PlotArea.Width = dRngWidth + dPltMarginWidth
  Next i

End Sub