如何使用Excel VBA调整图表大小以适应页面大小？ [英] How to adjust chart size to fit page size using Excel VBA?

问题描述

我试图在Excel中调整图表的大小，使其完全符合可打印区域。图表应该覆盖整个A4页 （边距区域 ），即应覆盖（A4高度 - 上边距和下边距） （A4宽度 - 左右页边距）。我试过下面的代码，但事实证明，图表的高度非常接近，但仍然不完全相同（A4高度 - 顶部和底部边距），而宽度大约1个单元格比（A4宽度 - 左和右边距）。

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.

任何帮助都会很大

推荐答案

可能你还需要考虑页眉和页脚边距：

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

这篇关于如何使用Excel VBA调整图表大小以适应页面大小？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！