Bubble Chart by Macro

  • Thread starter Thread starter cogent
  • Start date Start date
C

cogent

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
 
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
 
Hello

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.


Jon Peltier said:
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
Click to expand...
Click to expand...
 
I would think you have taken the advice too literally. You won't have a
bubblesizes attribute until you have a bubble chart. Perhaps you need

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"

.ChartType = xlBubble3DEffect

.SeriesCollection(1).BubbleSizes = "=MULTIRUN!R2C21:R776C21"

[blah, blah]

End with

Set the ranges, make it a bubble chart, then deal with attributes of a
bubble chart.

--
Regards,
Tom Ogilvy


cogent said:
Hello

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.


Jon Peltier said:
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,
Click to expand...
Click to expand...
or
the
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Bubble Graph by Macro REVISITED 1
Bubble Chart using VBA 7
Variable Range/chart 1
How to create a bubble chart with more than 2 variables 0
Named Ranges for Chart 1
Macro Copy/Paste error 1
Bubble Chart 1
Chart location method fails using sheet name 3

Back
Top