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

NWalters

New Member
Joined
Jan 24, 2005
Messages
5
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

NWalters

New Member
Joined
Jan 24, 2005
Messages
5
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021
Can you edit your code to look something like this --
Code:
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

New Member
Joined
Jan 24, 2005
Messages
5
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.
:oops:
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021

ADVERTISEMENT

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:
Code:
...
        .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

New Member
Joined
Jan 24, 2005
Messages
5
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021
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 (y) to Jon for his help.
 

Forum statistics

Threads
1,148,526
Messages
5,747,211
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top