Hi all,

I am trying to create a bubble chart in VBA but the code crashes on
the bubblesize line. I originally recorded a macro then modified it to
allow several series and to use named ranges for the data.

When the code gets to
ActiveChart.SeriesCollection(i).BubbleSizes = Range("zprod" & i)

It crashes with an error. I searched through these groups and saw
some info on this but nothing I tried would work.

I have created named ranges like xprod1, yprod1, zprod1, where "zprod"
is the bubblesize. The range "allplotdata" contains three columns of
data but it not want I eventually want to plot. That was added based
on other comments about having the data specified before changing to a
bubble chart.

numseries is an integer from 1 to 5. Each would be a separate series
with a different color. Doesn't matter since it crashes on 1.

The code crashes on i = 1 on the bubblesizes line. It says runtime
error 5, Invalid procedure or argument.

So far the code looks like this:

Sub CreateBubble()

Charts.Add

ActiveChart.SetSourceData Source:=Range("allplotdata")
ActiveChart.ChartType = xlBubble3DEffect
'ActiveChart.SeriesCollection(1).Delete

For i = 1 To numseries
If (i 1) Then
' NewSeries is done once for each series except the first
' because by default there is already one series
ActiveChart.SeriesCollection.NewSeries
End If

ActiveChart.SeriesCollection(i).Name = prodlist(i, 1)
ActiveChart.SeriesCollection(i).XValues = Range("xprod" & i)
ActiveChart.SeriesCollection(i).Values = Range("yprod" & i)
ActiveChart.SeriesCollection(i).BubbleSizes = Range("zprod" &
i)

Next i

Thanks for your help!

Try with something like the following

Dim sr as Series
Dim s as string

set sr = ActiveChart.SeriesCollection.NewSeries
sr.values = Range("yprod" & i)
s = Application.ConvertFormula(Range("zprod" & .Address(external:=True),
xlA1, xlR1C1)
sr.Bubblesizes = "=" & s

Regards,
Peter T


"Revolvr" wrote in message
...
Hi all,

I am trying to create a bubble chart in VBA but the code crashes on
the bubblesize line. I originally recorded a macro then modified it to
allow several series and to use named ranges for the data.

When the code gets to
ActiveChart.SeriesCollection(i).BubbleSizes = Range("zprod" & i)

It crashes with an error. I searched through these groups and saw
some info on this but nothing I tried would work.

I have created named ranges like xprod1, yprod1, zprod1, where "zprod"
is the bubblesize. The range "allplotdata" contains three columns of
data but it not want I eventually want to plot. That was added based
on other comments about having the data specified before changing to a
bubble chart.

numseries is an integer from 1 to 5. Each would be a separate series
with a different color. Doesn't matter since it crashes on 1.

The code crashes on i = 1 on the bubblesizes line. It says runtime
error 5, Invalid procedure or argument.

So far the code looks like this:

Sub CreateBubble()

Charts.Add

ActiveChart.SetSourceData Source:=Range("allplotdata")
ActiveChart.ChartType = xlBubble3DEffect
'ActiveChart.SeriesCollection(1).Delete

For i = 1 To numseries
If (i 1) Then
' NewSeries is done once for each series except the first
' because by default there is already one series
ActiveChart.SeriesCollection.NewSeries
End If

ActiveChart.SeriesCollection(i).Name = prodlist(i, 1)
ActiveChart.SeriesCollection(i).XValues = Range("xprod" & i)
ActiveChart.SeriesCollection(i).Values = Range("yprod" & i)
ActiveChart.SeriesCollection(i).BubbleSizes = Range("zprod" &
i)

Next i

Thanks for your help!

On Feb 26, 11:06*am, "Peter T" <peter_t@discussions wrote:
Try with something like the following

Dim sr as Series
Dim s as string

set sr = ActiveChart.SeriesCollection.NewSeries
sr.values = Range("yprod" & i)
s = Application.ConvertFormula(Range("zprod" & .Address(external:=True),
xlA1, xlR1C1)
sr.Bubblesizes = "=" & s

Regards,
Peter T

"Revolvr" wrote in message

...

Hi all,

I am trying to create a bubble chart in VBA but the code crashes on
the bubblesize line. I originally recorded a macro then modified it to
allow several series and to use named ranges for the data.

When the code gets to
ActiveChart.SeriesCollection(i).BubbleSizes = Range("zprod" & i)

It crashes with an error. *I searched through these groups and saw
some info on this but nothing I tried would work.

I have created named ranges like xprod1, yprod1, zprod1, where "zprod"
is the bubblesize. The range "allplotdata" contains three columns of
data but it not want I eventually want to plot. That was added based
on other comments about having the data specified before changing to a
bubble chart.

numseries is an integer from 1 to 5. Each would be a separate series
with a different color. Doesn't matter since it crashes on 1.

The code crashes on i = 1 on the bubblesizes line. It says runtime
error 5, Invalid procedure or argument.

So far the code looks like this:

Sub CreateBubble()

* *Charts.Add

* *ActiveChart.SetSourceData Source:=Range("allplotdata")
* *ActiveChart.ChartType = xlBubble3DEffect
* *'ActiveChart.SeriesCollection(1).Delete

* *For i = 1 To numseries
* * * *If (i 1) Then
* * * * * *' NewSeries is done once for each series except the first
* * * * * *' because by default there is already one series
* * * * * *ActiveChart.SeriesCollection.NewSeries
* * * *End If

* * * *ActiveChart.SeriesCollection(i).Name = prodlist(i, 1)
* * * *ActiveChart.SeriesCollection(i).XValues = Range("xprod" & i)
* * * *ActiveChart.SeriesCollection(i).Values = Range("yprod" & i)
* * * *ActiveChart.SeriesCollection(i).BubbleSizes = Range("zprod" &
i)

* *Next i

Thanks for your help!

Great! That's what I needed. Though I did something slightly
different. Looks like BubbleSize needs a formula with an R1C1 style
address string.
This worked:

sizestr = Range("zprod" & i).Address(ReferenceStyle:=xlR1C1,
external:=True)
ActiveChart.SeriesCollection(i).BubbleSizes = "=" & sizestr

The "slightly different" is much more sensible. Not sure why I suggested
CovertFormula, never have done before in this context!

Regards,
Peter T

"Revolvr" wrote in message
news:a9b696f3-7b38-45aa-85f8-Great! That's what I needed. Though I did
something slightly
different.
Looks like BubbleSize needs a formula with an R1C1 style
address string.
This worked:

sizestr = Range("zprod" & i).Address(ReferenceStyle:=xlR1C1,
external:=True)
ActiveChart.SeriesCollection(i).BubbleSizes = "=" & sizestr