title: How to adjust chart size to fit page size using Excel VBA?
body: <p>I'm trying to adjust the size of a chart in Excel to exactly fit the printable area, e.g. the chart should cover the whole A4 page <strong><em>except the margin area</em></strong>, i.e. it should cover the area of <code>(A4 height - top and bottom margins) x (A4 width - left and right margins)</code>. I have tried the following code but it turned out that the height of the chart is very close to but still not quite the same as (A4 height - top and bottom margins), whereas the width is about 1 cell wider than (A4 width - left and right margins). </p>
  
  <pre><code>Private Sub Worksheet_Activate()
      Dim sh As Worksheet
      Dim objChartShape As Chart
  
      Set sh = ActiveSheet
      If sh.ChartObjects.Count &lt;&gt; 0 Then
          sh.ChartObjects.Delete
      End If
      Set objChartShape = sh.Shapes.AddChart.Chart
  
      Dim w, h As Long
      w = Application.CentimetersToPoints(21#) ' A4 width in cm
      h = Application.CentimetersToPoints(29.7) ' A4 height in cm
      w = w - sh.PageSetup.LeftMargin - sh.PageSetup.RightMargin
      h = h - sh.PageSetup.TopMargin - sh.PageSetup.BottomMargin
      With objChartShape
          .Parent.Left = 0
          .Parent.Top = 0
          .Parent.Width = w
          .Parent.Height = h
      End With
  End Sub
  </code></pre>
  
  <p>The above code create an empty chart when the sheet is activated. You will see that the chart is not high enough to reach the top of the footer area and it is too wide to fit within a single page.</p>
  
  <p>Any help will be greatly appreciated, thanks in advance!</p>