Sub MakeChart()
Dim Cht As Chart
Dim Wbk As Workbook
Set Wbk = ThisWorkbook
Set Cht = Charts.Add
With Cht
.HasLegend = False
.SetSourceData Source:=Range("dSource")
.ChartType = xlBubble3DEffect
End With
End Sub
worked for me, where dSource was a rectangular area of 3 columns and n rows.
Had headers/labels in the first row, and first column was X values, 2nd
column was Y values, 3rd column was Bubblesizes.
I then tried to use that as a primer range, and change the location of my
data by using individually named columns. The below worked for me. The
SeriesCollection(2) which is commented out was recognition that the bubble
chart creates a second series named Size that has the location of the size
range as the value for Y Values. However, it didn't appear necessary to
manipulate this range to make the changes. I did need to force a
calculation to get the new bubble size range recognized.
dSource referred to Sheet3!B5
14
xValues referred to Sheet3!G11:G25
yValues referred to Sheet3!H11:H25
bubblesizes referred to Sheet3!I11:I25
Sub MakeChart()
Dim Cht As Chart
Dim Wbk As Workbook
Set Wbk = ThisWorkbook
Set Cht = Charts.Add
With Cht
.HasLegend = False
.SetSourceData Source:=Range("dSource")
.ChartType = xlBubble3DEffect
.SeriesCollection(1).XValues = Range("xvalues")
.SeriesCollection(1).Values = Range("yvalues") '
' .SeriesCollection(2).Values = Range("BubbleSizes")
.SeriesCollection(1).BubbleSizes = "=" & Range("BubbleSizes").Parent.Name
& _
"!" & Range("BubbleSizes").Address(1, 1, xlR1C1)
Application.CalculateFull
End With
End Sub
--
Regards,
Tom Ogilvy
cogent said:
Sorry for the multiple listing; my news server at home is different
from
my
news server at work. I am glad to now know that the help requests are
centrally reconciled. It recalls a joke about being earnest and frank with
women: Earnest in Chicago and Frank in New York.
I tried the order you suggested and still no luck. The run-time error 1004
Method 'ChartType' of object '_Chart' failed continues to emerge.
The resultant chart up to that error takes the y and x values but has no
input information for .Bubblesizes; those data are ignored.
When setting up a chart manually, there is a specific sequence necessary
wich does not translate using the macro recorder. Neither the successfully
recorderd manual inputs, nor any combination of commands that I have been
able to conjure are successful with respect to running the macro. The
subject of bubble charts is not even mentioned in Bullen/Green's Excel 2002
VBA reference
I am really stumped. Any ideas?
W
Tom Ogilvy said:
Carried forward from your other thread as "cogent"
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
Hi Tom
Yes I saw that example. Unfortunately if one were to copy the Microsoft
example it fails for other reasons. The statement
MyOval = ActiveSheet.DrawingObjects.Name
causes an error. I think this was an early macro to convert a scatter
plot
to a bubble plot BEFORE bubble charts were a defined option under chart
types.
I have tried every combination of commands in the nacro and I just cannot
get it to work. In fact, creating a bubble chart by hand requires a
definite series of manual inputs as well.
The responses on this question have been scant so I must conclude
that
I
must be charting new territory.
Any help is appreciated.
W
I have never created one, but maybe this will give you some insights:
http://support.microsoft.com/default.aspx?scid=kb;en-us;139662&Product=xlw
How to Use a Visual Basic Macro to Create a Bubble Chart
the Order of commands can be important when using code to create
charts -
don't know if that is related to your problem - just a general caution.
--
Regards,
Tom Ogilvy
Hello
Creating a bubble chart vba continues to generate a runtime
error
1004
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")
.SeriesCollection(1).Values =
Wbk.Application.Range("yvalues")
.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
can
create EVERY other charttype this way except bubble charts.
What
