How to assign XValues for excel chart using VBA

Question

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

I call the function this way:

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)

And I receive an error:

Runtime Error '1004':

Invalid Parameter

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

In the documentation is written:

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

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

Did you try it without Set? I've seen Set used to define a variable with an object, but never the other way around. — Chrismas007
– Chrismas007, Commented Jan 7, 2015 at 14:59
What is the type of XValues? I think @Chrismas007 is on to something. — RubberDuck
– RubberDuck, Commented Jan 7, 2015 at 15:07
Does your chart have a SeriesCollection added already? If not, you will need to add one like this MyChartSheet_1.SeriesCollection.NewSeries. The NewSeries method adds a series everytime you call it. — laylarenee
– laylarenee, Commented Jan 7, 2015 at 15:07
@Chrismas007 I have tried without Set but it gives the same error — vladiz
– vladiz, Commented Jan 7, 2015 at 16:03
@laylarenee exactly this was the problem. Thank you, you can add it as an answer. — vladiz
– vladiz, Commented Jan 7, 2015 at 16:05

Jon Peltier · Accepted Answer · 2016-06-26 21:03:58Z

8

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

edited Jun 26, 2016 at 21:03

Jon Peltier

6,1011 gold badge32 silver badges28 bronze badges

answered Jan 7, 2015 at 16:14

laylarenee

3,2947 gold badges35 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jon Peltier Over a year ago

Also, you don't use Set, you just assign the values with an equals sign. @laylarenee did this but I think it must have attention drawn to it.

Thomas Hartl · Accepted Answer · 2016-06-23 18:05:55Z

-1

You cannot use named ranges for .XValues or .Values, but you can change the .Formula property by replacing the series formula

For more information on editing the series formula see http://peltiertech.com/change-series-formula-improved-routines/

Note of caution, there is a bug in Excel 2013 that prevents you from using named ranges starting with "R" or "C" when using VBA to change the series formula; see http://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/named-range-use-in-chart-series-formulas-causes/c5a40317-c33f-4a83-84db-0eeee5c8827f/?auth=1&rtAction=1466703182593

answered Jun 23, 2016 at 18:05

Thomas Hartl

1

2 Comments

Jon Peltier Over a year ago

You can indeed use named ranges for .XValues or .Values.

Jon Peltier Over a year ago

Also, while you can't include names beginning with "R" or "C" in the series formula (manually or in VBA), you can include them using .Values = or .XValues = (manually in the Select Data dialog or in VBA).

Collectives™ on Stack Overflow

How to assign XValues for excel chart using VBA

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related