vba 创建图表时参考第一张表 1（以索引形式）

Reference the first sheet 1 (in index form) when creating a chart

提问by Amanda

I'm trying to create a macro to rearrange and graph data.

我正在尝试创建一个宏来重新排列和绘制数据。

I will be applying it to thousands of files that have different tab names based on the file name. I figured out that one may reference the tab via its 'Index Number' and that has worked when sorting the data.

我将根据文件名将其应用于具有不同选项卡名称的数千个文件。我发现人们可以通过它的“索引号”来引用该选项卡，并且在对数据进行排序时已经起作用了。

Now that I am trying to create a line graph, I do not know how to reference the tab name. I'm trying to select the range. I changed it back to 'Sheet1' and was planning on changing the tab name of each file to 'Sheet1' before I learned of 'Index Number'.

现在我正在尝试创建一个折线图，我不知道如何引用选项卡名称。我正在尝试选择范围。我将其改回“Sheet1”，并计划在我了解“索引号”之前将每个文件的标签名称更改为“Sheet1”。

Chart creating portion of the code:

代码的图表创建部分：

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1'!$Z:$AA")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Sheet1'!$C"
ActiveChart.SeriesCollection(1).Values = "='Sheet1'!$AA:$AA"
ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$Z:$Z"
ActiveChart.ApplyLayout (3)

I'm assuming that I want to replace all of the 'Sheet1's with some sort of Index Number. I tried random combinations of Sheets(1).

我假设我想用某种索引号替换所有的“Sheet1”。我尝试了 Sheets(1) 的随机组合。

回答by John Terry

Amanda,

阿曼达，

I have modified your code to reference the first sheet in a workbook by it's index number. This code will allow you to apply the chart to the very first sheet in your workbook regardless of what it has been named. This is accomplished with sheets(x) where "x" is the sheet you want to reference. The first sheet in your workbook is traditionally "1".

我已修改您的代码以通过索引号引用工作簿中的第一张工作表。此代码将允许您将图表应用到工作簿中的第一个工作表，而不管它的名称如何。这是通过 sheet(x) 完成的，其中“x”是您要引用的工作表。工作簿中的第一个工作表传统上是“1”。

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Sheets(1).Range("Z2:AA25")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = Sheets(1).Range("C1")
ActiveChart.SeriesCollection(1).Values = Sheets(1).Range("AA2:AA25")
ActiveChart.SeriesCollection(1).XValues = Sheets(1).Range("Z2:Z25")
ActiveChart.ApplyLayout (3)

Hope this helps,

希望这可以帮助，

John

约翰

回答by Jean-Fran?ois Corbett

This will work. Your code contained a lot of repetitious keywords; notice that I cleaned it up using the Withstatement. Also everything is specified at the top of the code: where the data is located, etc.

这将起作用。您的代码包含大量重复的关键字；请注意，我使用该With语句对其进行了清理。此外，所有内容都在代码顶部指定：数据所在的位置等。

It is poor practice to rely on the index of a Sheetor SeriesCollection, blindly assuming that it's (1). I guarantee that this will screw things up for you at some point. Better to keep references to these objects, as done below, e.g. Set serMyseries = .SeriesCollection.NewSeriesand then operate on serMyseries-- not on SeriesCollection(1).

依赖 a Sheetor的索引是不好的做法SeriesCollection，盲目地假设它是 (1)。我保证这会在某个时候为你搞砸。最好保留对这些对象的引用，如下所示，例如Set serMyseries = .SeriesCollection.NewSeries，然后操作serMyseries- 而不是 on SeriesCollection(1)。

All the Dimstatements are only required if you have Option Explicitat the top of your code. If you don't use Option Explicit, then those lines can be deleted. (But Option Explicitis good practice.)

Dim仅当您Option Explicit在代码顶部时才需要所有语句。如果您不使用Option Explicit，则可以删除这些行。（但这Option Explicit是很好的做法。）

Dim shtData As Worksheet
Dim chtMyChart As Chart
Dim serMyseries As Series
Dim rngName As Range
Dim rngMyValues As Range
Dim rngMyXValues As Range

' What sheet is the data on?
Set shtData = Worksheets("Sheet1") ' Or whatever it's called on the tab

' Where is the data?
Set rngName = shtData.Range("C1")
Set rngMyValues = shtData.Range("AA2:AA25")
Set rngMyXValues = shtData.Range("Z2:Z25")

With shtData
    .Activate

    ' Add a chart.
    Set chtMyChart = .Shapes.AddChart
    With chtMyChart
        .ChartType = xlLineMarkers
        ' Add the series.
        Set serMyseries = .SeriesCollection.NewSeries
        With serMyseries
            .Name = rngName
            .Values = rngMyValues
            .XValues = rngMyXValues
        End With
        .ApplyLayout (3)
    End With
End With

The following two lines of code were useless (first line creates a series, second line deletes it immediately) so I removed them.

以下两行代码没用（第一行创建一个系列，第二行立即删除它）所以我删除了它们。

ActiveChart.SetSourceData Source:=shtData.Range("Z2:AA25")
ActiveChart.SeriesCollection(1).Delete