Creating graphs in Excel

[Always confused]

I have a program that brings in data, carries out calculations and exports the results to Excel. The data is input by the user one set at a time on different days. I want to plot the results of some of the calculations against the date (which are both already columns in my spreadsheet) and update this every time a new set of data is entered. How do I use VB to create a graph in my Excel workbook? I really have no clue how to start with this.

Thanks

[Andrea_Rossini]

Hello!
Add a reference to Excel Object library in your application.

First thing

Dim x As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set x = New Excel.Application

Set wb = x.WorkBooks.Open("file name here")
Set ws = wb.Sheets(1) ' 1 or 0 ?? don't remember

Now that you are ready you can create the graph this way:

    Dim ch As Excel.Chart

    Set ch = wb.Charts.Add
    ch.ChartType = xlColumnClustered
    ch.SetSourceData Source:=ws.Range("A1:B15"), PlotBy :=xlColumns ' Choose the correct interval
    With ch
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With

    wb.Save

    Set ch = Nothing
    Set ws = Nothing
    Set wb = Nothing

    x.Quit

    Set x = Nothing

this will add an istogram to a separate view. I suggest you to study VBA and also the VBA object exposed by Excel.
You'll find everything in the msoffice help.


// EDIT wooops !!! Set ch = wb.Charts.Add !!!

[Always confused]

Thanks. I'm now having a problem with my data source. If I want it to plot all of whatever is filled in column B against those in Column K, how do I write the range?

[Andrea_Rossini]

    ch.SetSourceData Source:=ws.Range("B1:B12"), PlotBy _
        :=xlColumns ' Data

    ch.SeriesCollection(1).XValues = ws.Range("K1:K12") ' Category

[Always confused]

What is the chart type for a line chart? I've tried xlLine and it doesn't seem to be right.

Thanks

[Andrea_Rossini]

I'm not sure of what you mean with this post. Can you explain more??

PS: The xlChartType enumeration contains all of the graph styles.

[Always confused]

It's ok, I've solved the last query - typo. Is there a way of formatting the grph, so that I can fit, say, 4 of them on a page?

[Andrea_Rossini]

ch.Location Where:=xlLocationAsObject, Name:="Sheet1"

To place it on the worksheet.

Then

ch.ChartArea.Top
ch.ChartArea.Left


are the properties to muck with to move chart around the sheet

[Always confused]

I'm having difficulty with the setting of these properties. Could you give me an example please.

Thanks

[Andrea_Rossini]

What kind of difficulty? are you getting some kind of error message or just unexpected positioning of the chart?

[Always confused]

It says:

Runtime Error 1004:
Unable to set the Top property of the ChartArea class.

Any ideas?

Thanks

[Andrea_Rossini]

This piece

ws.Shapes(chartname).Top = somenumber
ws.Shapes(chartname).Left = somenumber

seems to work as long as the char is placed over the ws sheet.
the main problem is getting chartname.
it is expected to be "Chart N" but when you read ch.Name you get "Sheet N Chart N".

If I get an idea I'll tell you.

[Always confused]

I've used the following code to create the graph. The graph is on the "Coloured Charts" sheet and the data needs to come from the "Averages" sheet. If I use this code, I get an error:

Run-time error '-2147221080(800401a8)'
Method 'SetSourceData' of object'_Chart' failed

Set xlchart = xlApp.Charts.Add
    xlchart.ChartType = xlLineMarkers
    Set xlSheet4 = xlApp.Worksheets.Add
    xlSheet4.Name = "Colourant Charts"
    xlchart.Location Where:=xlLocationAsObject, Name:="Colourant Charts"
    xlSheet4.Shapes("Chart 1").Top = 1
    xlSheet4.Shapes("Chart 1").Left = 1
    Set xlSheet3 = xlApp.Worksheets("Averages")
    xlchart.SetSourceData Source:=xlSheet3.Range("K1:K12"), PlotBy:=xlColumns
    xlchart.SeriesCollection(1).XValues = xlSheet3.Range("B1:B12")

any ideas?

[Andrea_Rossini]

I reversed the order of operations. An Also I used WorkBook instead of Application. Now It works.

    Dim xlChart     As Chart
    Dim xlSheet3    As Worksheet
    Dim xlSheet4    As Worksheet

    Set xlChart = ActiveWorkbook.Charts.Add
    xlChart.ChartType = xlLineMarkers

    Set xlSheet3 = ActiveWorkbook.Worksheets("Averages")
    xlChart.SetSourceData Source:=xlSheet3.Range("K1:K12"), PlotBy:=xlColumns
    xlChart.SeriesCollection(1).XValues = xlSheet3.Range("B1:B12")

    Set xlSheet4 = ActiveWorkbook.Worksheets.Add
    xlSheet4.Name = "Colourant Charts"
    xlChart.Location Where:=xlLocationAsObject, Name:="Colourant Charts"
    
    xlSheet4.Shapes("Chart 1").Top = 1
    xlSheet4.Shapes("Chart 1").Left = 1

[Always confused]

Thanks a lot. You've been very helpful and I now have a fully functioning graph that does exactly what I need it to do.