How to create a chart that has two data sets using Excel VBA 2010

Question

This is really two separate questions. First, I have an excel sheet with three columns of data. I have 12 rows of data in columns A, C, and E. From what I've been looking up, This program should assign column A to the x-axis, and Columns C and D to the y-axis, while also adding labels.

Sub ChartMaker()
    ActiveWorkbook.Charts.Add
    With ActiveWorkbook.ActiveChart
        .ChartType = xlXYScatterLines
        ActiveChart.SetSourceData Source:=Range("A1:A12,C1:C12,E1:E12")
        .Location xlLocationAsNewSheet
        .HasTitle = True
        .ChartTitle.Characters.Text = "CFL Over Time"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (Days)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "CFL"
        .Axes(xlCategory).HasMajorGridlines = True
        .Axes(xlCategory).HasMinorGridlines = False
        .Axes(xlValue).HasMajorGridlines = True
        .Axes(xlValue).HasMinorGridlines = False
        .HasLegend = False
    End With
End Sub

I keep getting the error "Object variable or With block variable not set".

That's my first question!

My second question is how can I create a graph with the following sets of data, where there are two sets of data on the y-axis, but they share an x-axis.

Data set one:

Data set two:

Is there a way to get these on the same graph? (Using VBA of course)

SeanC · Accepted Answer · 2012-08-07 20:39:57Z

5

After creating the chart

ActiveSheet.Shapes.AddChart.Select  
'this adds the chart and selects it in the same statement
ActiveChart.ChartType = xlXYScatter

you then add the first set of points

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=Sheet1!$C$1"
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$A$3:$A$12"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$C$3:$C$12"

and use the same method to add the second set

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "Sheet1!$E$1"
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!$D$3:$D$12"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!$E$3:$E$12"

answered Aug 7, 2012 at 20:39

SeanC

16k5 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

TheTreeMan Over a year ago

Does the first part add the chart? Or do I also need ActiveWorkBook.Charts.Add for the first line?

SeanC Over a year ago

the first line creates the chart, and also makes it active, all in a single statement

TheTreeMan Over a year ago

Thank you! I have a few other questions if you don't mind. First, when this creates the graph, for some reason on the legend it has three different series listed. Why is this third one being created? I have exactly what you have up there, just changed to fit my ranges. Lastly, how do I change the marker type/size/color for the different series?

TheTreeMan Over a year ago

I should also add, how do I make the chart fill a specific area? For example, if I want it to resize and move to fit the area from J1 to S7?

SeanC Over a year ago

.Left, .top, .Width, .Height (all measured in points) or, if you want it on it's own sheet, ActiveChart.Location Where:=xlLocationAsNewSheet

Collectives™ on Stack Overflow

How to create a chart that has two data sets using Excel VBA 2010

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related