CODEWITHSUNDEEP
vbaexcelcharts
james
james

Reputation: 1165

How to adjust chart size to fit page size using Excel VBA?

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 except the margin area, i.e. it should cover the area of (A4 height - top and bottom margins) x (A4 width - left and right margins). 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). 

Private Sub Worksheet_Activate()
    Dim sh As Worksheet
    Dim objChartShape As Chart

    Set sh = ActiveSheet
    If sh.ChartObjects.Count <> 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

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.

Any help will be greatly appreciated, thanks in advance!

Upvotes: 2

Views: 6458

Answers (1)

Dave.Gugg
Dave.Gugg

Reputation: 6781

Possibly you need to also account for the header and footer margin?:

Private Sub Worksheet_Activate()
    Dim sh As Worksheet
    Dim objChartShape As Chart

    Set sh = ActiveSheet
    If sh.ChartObjects.Count <> 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 - sh.PageSetup.HeaderMargin - sh.PageSetup.FooterMargin
    With objChartShape
        .Parent.Left = 0
        .Parent.Top = 0
        .Parent.Width = w
        .Parent.Height = h
    End With
End Sub

Upvotes: 0

Related Questions