Setting X-Axis labels with a ChartSpace

I'm using an OWC11 ChartSpace object on a userform. It contains 1 chart and the x-axis is a set of dates to correspond to the y-values. The ChartType is a LineType.

How do I set the X-axis labels to my dates using VBA? I'm using Office 2003.
GroganJAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Sure - here's a rough sample:

Dim ser As ChSeries
    Dim cht As ChChart
    Dim objAxis As ChAxis
    ' note the data and category arrays must be of Variant type
    Dim varDataCats(1 To 50), varDataVals(1 To 50)
    Dim n As Long
    
    For n = 1 To 50
        varDataCats(n) = DateSerial(2010, 1, n)
        varDataVals(n) = n
    Next n
    
    With Me.ChartSpace1
        Debug.Print .Charts.Count
        .Charts.Add
        Set cht = .Charts(0)
    End With
    
    With cht
        .Type = chChartTypeLineMarkers
        .SeriesCollection.Add
        .SetData chDimCategories, chDataLiteral, varDataCats
        .SeriesCollection(0).SetData chDimValues, chDataLiteral, varDataVals
        .HasLegend = False
        .HasTitle = True
        Set objAxis = .Axes(0)
        With objAxis
            .Font.Size = 6
            .NumberFormat = "MM/DD/YYYY"
            ' don't want the dates grouped
            .GroupingType = chAxisGroupingNone
        End With

    End With

Open in new window

0
 
werafaCommented:
start recording a macro and do it manually.
then look at the code and modify it to suit

there is no vba code to set the axis and cart labels dynamically
0
 
Rory ArchibaldCommented:
Do you have a spreadsheet control that you are using for the data or is the whole thing populated in code?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GroganJAuthor Commented:
rorya - the entire thing is programmed from VBA. I'm not using Excel at all.
0
 
GroganJAuthor Commented:
Werafa - I had tried that, but the methods that a recorded macro picks up do not all exist in the OWC11 object. And specifically, the methods around setting the x-axis.

If vba doesn't let you change the axes, can anyone recommend a third party object that does allow me more control over graphing? Cheap is good, free is better :-)
0
 
Rory ArchibaldCommented:
You don't actually need to be in Excel to use a spreadsheet control! :)
I should be back in the office shortly - I'll check my OWC code samples - I'm pretty sure it is possible to do what you want. Do you have the dates in an array already?
0
 
GroganJAuthor Commented:
Rorya - thanks for that, I appreciate the help. I have 2 arrays that I read from a database. One contains the dates for the x-axis and the other contains the y-values.
0
 
Rory ArchibaldCommented:
Assuming your chart variable is called cht and your date array is adteCategories, the code would be:

cht.SetData chDimCategories, chDataLiteral, adteCategories


I assume you know you could bind the chart to the database?
0
 
GroganJAuthor Commented:
Rorya - I can't get this to work. Do you have a code sample that you could post?

I don't want to bind to a database as there is some manipulation of the data before displaying.
0
 
GroganJAuthor Commented:
Thanks Rorya - my mistake was in not making the date array a variant array.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.