A Newbie Asks: Why doesn't this work??

[NWalters]

I'm a fairly new user of VB. I'm trying to pull together some code that will automatically add a new series to a bubble graph. The graph references a data table on another worksheet. The new series should be the last row of that table - which is created by an eariler macro. But. I can't get the new series to look at the right cells. Any ideas much appreciated.

This is the code I'm using:

Dim intSeriesCount As Integer
Dim strName As String
Dim xValue As String
Dim yValue As String
Dim bubble

'The sheet "Summary Assessment Scores" being where the data table is. the Range "HiddenSummary" is the cell at the left top corner of the data table.

Sheets("Summary Assessment Scores").Select
Range("HiddenSummary").End(xlDown).Select

strName = ActiveCell.Address
xValue = ActiveCell.Offset(0, 2).Address
yValue = ActiveCell.Offset(0, 1).Address
bubble = ActiveCell.Offset(0, 3).Address

'The worksheet "Country Comparison" being where the graph is stored

Sheets("Country Comparison").Select
ActiveChart.SeriesCollection.NewSeries
intSeriesCount = ActiveChart.SeriesCollection.Count



ActiveChart.SeriesCollection(intSeriesCount).xValue = "='Summary Assessment Scores'!" & CStr(xValue)
ActiveChart.SeriesCollection(intSeriesCount).Value = "='Summary Assessment Scores'!" & CStr(yValue)
ActiveChart.SeriesCollection(intSeriesCount).Name = "='Summary Assessment Scores'!" & CStr(strName)
ActiveChart.SeriesCollection(intSeriesCount).BubbleSizes = "='Summary Assessment Scores'!" & CStr(bubble)
ActiveChart.ChartType = xlBubble

 

[NWalters]

The error message I get by the way is:

"Run time error 438

Object doesn't support this property or method."

Don't know if this helps anyone. Thanks

 

[Greg Truby]

Can you edit your code to look something like this --

Sub AddBubbleSeries()
    '
    ' Add new series, color it dark green
    '
        With ActiveChart.SeriesCollection.NewSeries
            .Name = "=""Second Series"""
            .XValues = ActiveSheet.Range("C42:C48")
            .Values = ActiveSheet.Range("D42:D48")
            .BubbleSizes = ActiveSheet.Range("E42:E48")
            With .Interior
                .ColorIndex = 10
            End With
        End With
    End Sub

and see if that will work for you? This is taken from a recommendation found on Jon Peltier's website. http://www.peltiertech.com/Excel/ChartsHowTo/HowToBubble.html

And I should tell you that I will be impressed if this works for you because I could not get this approach to work for me. I ended up using a class module to solve the problem.

 

[NWalters]

Thanks for the reply. Tried it looking like this:

Sheets("Country Comparison").Select
ActiveChart.SeriesCollection.NewSeries
With ActiveChart.SeriesCollection.NewSeries
.Name = strName
.XValues = "=""='Summary Assessment Scores'!""" & Cstr(xValue)

And I get an error message saying that VB can't set XValue. The problem is that the coding you suggest uses hard cell references. I need to set the Xvalue as being the last cell in a list and I can't work out how to do it.

 

[Greg Truby]

All you need to do is figure out which cell is your last one and then just use that. For example, let's say that your X values are in column C and your Y values are in column E...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Bubblies()
    <SPAN style="color:#00007F">Dim</SPAN> rngLastXCell <SPAN style="color:#00007F">As</SPAN> Range, rngLastYCell <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Set</SPAN> rngLastXCell = [C65536].End(xlUp)
    <SPAN style="color:#00007F">Set</SPAN> rngLastYCell = [E65536].End(xlUp)
    Sheets("Country Comparison").Select
    <SPAN style="color:#00007F">With</SPAN> ActiveChart.SeriesCollection.NewSeries
        .Name = strName
        .XValues = rngLastXCell
        .Values = rngLastYCell
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Theoretically setting bubble size would amount to trying the same thing. Let's say bubbles are in column D. Then one would expect the following.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Bubblies()
    <SPAN style="color:#00007F">Dim</SPAN> rngLastXCell <SPAN style="color:#00007F">As</SPAN> Range, rngLastYCell <SPAN style="color:#00007F">As</SPAN> Range, _
        rngLastBCell <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Set</SPAN> rngLastXCell = [C65536].End(xlUp)
    <SPAN style="color:#00007F">Set</SPAN> rngLastYCell = [E65536].End(xlUp)
    <SPAN style="color:#00007F">Set</SPAN> rngLastBCell = [D65536].End(xlUp)
    Sheets("Country Comparison").Select
    <SPAN style="color:#00007F">With</SPAN> ActiveChart.SeriesCollection.NewSeries
        .Name = strName
        .XValues = rngLastXCell
        .Values = rngLastYCell
        .BubbleSizes = rngLastBCell
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

However I cannot get this approach to work. So one could try passing a string / address instead:

...
        .BubbleSizes = rngLastBCell.Address
...

But at least, for me, this blows up in my face too. So if you can get either approach to work for you, let us all know. Because like I said above, currently I've resorted to using a class module to solve this problem.

 

[NWalters]

It works! It works! Ha!

Thank you very much Greg Truby! The number of man hours I've wasted so far on this are just daft. You've made my day.

(Fortunately didn't have to worry about the bubble sizes as bubbles in my graph are constant.)

 

[Greg Truby]

For anyone reading this - I wrote to Jon Peltier about setting bubble size and he was kind enough to re-visit the issue. He has posted an update to the part of his page (see link in my previous post) where he discusses how to change the .BubbleSizes property via VBA. Essentially, you need to pass the address, but be sure to concatenate an equals sign in front of the address -- that's the part that eluded me. (It's always the details, ain't it.) So at least from me, a very large Thank You to Jon for his help.