VBA：格式化多个选定的图表（图表，图解，图例等） [英] VBA: Formatting Multiple Selected Charts (Chart, Plot, Legend, etc.)

问题描述

我正在寻找使用VBA在Excel 2010上格式化多个选定图表的格式。无论选择一个还是多个图表，我都希望代码能够正常工作。当仅选择一个图表但选择多个图表时，下面的代码有效，我收到运行时错误'91'对象变量或未设置带块变量的信息。知道如何为所选图表的数量运行宏吗？

I am looking to format multiple selected charts on Excel 2010 using VBA. I want the code to work whether I choose one or multiple charts. The code below works when only one chart is selected but when multiple charts are selected, I get a "run-time error '91' Object variable or With Block variable not set". Any idea how to run the macro for number of selected charts?

Sub ChartFormat5_Click()


''Adjust chart area
ActiveChart.ChartArea.Select

'Size
Selection.Width = 631.9
Selection.Height = 290.1

'Border
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
    .Weight = 1
    .DashStyle = msoLineSolid
End With

'Font
With Selection.Format.TextFrame2.TextRange.Font
    .Name = "Calibri"
    .Size = 10
    .Fill.Visible = msoTrue
    .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
    .Fill.ForeColor.TintAndShade = 0
    .Fill.ForeColor.Brightness = 0
    .Fill.Transparency = 0
    .Fill.Solid
End With


''Adjust axis alignment and format

ActiveChart.Axes(xlCategory).Select
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
End With
ActiveChart.Axes(xlCategory).TickLabelSpacing = 1
ActiveChart.Axes(xlCategory).TickLabels.Orientation = 45

ActiveChart.Axes(xlValue).Select
Selection.TickLabels.NumberFormat = "#,##0_);(#,##0)"
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
End With

ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Left = 1.5
Selection.Format.Line.Visible = msoFalse


''Adjust legend box

ActiveChart.Legend.Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 255, 255)
    .Transparency = 0
    .Solid
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = -0.5
    .Transparency = 0
End With
Selection.Left = 124
Selection.Top = 67

''Adjust plot area size and format
ActiveChart.PlotArea.Select

'Borders
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
    .Weight = 0.75
    .DashStyle = msoLineSolid
End With

'Size
Selection.Width = ActiveChart.ChartArea.Width - 30.4
Selection.Height = ActiveChart.ChartArea.Height - 8.5
Selection.Top = 4
Selection.Left = 20

'Gridlines
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
End With
With Selection.Format.Line
    .Visible = msoTrue
    .DashStyle = msoLineDash
End With


End Sub

推荐答案

处理活动图表或所有选定图表。第一个例程确定要处理的内容（活动图表或选定的图表），第二个例程分别确定。

This will process the active chart or all selected charts. The first routine determines what to process (active chart or selected charts) and the second processes each.

Sub FormatCharts()
  Dim obj As Object

  If Not ActiveChart Is Nothing Then
    FormatOneChart ActiveChart
  Else
    For Each obj In Selection
      If TypeName(obj) = "ChartObject" Then
        FormatOneChart obj.Chart
      End If
    Next
  End If
End Sub

Sub FormatOneChart(cht As Chart)
  ' do all your formatting here, based on cht not on ActiveChart
End Sub

不要选择图表的各个部分，只需完全参考它们即可。代替

Don't select parts of the chart, just fully reference them. Instead of

ActiveChart.ChartArea.Select
With Selection.Format.Line

使用此

With cht.ChartArea.Format.Line

等。

这篇关于VBA：格式化多个选定的图表（图表，图解，图例等）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！