使用 VBA 获取图表名称

Get chart name with VBA

提问by L.Dutch - Reinstate Monica

I am setting up a macro to generate a chart. I have recorded a macro while I was generating the sample chart, but now I need to have the macro working independently from the name of the chart (Chart 9in this case)

我正在设置一个宏来生成图表。我在生成示例图表时录制了一个宏，但现在我需要让宏独立于图表名称工作（Chart 9在本例中）

Sheets("statistics").Select
Sheets("statistics").Range("A101:C106").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Range("statistics!$A1:$C6")
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 9").Name = "waterfall"
ActiveChart.Location Where:=xlLocationAsObject, Name:="summary"
ActiveSheet.ChartObjects("waterfall").Activate
ActiveSheet.Shapes("waterfall").IncrementLeft 80
ActiveSheet.Shapes("waterfall").IncrementTop -2200
ActiveSheet.ChartObjects("waterfall").Activate
ActiveSheet.Shapes("waterfall").ScaleWidth 1.6025463692, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("waterfall").ScaleHeight 1.6084106153, msoFalse, msoScaleFromTopLeft
ActiveSheet.ChartObjects("waterfall").Activate
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("waterfall").Activate
ActiveChart.SeriesCollection(1).Select
Selection.Format.Fill.Visible = msoFalse
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Points(6).Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent3
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
End With
ActiveChart.SeriesCollection(2).Points(1).Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent3
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
End With
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
    .Solid
End With
ActiveChart.SeriesCollection(2).Points(5).Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
End With
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 0, 0)
    .Transparency = 0
    .Solid
End With
ActiveChart.SetElement (msoElementDataLabelCenter)
ActiveChart.SeriesCollection(2).Points(1).Select
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(2).Points(1).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.SetElement (msoElementDataLabelCenter)
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleHorizontal)
Selection.Caption = "hrs"
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Left = 7
Selection.Top = 13.028

I have tried

我试过了

Sheets("statistics").Range("A101:C106").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Range("statistics!$A1:$C6")
ActiveChart.ChartArea.Select
Set ThisChart = ActiveChart
ActiveSheet.Shapes(ThisChart).Name = "waterfall"

but it is not working

但它不起作用

回答by Shai Rado

Try the code below, it will loop through all existing ChartObjectsin "statistics" worksheet, and if it finds a chartobject with a name of "Chart 9" it will rename it to "waterfall".

试试下面的代码，它将遍历ChartObjects“统计”工作表中存在的所有内容，如果它找到名称为“图表 9”的图表对象，它将重命名为“瀑布”。

Note: you could use a similar approach to create the chart, without the need to use Select, ActiveSheetand ActiveChart.

注意：您可以使用类似的方法来创建图表，而无需使用Select,ActiveSheet和ActiveChart。

Code

代码

Option Explicit

Sub RenameExistingChart()

Dim ChtObj As ChartObject

For Each ChtObj In Worksheets("statistics").ChartObjects
    If ChtObj.Name = "Chart 9" Then
        ChtObj.Name = "waterfall"
    End If
Next ChtObj

End Sub

Edit 1: create the chart with ChtObj:

编辑 1：创建图表ChtObj：

Set ChtObj = Worksheets("statistics").ChartObjects.Add(Left:=100, Top:=100, _
                                    Width:=100, Height:=100) ' <-- just default settings , modify later    
With ChtObj
    .Chart.ChartType = xlColumnStacked
    .Chart.SetSourceData Source:=range("statistics!$A1:$C6")
    .Name = "waterfall"

    With .Chart.SeriesCollection(2).Format.Fill ' modify fill for series (2)
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent3
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Solid
    End With

    .Chart.SeriesCollection(1).ApplyDataLabels ' add data lables to series (1)

End With

回答by KoderM16

You could use something like this:

你可以使用这样的东西：

Sub ChartStuff()
Dim cht As Shape
Range("A101:A106").Select
ActiveSheet.Shapes.AddChart.Select
Set cht = ActiveSheet.Shapes(1)
cht.Name = "waterfall"
End Sub

Hope this helps!

希望这可以帮助！

回答by FunThomas

Dealing with charts in VBA is a little complicated. When you use Addchart, Selection will be a ChartAreaA ChartAreais part of a Chart which is part of a ChartObjectThe name of a chart you see is in fact the name of the ChartObject

在 VBA 中处理图表有点复杂。当您使用时Addchart，Selection 将是ChartAreaA ChartAreais part of a Chart which is part of aChartObject您看到的图表名称实际上是ChartObject

You can do something like this:

你可以这样做：

Range("A101:A106").Select
ActiveSheet.Shapes.AddChart.Select

Dim ca As ChartArea, ch As Chart, co As ChartObject
Set ca = Selection 
Set ch = ca.Parent
ch.ChartType = xl3DColumn
Set co = ch.Parent
co.Name = "waterfall"
Debug.Print ca.Name, ch.Name, co.Name

回答by Bonny

Create a function that you call from within the sub, that sends the name of the active chart, for example:

创建一个从 sub 中调用的函数，该函数发送活动图表的名称，例如：

Function actchart(ActiveChart As String) As String
  actchart = ActiveChart
End Function

And then from within your sub, you can replace, as an example, where you have:

然后从您的 sub 中，您可以替换，例如，您拥有的位置：

ActiveSheet.Shapes("Chart 9").Name = "waterfall" 

with

和

ActiveSheet.Shapes(actchart(ActiveChart.Parent.Name)).Name = "waterfall"

This worked for me with the same issue! Hope it helps.

这对我有同样的问题！希望能帮助到你。