Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

VBA and ChartType xlBubble

268 views
Skip to first unread message

Tim

unread,
Jan 15, 2003, 6:18:58 PM1/15/03
to
Hello,
I have successfully automated chart generation in Excel
2000 with VBA using many XYScatter chart types, but am now
having trouble with the Bubble type.

Using the code in my VBA macro:

Charts.Add
ActiveChart.ChartType = xlBubble

The macro hangs at the second line and I get the error:
Run-time error '1004':
Method 'ChartType' of object '_Chart' failed.

Has anyone else had this same problem? Why won't the
bubble chart work?

Thanks,
Tim

Jon Peltier

unread,
Jan 15, 2003, 9:17:46 PM1/15/03
to
Tim -

The macro recorder doesn't always create code that works efficiently, and
sometimes the code doesn't work at all. The bubble chart is one of these
cases.

You probably have an ActiveChart.SetSourceData statement in your code. Put
this line above the ActiveChart.ChartType = xlBubble, and you will get your
bubble chart. Make sure the SetSourceData includes a range with three
columns, X, Y, and bubblesize, or else it will hang anyway.

The following makes a bubble chart on the active sheet using data in the
seected range.

Sub RegularBubbleChart()
Dim oChart As ChartObject
Dim rngData As Range

Set rngData = Selection
Set oChart = ActiveSheet.ChartObjects.Add(100, 100, 350, 225)
With oChart.Chart
.SetSourceData Source:=rngData
.ChartType = xlBubble
End With

End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <548b01c2bcec$7b0564d0$d5f82ecf@TK2MSFTNGXA12>,
tim.o...@twdb.state.tx.us says...

Tim

unread,
Jan 16, 2003, 9:31:43 AM1/16/03
to
Jon,
Thanks so much for the reply...but neither methods you
suggested have worked. I keep getting the same error as
soon as the < .ChartType = xlBubble > statement is
issued. This script is rather long and generates three XY
charts prior to starting the bubble chart...although the
bubble chart code doesn't work when in its own module by
itself either.

Does anyone have any more suggestions?? Frustrating.

Tim

>.
>

Andy Pope

unread,
Jan 16, 2003, 11:37:09 AM1/16/03
to
Tim,

Jon's example code worked for me (using XL2000, WK2000).

Have you tried it?
Did you have a valid range of cells selected when you ran it?

Tim wrote:

> >.
> >


--

Cheers
Andy

http://www.geocities.com/andy_j_pope/index.html

Tim

unread,
Jan 16, 2003, 2:39:03 PM1/16/03
to
Yeah, no dice. I'm using Excel 2000 9.0.38211 SR-1 if it
makes any difference. Do I need any particular toolpaks
enabled? I'm not using them all.

Tim

>.
>

Andy Pope

unread,
Jan 16, 2003, 3:27:49 PM1/16/03
to
Tim,

Shouldn't need any special paks.

If you want to email the workbook I can take a look.

Use this account andy_...@yahoo.co.uk as I am not work.
Please don't attach anything back to the NG.

Cheers
Andy

Andy Pope

unread,
Jan 16, 2003, 5:16:40 PM1/16/03
to
Tim,

Located the problem.
In the code where you go to create the chart the range E3:G200, although
it contains cell formula's is sparsely populated with data. For some
reason the chart tries to interpert you requirements and makes a real
has of it. Try manually selecting the range, after completion use
E9:G200, and use the chart wizard. even with the column chart if you
"Press and Hold to view" you will see the chart has no data points.

That in itself is not a problem when it creates it BUT when you try and
make it a Bubble the code blows up.

Without spending a great deal of time on the detail of your code, my
solution was to reduce the data range to valid cell content. This was
done purely by looking at the cells when it originally fails.
Note subsequent data may cause the problem to happen again.

slot the following code snip into your existing code. Watch out for word
wrap.

'---
'
' Start Bubble Chart Here!!!
'
Range("E3:G200").Select
Range("E3").Activate

'Set oChart = ActiveSheet.ChartObjects.Add(100, 100, 350, 225)
'With oChart.Chart
' .SetSourceData Source:=Sheets("x").Range("E3:G200"), PlotBy:= _
' xlColumns
' .ChartType = xlBubble
'End With

Charts.Add
' revised line
ActiveChart.SetSourceData Source:=Sheets("x").Range("E3:G6"),
PlotBy:= _
xlColumns
ActiveChart.ChartType = xlBubble
ActiveChart.SetSourceData Source:=Sheets("x").Range("E3:G200"),
PlotBy:= _
xlBubble

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=x!R3C6:R200C6"
'----

An problems post back.

Cheers
Andy

Tim

unread,
Jan 16, 2003, 6:01:13 PM1/16/03
to
Andy:
Wow, that's it. THANK YOU! ...per your suggestion, I
specified a very small initial selection, issued the
ChartType=xlBubble command, then respecified my bigger
standard range. no more problems.

Tim

>.
>

Jon Peltier

unread,
Jan 17, 2003, 12:34:10 PM1/17/03
to
Actually, Tim's code inserted formulas into the data range of the form

=IF(<something>,A10,"")

The zero length strings ("") which look like blanks caused the problem. If
they had been real blank cells, the chart would have worked, or if the
formulas looked like this instead:

=IF(<something>,A10,NA())

the #N/A errors would have worked fine in the chart. I tried the above type
of formula, and the bubble chart was built properly.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <OPYzlxavCHA.848@TK2MSFTNGP11>, an...@digitab.demon.co.uk says...

0 new messages