在 VBA 中使用命名范围制作图表

Making Charts with Named Ranges in VBA

提问by Dedwards

I am trying to write some code that will work through the named ranges I have established already in my Excel Workbook. Each sheet in the workbook has a different layout, which made writing code for the names the lion's share of the work. I have already done that and now would like to write code that will cycle through the names in each sheet and make charts using the named ranges I have already established. I have run into a bunch of errors in the following code and would appreciate some assistance fixing them and making the code more efficient!

我正在尝试编写一些代码来处理我已经在 Excel 工作簿中建立的命名范围。工作簿中的每个工作表都有不同的布局，这使得为名称编写代码成为工作的主要部分。我已经这样做了，现在想编写代码来循环遍历每个工作表中的名称并使用我已经建立的命名范围制作图表。我在以下代码中遇到了一堆错误，希望得到一些帮助来修复它们并使代码更高效！

ChartName = ActiveSheet.Range(n).Offset(0, -6) & " " & ActiveSheet.Range(n).Offset(0, -5)

ChartName = ActiveSheet.Range(n).Offset(0, -6) & " " & ActiveSheet.Range(n).Offset(0, -5)

I get an error in the above line for a type mismatch that I just cannot figure out how to deal with. Also, ActiveSheet.ChartObjects.Adddraws an invalid property assignment!

由于类型不匹配，我在上面的行中收到一个错误，我无法弄清楚如何处理。此外，ActiveSheet.ChartObjects.Add绘制了一个无效的属性分配！

Sub WEO_DevCharts()

Sheets("WEO").Activate

Dim objChart As ChartObject
Dim n As Name
Dim ChartName As String

For Each n In ActiveSheet.Names

    If n.Name <> "DateRange" Then

    ChartName = ActiveSheet.Range(n).Offset(0, -6) & " " & ActiveSheet.Range(n).Offset(0, -5)

        Set objChart = ActiveSheet.ChartObjects.Add

        With objChart.Chart
            .chartType = xlXYScatterLines
            .SeriesCollection.Values = n.Value
            .SeriesCollection.XValues = ActiveSheet.Range("DateRange").Value
            .SeriesCollection.Name = ChartName
            .legend.Delete
        End With
    End If
    Next n
    End Sub

回答by brettdj

I have made a number of changes below

我在下面做了一些改变

(Updated to handle two separate ranges on the sheet, a range called DateRangeand one called Test. Both ranges are local to the sheet to satisfy the ActiveSheettest, else they would belong to the ActiveWorkbook{but DateRangecan be global or local and wil still graph fine either way})

（更新处理在片材上的两个独立的范围内，所谓的范围DateRange和一个叫Test。这两个范围是本地的片材，以满足ActiveSheet测试，否则会属于ActiveWorkbook{但DateRange可以是全局的或局部的和WIL仍然图形精细无论哪种方式})

1. Rather than ActiveSheet.Range(n).Offset(0, -6)you need a single cell, probably the first in the nrange, ie Range(n).Cells(1).Offset(0, -6)(plus this means your title lies 6 columns to the left of your range name - ensure there is space)
2. Try adding your chart like ActiveSheet.ChartObjects.Add(Left:=500, Width:=300, Top:=50, Height:=400)
3. Once you have set the xy type you can set your sourcerange directly to your range name nthen add the DateRangerange as the x series
4. Add your series name to the first series, ie .SeriesCollection(1).Name = ChartNamenot .SeriesCollection.Name = ChartName
5. I have used InStr(n.Name, "DateRange") = 0rather than your n.Name <> "DateRange"to avoid processing any local name versions of DateRange ie WEO!DateRange

1. 而不是ActiveSheet.Range(n).Offset(0, -6)您需要一个单元格，可能是n范围中的第一个单元格，即Range(n).Cells(1).Offset(0, -6)（另外，这意味着您的标题位于范围名称左侧的 6 列 - 确保有空格）
2. 尝试添加您的图表，例如 ActiveSheet.ChartObjects.Add(Left:=500, Width:=300, Top:=50, Height:=400)
3. 设置 xy 类型后，您可以将 sourcerange 直接设置为范围名称，n然后将DateRange范围添加为 x 系列
4. 将您的系列名称添加到第一个系列，即.SeriesCollection(1).Name = ChartName不是.SeriesCollection.Name = ChartName
5. 我使用InStr(n.Name, "DateRange") = 0而不是你n.Name <> "DateRange"来避免处理 DateRange 的任何本地名称版本，即WEO!DateRange

I suggest you look at Jon Peltier's excellent sitefor further detailed code examples

我建议您查看Jon Peltier 的优秀站点以获取更详细的代码示例

Sample screenshot of my test code for a local sheet name

我的本地工作表名称的测试代码的示例屏幕截图

• Testin H10:H13
• DateRangein D14:D17

• Test在 H10:H13
• DateRange在 D14:D17

    Sub WEO_DevCharts()
    Sheets("WEO").Activate
    Dim objChart As ChartObject
    Dim n As Name
    Dim ChartName As String
    For Each n In ActiveSheet.Names
        If InStr(n.Name, "DateRange") = 0 Then
            ChartName = Range(n).Cells(1).Offset(0, -6) & " " & Range(n).Cells(1).Offset(0, -5)
            Set objChart = ActiveSheet.ChartObjects.Add(Left:=500, Width:=300, Top:=50, Height:=400)
            With objChart.Chart
                .ChartType = xlXYScatterLines
                .SetSourceData Range(n)
                .SeriesCollection(1).XValues = Range("DateRange")
                .SeriesCollection(1).Name = ChartName
                .Legend.Delete
            End With
        End If
    Next n
End Sub

回答by chris neilsen

Instead of ActiveSheet.Range(n)or n.Value, use n.RefersToRangeto get the range the name refers to

而不是ActiveSheet.Range(n)or n.Value，用于n.RefersToRange获取名称所指的范围