如何使用VBA为Excel图表分配XValues [英] How to assign XValues for excel chart using VBA

问题描述

我具有用于在Excel 2013中绘制图表的VBA功能:

I have this VBA function for drawing charts in Excel 2013:

Sub DrawChart2(obj_worksheetTgt As Worksheet, ByVal XLabels As Range, ByVal DataValues As Range, ByVal chartTitle As String, a As Integer, b As Integer)
'
'obj_worksheetTgt   - Object worksheet on which to be placed the chart
'XLabels            - Data range for X labels
'DataValues         - Data range for Y values
'chartTitle         - Chart title
'a                  - left border position of chart in pixels
'b                  - top border position of chart in pixels


With obj_worksheetTgt.ChartObjects.Add(a, b, 900, 300) ' Left, Top, Width, Height
    With .Chart
        .ChartType = xlBarClustered  
        Set .SeriesCollection(1).XValues = XLabels ' Here is the error
        Set .SeriesCollection(1).Values = DataValues
        .Legend.Position = -4107
        .HasTitle = True
        .chartTitle.Text = chartTitle
        .chartTitle.Font.Size = 12
        With .Axes(1).TickLabels
            .Font.Size = 8
            .Orientation = 90
        End With
    End With
End With
End Sub

我这样调用函数:

ChartsWorksheet = "Summary"
Queryname = "query1"
chartTitle = "Values"
With .Worksheets("LastDayData").ListObjects(Queryname)
    Set chart_labels = .ListColumns(2).DataBodyRange
    Set chart_values = .ListColumns(6).DataBodyRange
End With
Call DrawChart2(.Worksheets(ChartsWorksheet), chart_labels, chart_values, chartTitle, 10, 10)

我收到一个错误:

运行时错误'1004':

Runtime Error '1004':

无效的参数

当我单击debug时，它将在上面的函数中标记"Set .SeriesCollection(1).XValues = XLabels"行.

When I click debug it marks the row "Set .SeriesCollection(1).XValues = XLabels" in the function above.

在文档中写道:

可以将XValues属性设置为工作表上的范围或 值数组，但不能是两者的组合

The XValues property can be set to a range on a worksheet or to an array of values, but it cannot be a combination of both

因此它应该能够将给定范围用作XValues的值，但我不明白为什么会出现此错误.

So it should be able to take the given range as values for XValues, but I can't understand why this error appears.

推荐答案

在您可以设置系列的值和XValue之前，需要先添加系列.使用SeriesCollection.NewSeries方法很容易做到，如下所示:

Before you can set the Values and XValues of a series, you will need to add the series first. This is simple to do using the SeriesCollection.NewSeries method as show below:

With ActiveSheet.ChartObjects.Add(a, b, 900, 300) ' Left, Top, Width, Height
    With .Chart
        .ChartType = xlBarClustered

        ' need to add the series before you can assign the values/xvalues
        ' calling the "NewSeries" method add one series each time you call it.

        .SeriesCollection.NewSeries

        ' now that the series is added, you may assign (not set) the values/xvalues

        .SeriesCollection(1).XValues = XLabels
        .SeriesCollection(1).Values = DataValues
    End With
End With

这篇关于如何使用VBA为Excel图表分配XValues的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！