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" <Revo...@cox.net> wrote in message
>
> news:001d2862-d8cd-4742-936f-(E-Mail Removed)...
>
> > 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
|
Similar Threads
