Bubble Graph by Macro REVISITED

[cogent]

Hello again!

Thanks for your reply.

I am still having trouble. Please note the following code:

Sub MakeChart()
Dim Cht As Chart
Dim Wbk As Workbook
Set Wbk = ThisWorkbook
Set Cht = Charts.Add
With Cht
.HasLegend = False
.SetSourceData Source:=Wbk.Application.Range("bubblesize"),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Wbk.Application.Range("xvalues")
'"=MULTIRUN!R2C4:R776C4"
.SeriesCollection(1).Values = Wbk.Application.Range("yvalues") '
"=MULTIRUN!R2C5:R776C5"
.SeriesCollection(1).BubbleSizes = "=MULTIRUN!R2C21:R776C21"

.ChartType = xlBubble3DEffect
[blah, blah]

End with

First, while Set.Source.data will accept a named range,
SeriesCollection(1).Bubblesizes will not. This strikes me as very peculiar.
The following will not go:

..SeriesCollection(1).BubbleSizes = Wbk.Application.Range("bubblesize")

I got around that problem by using the RC format.

But the ChartType still will not work when Bubble is the constant. i
defined the ranges first as per your suggestion. What am I missing?

Thanks!

W

PS It occurred to me that SetSourceData may not be necessary with the Bubble
Chart.... but the same problem arises whether or not the SetSourceData line
is commented out.

Named ranges can be used dynamically when specifying the X and Y values
of a chart series, but when used for the entire source data range, they
are converted to the range address at the time the source data is set.

Bubble charts are a special case in Excel VBA. You must specify the
chart source data range before specifying the chart type, despite the
order of steps in macro recorder code.

You could make your chart, and use a Worksheet_Change event to detect
changes to the desired range. In the event procedure, you can reset the
chart's source data to the named range. For example, if your source data
is in the range named "MyRange", right click on the sheet tab, select
View Code, and paste this into the code module that pops up:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("MyRange")) Is Nothing Then
ChartObjects(1).Chart.SetSourceData Source:=Range("MyRange")
End If
End Sub

This assumes the chart is on the same sheet as the data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Hello

Two questions.

I am creating a chart by macro only because the chart seems not to accept
NAMED RANGES when defining the y values and x values. Does that seem right?
Could it be that named ranges cannot be used to define data source?

Assuming that named ranges cannot be used (what a pity), I have tried macros
but I have also had trouble setting up the Bubble Chart. I get a Run Time
Error 1004 with the follwoing code (generalized):

Sub MakeChart()
Dim Cht As Chart
Set Cht = Charts.Add
With Cht
.ChartType = xlBubble3DEffect
[blah blah]
End With

I get the same 1004 error whether the Chart Type is set to xlBubble, or the
constant 15. I tried every other Chart Type including 3D Columns and they
work fine. What gives with the Bubble Charts?

W

 

[Jon Peltier]

Hi Wayne -

Tom and I've already responded to your later thread. You have followed
my suggestion of assigning data before assigning the chart type. But if
you have no bubble series yet defined, the .BubbleSizes makes no sense.
You would need to use SetSourceData, or define .Values for two series
(the second becomes BubbleSizes when you change the chart type to bubble).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Hello again!

Thanks for your reply.

I am still having trouble. Please note the following code:

Sub MakeChart()
Dim Cht As Chart
Dim Wbk As Workbook
Set Wbk = ThisWorkbook
Set Cht = Charts.Add
With Cht
.HasLegend = False
.SetSourceData Source:=Wbk.Application.Range("bubblesize"),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Wbk.Application.Range("xvalues")
'"=MULTIRUN!R2C4:R776C4"
.SeriesCollection(1).Values = Wbk.Application.Range("yvalues") '
"=MULTIRUN!R2C5:R776C5"
.SeriesCollection(1).BubbleSizes = "=MULTIRUN!R2C21:R776C21"

.ChartType = xlBubble3DEffect
[blah, blah]

End with

First, while Set.Source.data will accept a named range,
SeriesCollection(1).Bubblesizes will not. This strikes me as very peculiar.
The following will not go:

..SeriesCollection(1).BubbleSizes = Wbk.Application.Range("bubblesize")

I got around that problem by using the RC format.

But the ChartType still will not work when Bubble is the constant. i
defined the ranges first as per your suggestion. What am I missing?

Thanks!

W

PS It occurred to me that SetSourceData may not be necessary with the Bubble
Chart.... but the same problem arises whether or not the SetSourceData line
is commented out.

Named ranges can be used dynamically when specifying the X and Y values
of a chart series, but when used for the entire source data range, they
are converted to the range address at the time the source data is set.

Bubble charts are a special case in Excel VBA. You must specify the
chart source data range before specifying the chart type, despite the
order of steps in macro recorder code.

You could make your chart, and use a Worksheet_Change event to detect
changes to the desired range. In the event procedure, you can reset the
chart's source data to the named range. For example, if your source data
is in the range named "MyRange", right click on the sheet tab, select
View Code, and paste this into the code module that pops up:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("MyRange")) Is Nothing Then
ChartObjects(1).Chart.SetSourceData Source:=Range("MyRange")
End If
End Sub

This assumes the chart is on the same sheet as the data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

cogent wrote:

Hello

Two questions.

I am creating a chart by macro only because the chart seems not to

accept

NAMED RANGES when defining the y values and x values. Does that seem

right?

Could it be that named ranges cannot be used to define data source?

Assuming that named ranges cannot be used (what a pity), I have tried

macros

but I have also had trouble setting up the Bubble Chart. I get a Run

Time

Error 1004 with the follwoing code (generalized):

Sub MakeChart()
Dim Cht As Chart
Set Cht = Charts.Add
With Cht
.ChartType = xlBubble3DEffect
[blah blah]
End With

I get the same 1004 error whether the Chart Type is set to xlBubble, or

the

constant 15. I tried every other Chart Type including 3D Columns and

they

work fine. What gives with the Bubble Charts?

W