bubble Chart bubblesize problem in VBA

[Revolvr]

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!

 

[Peter T]

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]

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

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

 

[Peter T]

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

Regards,
Peter T

That's what I needed. Though I did
something slightly
different.