Creating a chart in VBA using the Cells method -- Application or Object Defined Error

Question

I'm attempting to create a chart using VBA in Excel 2010. I'm attempting to select data from another sheet in the same workbook. The data, however, is horizontal (and has to be kept that way), so I need to use the Cells() function to select the data for the chart.

However, it doesn't seem to be working, and I can't really find out why. I keep getting the error "Application-Defined or Object-Defined error" on any line that uses the Cells() function to define a range. However, if Cells() is used to reference a single cell, it works fine.

Can Range(Cells(x, y), Cells(z, w)) not be used in this case?

Here is the relevant code:

BinNumber = 2048
Worksheets("Graph One").Activate
Range("A1").Select

'Setting the range the chart will cover
Set rngChart = ActiveSheet.Range("H2:U26")
'Dimensioning the chart and choosing chart type
Set co = ActiveSheet.Shapes.AddChart(xlXYScatter, rngChart.Cells(1).Left, rngChart.Cells(1).Top, rngChart.Width, rngChart.Height)
Set cht = co.Chart
Set sc = cht.SeriesCollection

'Remove any default series
Do While sc.Count > 0
    sc(1).Delete
Loop

'Setting chart data

'Graphing Data
With cht.SeriesCollection.NewSeries
    .Name = Worksheets("Transposed Data").Cells(3, 1)
    .XValues = Worksheets("Transposed Data").Range(Cells(2, 5), Cells(2, BinNumber + 4))
    .Values = Worksheets("Transposed Data").Range(Cells(3, 5), Cells(3, BinNumber + 4))
    .MarkerSize = 4
    .MarkerStyle = xlMarkerStyleCircle

End With

'Setting chart labels
With cht
    .HasTitle = True
    .ChartTitle.Characters.Text = "Counts per energy level"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Energy (keV)"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Counts"
    .Axes(xlCategory).HasMajorGridlines = True
    .Axes(xlCategory).HasMinorGridlines = True
End With

The code stops when it tries to set the x and y values under the Graphing Data header.

Just to single it out, I'll include it again below.

'Graphing Data
With cht.SeriesCollection.NewSeries
    .Name = Worksheets("Transposed Data").Cells(3, 1)
    .XValues = Worksheets("Transposed Data").Range(Cells(2, 5), Cells(2, BinNumber + 4))
    .Values = Worksheets("Transposed Data").Range(Cells(3, 5), Cells(3, BinNumber + 4))
    .MarkerSize = 4
    .MarkerStyle = xlMarkerStyleCircle

The data for the x-values is in range E2 to BZX2 and the data for the y-values is in range E3 to BZX3. Cell A3 just has the title for the chart.

What if you set `Worksheets("Transposed Data").Range(Cells(2, 5), Cells(2, BinNumber + 4))` to a range variable then use the variable to add it to the chart? Troubleshooting here. Not sure it'll actually work. — guitarthrower, Jul 18 '14 at 19:21
I got a different error. It says "Object Variable or With block variable not set". I'll add what I tried above. — TheTreeMan, Jul 18 '14 at 19:29

score 4 · Accepted Answer · edited May 23 '17 at 12:13

However, if Cells() is used to reference a single cell, it works fine.

This is not exactly true.

The problem is that Cells(2, 5) always refers to the active worksheet, unless it is fully qualified. So in this line, the Cells method is qualified to an explicit worksheet reference, and no error should occur. This might be what you were seeing that it is a "single cell", but the distinction here is that it's fully qualified.

.Name = Worksheets("Transposed Data").Cells(3, 1)

This line will fail unless the "Transposed Data" sheet is active (not recommended) or you fully qualify the range.

.XValues = Worksheets("Transposed Data").Range(Cells(2, 5), Cells(2, BinNumber + 4))

This is equivalent to:

.XValues = Worksheets("Transposed Data").Range(ActiveSheet.Cells(2, 5), ActiveSheet.Cells(2, BinNumber + 4))

When we put it that way, it becomes more clearly a potential for error :)

Here is your code, restructured slightly. I will use a With block for the worksheet itself, and create a variable to represent the series:

Dim srs as Series
Set srs = cht.SeriesCollection.NewSeries

With Worksheets("Transposed Data")
    srs.Name = .Cells(3, 1)
    srs.XValues = .Range(.Cells(2, 5), .Cells(2, BinNumber + 4))
    srs.Values = .Range(.Cells(3, 5), .Cells(3, BinNumber + 4))
    srs.MarkerSize = 4
    srs.MarkerStyle = xlMarkerStyleCircle
End With

So note the . preceding .Range and .Cells(... this makes these methods relate to the With Worksheets("Transposed Data").

This worked! Fantastic. Thank you so much for your help! I'm not sure I would've figured that out about the Cells() function without you. I like that you actually explained why it failed, instead of just giving me the answer. I appreciate it! — TheTreeMan, Jul 18 '14 at 19:35

Creating a chart in VBA using the Cells method -- Application or Object Defined Error

1 Answers1