Novice's Question re Chart Programming

[John Bigelow]

Hi all. I am relatively new to using VBA to program Excel and have just
started to tinker with charts. I'm just at the stage where I'm tinkering,
trying to figure out how things work before trying anything serious, and in
the process I have come accross a very confusing situation.

I set up a very simple worksheet with the following dummy data in A1:C7

X Y Z
1 10 6
2 20 5
3 30 4
4 40 3
5 50 2
6 60 1

Then, I wrote two subroutines in VBA to try my hand at creating charts:

Public Sub Step1()
ThisWorkbook.Activate
Dim MyChart1 As Chart
Set MyChart1 = ThisWorkbook.Charts.Add
MyChart1.Name = "Chart1"
With MyChart1
.ChartType = xlLine
.SetSourceData _
Source:=Sheets("Sheet1").Range("A1:C7"), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Text = "From Step 1"
End With
End Sub

Public Sub Step2()
ThisWorkbook.Activate
Dim MyChart2 As Chart
Set MyChart2 = ThisWorkbook.Charts.Add
MyChart2.Name = "Chart2"
With MyChart2
.ChartType = xlBubble
.SetSourceData _
Source:=Sheets("Sheet1").Range("A1:C7"), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Text = "From Step 2"
End With
End Sub

If I run Step2() by itself, I get a run-time error 1004
"Method `ChartType' of object `_Chart' failed."

But, if I run Step1() first and then run Step(2), both subroutines run with
no problem. Step1 produces a line chart and Step2 produces a bubble chart.

In fact, both run with no problem if I run

Public Sub Combo()
Call Step1
Call Step2
End Sub

So my questions are: 1) What is wrong with Step2 that is not wrong with
Step1? They look pretty parallel to me. 2) Why does running Step1 first
make it possible for Step2 to run without an error? 3) How should I code
the creation of a chart to get consistently succesful results?

I would certainly be grateful for helpful suggestions on any or all of these
questions.

John

 

[SeanC UK]

Hi John,

Try changing the chart type after you have finished adding the data:

Public Sub Step2()
ThisWorkbook.Activate
Dim MyChart2 As Chart
Set MyChart2 = ThisWorkbook.Charts.Add
MyChart2.Name = "Chart2"
With MyChart2
.SetSourceData _
Source:=Sheets("Sheet1").Range("A1:C7"), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Text = "From Step 2"
End With
MyChart2.ChartType = xlBubble
End Sub

If you still get problems let me know.

Sean.

 

[John Bigelow]

Sean,

Thank you very much.

That worked - sort of. If I do as you suggest, then the subroutine runs
without a run time error and produces a bubble chart.

However, the bubble chart isn't correct. When the data are added before
setting the chart type to "bubble" the second and third columns are
interpreted as values for two distinct series. That interpretation persists
after the chart type is changed from the default (I guess it's "line.") to
bubble.

I had noticed this before, which was why I set the chart type first. When
the chart type is "bubble" to begin with then the second column is
interpreted as the Y values for the bubble and the third column is
interpreted as bubble width.

So, it seems that there's a Catch 22 here. The data for a bubble chart
won't be interpreted correctly unless the chart type is set to bubble before
the data are added. However, you can't set the chart type to bubble without
adding the data first. I can break the log jam by repeating the data adding
step - once before and once after setting the chart type. That is,

Public Sub Step2()
ThisWorkbook.Activate
Dim MyChart2 As Chart
Set MyChart2 = ThisWorkbook.Charts.Add
MyChart2.Name = "Chart2"
With MyChart2
.SetSourceData _
Source:=Sheets("Sheet1").Range("A1:C7"), _
PlotBy:=xlColumns
.ChartType = xlBubble
.SetSourceData _
Source:=Sheets("Sheet1").Range("A1:C7"), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Text = "From Step 2"
End With
End Sub

Can this possibly be the way this is supposed to work? It seems an
understatement to call it "clumsy." Or, perhaps I'm still missing something.
Like I said, I am a novice.

 

[SeanC UK]

Hi John,

I haven't looked into it in great detail (why setting the chart type fails
sometimes, but not others), but it is linked somehow to the active sheet. If
the first chart is showing then no error occurs (hence running Sub1 then Sub2
was OK) but if another sheet was active the chart type fails.

Anyway, the following code should work regardless, it has done for me at
least. You will need to amend it so that it specifies the ranges of data to
include what you need. At present it is only using column A-C rows 2-7,
specifically. I would suggest you write something else that will identify the
data ranges and pass them to the sub as text strings and place in the
appropriate lines, or just write such code within this sub if you prefer.

Whilst testing I would comment out - using the apostrophe - the line that
sets the chart name, then you can keep testing without having to delete the
chart each time.

I'm not sure that I have the X, Y and Sizes set to the correct ranges, but
I'm sure you'll be able to swap these bits around as you find necessary.


Public Sub Step2()
ThisWorkbook.Activate
Dim MyChart2 As Chart
Dim lngSeriesCount As Long
Set MyChart2 = ThisWorkbook.Charts.Add
MyChart2.Name = "Chart2"
With MyChart2
.SetSourceData _
Source:=Sheets("Sheet1").Range("A1:C7"), _
PlotBy:=xlColumns
.ChartType = xlBubble
For lngSeriesCount = 2 To .SeriesCollection.Count
.SeriesCollection(lngSeriesCount).Delete
Next

With .SeriesCollection(1)
.XValues = Sheets("Sheet1").Range("A2:A7")
'"=Sheet1!R2C1:R7C1"
.Values = Sheets("Sheet1").Range("B2:B7")
'"=Sheet1!R2C2:R7C2"
.BubbleSizes = "=Sheet1!R2C3:R7C3"
End With
.HasTitle = True
.ChartTitle.Text = "From Step 2"
End With

End Sub


Any problems then let me know, and I'll try harder!

Sean.