VB for MSChart - display the data points as circles on a 2dLine Chart and x-axis showing dates.

Hope this helps! I find I have good luck with the chart control as long as I don't try to fine tune the appearance too much.  

Option Explicit

Private Sub Form_Load()
    Dim x As Integer
    Randomize Timer
    With MSChart1
        .AutoIncrement = True
        .chartType = VtChChartType2dLine
        .ColumnCount = 1
        .RowCount = 12
        .Plot.SeriesCollection(1).SeriesMarker.Show = True
        For x = 1990 To 2001
            .RowLabel = CStr(x)
            .Data = Rnd
        Next x
    End With
    WindowState = vbMaximized
    Show
End Sub

Private Sub Form_Resize()
    MSChart1.Move ScaleLeft, ScaleTop, ScaleWidth, ScaleHeight
End Sub

Using

oChart.SeriesCollection(1).MarkerStyle =
xlMarkerStyleDiamond

you can set data points on 2d line charts.
oChart - CHart Object
SeriesCollection(1) - Data collection 1
MarkerStyle -  Marker style
xlMarkerStyleDiamond - DImaond marker

rsk

Or for a different viewpoint on diamonds, try this:

.Plot.SeriesCollection(1).SeriesMarker.Show = True
.Plot.SeriesCollection(1).SeriesMarker.Auto = False
.Plot.SeriesCollection(1).DataPoints.Item(-1).Marker.Size = 200
.Plot.SeriesCollection(1).DataPoints.Item(-1).Marker.Style = VtMarkerStyleDiamond
.Plot.SeriesCollection(1).DataPoints.Item(-1).Marker.Pen.VtColor.Red = 0
.Plot.SeriesCollection(1).DataPoints.Item(-1).Marker.Pen.VtColor.Blue = 0
.Plot.SeriesCollection(1).DataPoints.Item(-1).Marker.Pen.VtColor.Green = 0

listening...

to adq

adq; I am very encouraged!

I tested your first subroutine which displayed red crosses as markers, but I was unable to specify other marker types or colors using the
.plot.SeriesCollection(1).... lines in your second posting..

I am very new to all this, so could you send your first subroutine with the appropriate code to specify (say) blue diamond markers?

Thanks

David  

Glad to help - here is the routine with blue diamonds. Let me know if you have trouble with it.

Private Sub Form_Load()
   Dim x As Integer
   Randomize Timer
   With MSChart1
       .AutoIncrement = True
       .chartType = VtChChartType2dLine
       .ColumnCount = 1
       .RowCount = 12
       With .Plot.SeriesCollection(1)
            .SeriesMarker.Show = True
            .SeriesMarker.Auto = False
            .DataPoints.Item(-1).Marker.Size = 200
            .DataPoints.Item(-1).Marker.Style = VtMarkerStyleDiamond
            .DataPoints.Item(-1).Marker.Pen.VtColor.Red = 0
            .DataPoints.Item(-1).Marker.Pen.VtColor.Blue = 255
            .DataPoints.Item(-1).Marker.Pen.VtColor.Green = 0
        End With
       For x = 1990 To 2001
           .RowLabel = CStr(x)
           .Data = Rnd
       Next x
   End With
   WindowState = vbMaximized
   Show
End Sub

to adq:

Yes, that worked fine! Can you point me to a source of information on the various options for MSChart. I didn't find anything on (for example) marker.pen in MSDN. 50 points more for your trouble...

David

DrD, I've never found a good source of online information for the MSChart control, only bits and pieces here and there.  Most of my limited understanding has been gained by trial and error using IntelliSense, a very slow method.  I don't understand why there is not more info available on this control. As you know the MS documentation is terrible in this respect.  I would expect there would be entire websites dedicated to using this control alone but apparently you me and nickwoolley are the only people in the world using it.  I also tried to search EE to find more info but I'll bet you can guess how well that worked.  Another question I asked in EE was how to rapidly update charts without the annoying flicker. I got a good solution too. I think EE is great if one ignores the bugs.  

I did find an MSDN sample (under a problem) - the link follows. I found it by searching for "mschart marker" from within MSDN.  However, please contact community support and have them remove the extra 50 points - it's not necessary.  In fact, take off 150 - the expert points aren't very useful but you could use the question points to ask another interesting question.  

http://support.microsoft.com/support/kb/articles/Q178/5/45.ASP

If you find a worthwhile source of info, please post it to this question even after it has been accepted.

Thanks!

I know that last answer was kind of rambling (too much caffeine).  I just did another internet search using a different search engine. It found this link which is a tutorial but it might be a little too simple.  

http://www.vb123.com/toolshed/99_vbchart/vbchart1.htm

to adq

Yes, I found that one too...Toolshed refers to some source of info from the MS Knowledge base which I can't find.. Any ideas there?

(I have obtained a few more pointers from a further searc of MSDN)

Also, of course, there are a few hints from looking at the MSChart Properties list.

Thanks

David

David, you're very welcome for what little it was worth.  The available info seems to be very limited.

What source of info are you referring to?  Is it the following quote?

Microsoft Chart comes with volumes of help in MSDN and 95% of it is useless. Here is an example of the help that you get for the chart legend property

"Returns a reference to a Legend object that contains information about the appearance and behavior of the graphical key and accompanying text that describes the chart series."

or something else?  

Did you get the chart working the way you want or do we still have a question?

to adq

Yes, that's the reference... I couldn't find the "volumes of help in MSDN"

I am playing with the format (diamond markers etc), but perhaps you can help me as well with this date axis problem. Can MSChart have a numeric x-axis scale of any kind, or must they always be strings (labels). VB can convert a mm/dd/yyyy date into days, and I was thinking of constructing the x-axis using this scale...

I think there are entries for many of the mschart properties in MSDN but much of it is useless.  For example the description of the type property is:

"Returns or sets the scale type of an axis."

I find that "explanation" less than helpful.  I think that is the point that the folks at vb123.com were making.

Regarding the x-axis issue, that threw me for a long time too.  You could convert your numbers into strings or you might want to consider using a VtChChartType2dXY instead of a VtChChartType2dLine, which would allow you to use actual numbers.  If we switch to VtChChartType2dXY, we may have to change some of the associated code but it will be an educational experience!

Do you have a preference?    

to adq

I need to show the datapoints with connecting lines, with the scaled date axis of course! The XY plot has the better potential, I think...

I tried this, with very odd results...
Private Sub Form_Load()
   Dim x As Integer
   Randomize Timer
   With MSChart1
       .AutoIncrement = True
       .chartType = VtChChartType2dXY
       '.ColumnCount = 1
       '.RowCount = 12
       .Plot.SeriesCollection(1).SeriesMarker.Show = True
       For x = 1990 To 2001
           .RowLabel = x
           .Data = Rnd
       Next x
   End With
   WindowState = vbMaximized
   Show
End Sub

Private Sub Form_Resize()
   MSChart1.Move ScaleLeft, ScaleTop, ScaleWidth, ScaleHeight
End Sub


Thanks

David



 

To adq

I got this from MSDN and it is VERY promising...

Now for the date axis....

David


Private Sub Form_Load()
         Dim Graph(1 To 10, 1 To 2) As Single
         Dim x As Integer
         For x = 1 To 10
            Graph(x, 1) = x   ' value for X-axis
            Graph(x, 2) = x * 10 ' value for Y-axis
         Next x
         MSChart1.chartType = VtChChartType2dXY  ' set to X Y Scatter chart
         MSChart1 = Graph ' populate chart's data grid using Graph array
         ' Leave the following line commented until step 6:
       MSChart1.Plot.UniformAxis = False
      End Sub

Yes, that's exactly the right idea! Pardon me for being dense, but what is the outstanding question?  I know its an issue with dates on the x-axis but can you clarify it a little for me?  

Thanks!

Like this?

Option Explicit
Private Sub Form_Load()
        Dim Graph(1 To 10, 1 To 2) As Single
        Dim x As Integer
        For x = 1 To 10
           Graph(x, 1) = x + 1989 ' value for X-axis
           Graph(x, 2) = Rnd ' value for Y-axis
        Next x
        With MSChart1
            .chartType = VtChChartType2dXY  ' set to X Y Scatter chart
            .Plot.UniformAxis = False
            With .Plot.SeriesCollection(1)
                .SeriesMarker.Show = True
                .SeriesMarker.Auto = False
                .DataPoints.Item(-1).Marker.Size = 200
                .DataPoints.Item(-1).Marker.Style = VtMarkerStyleDiamond
                .DataPoints.Item(-1).Marker.Pen.Width = 25
                .DataPoints.Item(-1).Marker.Pen.VtColor.Red = 255
                .DataPoints.Item(-1).Marker.Pen.VtColor.Blue = 255
                .DataPoints.Item(-1).Marker.Pen.VtColor.Green = 155
                End With
       End With
       MSChart1 = Graph ' populate chart's data grid using Graph array
       Me.WindowState = vbMaximized
End Sub
Private Sub Form_Resize()
    MSChart1.Move ScaleLeft, ScaleTop, ScaleWidth, ScaleHeight
End Sub

to adq

That Chart looks great!

The problem is to get data like 10-10-1993, 8.2 in the correct spot on the graph.

... Still working on it!

David

Are you waiting on a response from me?  

I have been working on this problem for some time now, and I think it can be done using MSChart. The problem is to plot a line chart with data where the dates are on a SCALED x-axis and the data points are on the y-axis. This causes an immense problem in MSChart, but not Excel, when some dates are missing.

The data might look something like this :

April 1 1945    1.2
April 3 1946    12
April 4 1985    13
May 13  2000    16.3
etc

MSChart will plot this data using equal intervals between dates; i.e using the dates as Labels and not on a scale.

I played around with both the MSChart 2dXY and 2dLine charts over the weekend and I think the problem can be worked around using some fairly elaborate code. I have developed a strategy for doing it, but the coding is going to be very tedious.

Any comments?

David

You do know that you can use Excel charting in your VB project, right?  

Also, assuming you don't want to use Excel charting, have you seen this?

http://support.microsoft.com/support/kb/articles/Q177/6/85.ASP

Here is a scaled date chart - don't have the axis labels though.  Is this the kind of thing you are trying to do?  

Dim MinDate As Date, MaxDate As Date
Dim D(1 To 4, 1 To 4) As Double

Private Sub Form_Load()
    MinDate = #1/1/1940#
    MaxDate = #12/31/2005#
    D(1, 1) = #4/1/1945#: D(1, 2) = 1.2
    D(2, 1) = #4/3/1946#: D(2, 2) = 12
    D(3, 1) = #4/4/1985#: D(3, 2) = 13
    D(4, 1) = #5/13/2000#: D(4, 2) = 16.3
   
    With MSChart1
           .chartType = VtChChartType2dXY  ' set to X Y Scatter chart
           .Plot.Axis(VtChAxisIdX).ValueScale.Auto = False
           .Plot.Axis(VtChAxisIdX).ValueScale.Minimum = MinDate
           .Plot.Axis(VtChAxisIdX).ValueScale.Maximum = MaxDate
           .Plot.UniformAxis = False
           With .Plot.SeriesCollection(1)
               .SeriesMarker.Show = True
               .SeriesMarker.Auto = False
               .DataPoints.Item(-1).Marker.Size = 200
               .DataPoints.Item(-1).Marker.Style = VtMarkerStyleDiamond
               .DataPoints.Item(-1).Marker.Pen.Width = 25
               .DataPoints.Item(-1).Marker.Pen.VtColor.Red = 255
               .DataPoints.Item(-1).Marker.Pen.VtColor.Blue = 255
               .DataPoints.Item(-1).Marker.Pen.VtColor.Green = 155
               End With
      End With
      MSChart1 = D ' populate chart's data grid using Graph array
      Me.WindowState = vbMaximized
End Sub

To adq

Managed to get the Date Axis!

How about the tick marks...?

Thanks

David


Dim MinDate As Date, MaxDate As Date
Dim D(1 To 4, 1 To 4) As Double

Private Sub Form_Load()
 
MinDate = 1945
MaxDate = 2002
   D(1, 1) = (1900 + (#4/1/1945# / 365)): D(1, 2) = 1.2
   D(2, 1) = (1900 + (#4/3/1946# / 365)): D(2, 2) = 12
   D(3, 1) = (1900 + (#4/4/1985#) / 365): D(3, 2) = 13
   D(4, 1) = (1900 + (#5/13/2000#) / 365): D(4, 2) = 16.3
 
   With MSChart1
          .chartType = VtChChartType2dXY  ' set to X Y Scatter chart
          .Plot.Axis(VtChAxisIdX).ValueScale.Auto = False
          .Plot.Axis(VtChAxisIdX).ValueScale.Minimum = MinDate
         .Plot.Axis(VtChAxisIdX).ValueScale.Maximum = MaxDate
         
         
         .Plot.UniformAxis = False
          With .Plot.SeriesCollection(1)
              .SeriesMarker.Show = True
              .SeriesMarker.Auto = False
              .DataPoints.Item(-1).Marker.Size = 200
              .DataPoints.Item(-1).Marker.Style = VtMarkerStyleDiamond
              .DataPoints.Item(-1).Marker.Pen.Width = 25
              .DataPoints.Item(-1).Marker.Pen.VtColor.Red = 255
              .DataPoints.Item(-1).Marker.Pen.VtColor.Blue = 255
              .DataPoints.Item(-1).Marker.Pen.VtColor.Green = 155
              End With
     End With
     MSChart1 = D ' populate chart's data grid using Graph array
     Me.WindowState = vbMaximized
End Sub

David, very good idea!  I added the following to get nice tick marks. I changed the mindate to make the tick marks come out even.  

MinDate = 1942
MaxDate = 2002

.Plot.Axis(VtChAxisIdX).ValueScale.MajorDivision = (MaxDate - MinDate) / 5
.Plot.Axis(VtChAxisIdX).AxisGrid.MajorPen.Style = VtPenStyleNull

To adq

Fabuloso!!

and I got another series on the same date scale as follows:

Dim MinDate As Date, MaxDate As Date
Dim D(1 To 4, 1 To 4) As Double

Private Sub Form_Load()
MinDate = 1942
MaxDate = 2002

 

   D(1, 1) = (1900 + (#4/1/1945# / 365)): D(1, 2) = 1.2
   D(2, 1) = (1900 + (#4/3/1946# / 365)): D(2, 2) = 12
   D(3, 1) = (1900 + (#4/4/1985#) / 365): D(3, 2) = 13
   D(4, 1) = (1900 + (#5/13/2000#) / 365): D(4, 2) = 16.3
 
  D(1, 3) = (1900 + (#4/1/1945# / 365)): D(1, 4) = 2.2
   D(2, 3) = (1900 + (#4/3/1946# / 365)): D(2, 4) = 14
   D(3, 3) = (1900 + (#4/4/1985#) / 365): D(3, 4) = 15
   D(4, 3) = (1900 + (#5/13/2000#) / 365): D(4, 4) = 19.3
 
 
 
   With MSChart1
          .chartType = VtChChartType2dXY  ' set to X Y Scatter chart
          .Plot.Axis(VtChAxisIdX).ValueScale.Auto = False
          .Plot.Axis(VtChAxisIdX).ValueScale.Minimum = MinDate
         .Plot.Axis(VtChAxisIdX).ValueScale.Maximum = MaxDate
         .Plot.Axis(VtChAxisIdX).ValueScale.MajorDivision = (MaxDate - MinDate) / 5
.Plot.Axis(VtChAxisIdX).AxisGrid.MajorPen.Style = VtPenStyleNull
         
         .Plot.UniformAxis = False
          With .Plot.SeriesCollection(1)
              .SeriesMarker.Show = True
              .SeriesMarker.Auto = False
              .DataPoints.Item(-1).Marker.Size = 50
              .DataPoints.Item(-1).Marker.Style = VtMarkerStyleDiamond
              .DataPoints.Item(-1).Marker.Pen.Width = 25
              .DataPoints.Item(-1).Marker.Pen.VtColor.Red = 255
              .DataPoints.Item(-1).Marker.Pen.VtColor.Blue = 0
              .DataPoints.Item(-1).Marker.Pen.VtColor.Green = 0
             
              End With
     
      With .Plot.SeriesCollection(3)
              .SeriesMarker.Show = True
              .SeriesMarker.Auto = False
              .DataPoints.Item(-1).Marker.Size = 50
              .DataPoints.Item(-1).Marker.Style = VtMarkerStyleDiamond
              .DataPoints.Item(-1).Marker.Pen.Width = 25
              .DataPoints.Item(-1).Marker.Pen.VtColor.Red = 0
              .DataPoints.Item(-1).Marker.Pen.VtColor.Blue = 255
              .DataPoints.Item(-1).Marker.Pen.VtColor.Green = 0
             
              End With
     
     
     
     
     
     End With
     MSChart1 = D ' populate chart's data grid using Graph array
     Me.WindowState = vbMaximized
End Sub

How many series can we get? The properties box seems to indicate a maximum of two, and your array logic seems to provide a maximum of two series as well.

Thanks..

David

To adq..

Got three series, but can the marker styles and line colours be set using code...?

The properties box seems to allow for only two series.

Do you want to continue on this? The original question has been answered in spades, Thanks to you!

If you want to go on, please let me know...


Thanks

David



Dim MinDate As Date, MaxDate As Date
Dim D(1 To 4, 1 To 6) As Double

Private Sub Form_Load()
MinDate = 1942
MaxDate = 2002

 

   D(1, 1) = (1900 + (#4/1/1945# / 365)): D(1, 2) = 1.2
   D(2, 1) = (1900 + (#4/3/1946# / 365)): D(2, 2) = 12
   D(3, 1) = (1900 + (#4/4/1985#) / 365): D(3, 2) = 13
   D(4, 1) = (1900 + (#5/13/2000#) / 365): D(4, 2) = 16.3
 
  D(1, 3) = (1900 + (#4/1/1945# / 365)): D(1, 4) = 2.2
   D(2, 3) = (1900 + (#4/3/1946# / 365)): D(2, 4) = 14
   D(3, 3) = (1900 + (#4/4/1985#) / 365): D(3, 4) = 15
   D(4, 3) = (1900 + (#5/13/2000#) / 365): D(4, 4) = 19.3
 
 
  D(1, 5) = (1900 + (#4/1/1945# / 365)): D(1, 6) = 4.2
   D(2, 5) = (1900 + (#4/3/1946# / 365)): D(2, 6) = 16
   D(3, 5) = (1900 + (#4/4/1985#) / 365): D(3, 6) = 17
   D(4, 5) = (1900 + (#5/13/2000#) / 365): D(4, 6) = 21.3
 
 
 
 
 
   With MSChart1
          .chartType = VtChChartType2dXY  ' set to X Y Scatter chart
          .Plot.Axis(VtChAxisIdX).ValueScale.Auto = False
          .Plot.Axis(VtChAxisIdX).ValueScale.Minimum = MinDate
         .Plot.Axis(VtChAxisIdX).ValueScale.Maximum = MaxDate
         .Plot.Axis(VtChAxisIdX).ValueScale.MajorDivision = (MaxDate - MinDate) / 5
.Plot.Axis(VtChAxisIdX).AxisGrid.MajorPen.Style = VtPenStyleNull
         
         .Plot.UniformAxis = False
          With .Plot.SeriesCollection(1)
              .SeriesMarker.Show = True
              .SeriesMarker.Auto = False
              .DataPoints.Item(-1).Marker.Size = 50
              .DataPoints.Item(-1).Marker.Style = VtMarkerStyleDiamond
              .DataPoints.Item(-1).Marker.Pen.Width = 25
              .DataPoints.Item(-1).Marker.Pen.VtColor.Red = 255
              .DataPoints.Item(-1).Marker.Pen.VtColor.Blue = 0
              .DataPoints.Item(-1).Marker.Pen.VtColor.Green = 0
             
              End With
     
      With .Plot.SeriesCollection(2)
              .SeriesMarker.Show = True
              .SeriesMarker.Auto = False
              .DataPoints.Item(-1).Marker.Size = 50
              .DataPoints.Item(-1).Marker.Style = VtMarkerStyleDiamond
              .DataPoints.Item(-1).Marker.Pen.Width = 25
              .DataPoints.Item(-1).Marker.Pen.VtColor.Red = 0
              .DataPoints.Item(-1).Marker.Pen.VtColor.Blue = 255
              .DataPoints.Item(-1).Marker.Pen.VtColor.Green = 0
             
              End With
     
     
     
     
     
     End With
     MSChart1 = D ' populate chart's data grid using Graph array
     Me.WindowState = vbMaximized
End Sub

To adq..

Sorry, I missed out some of the code in my excitement.. Here it is.

The markers are missing for one of the series, and I tried interchanging their colours on the three series, but only two of them show up....?


David
 

Dim MinDate As Date, MaxDate As Date
Dim D(1 To 4, 1 To 6) As Double

Private Sub Form_Load()
MinDate = 1942
MaxDate = 2002

 

   D(1, 1) = (1900 + (#4/1/1945# / 365)): D(1, 2) = 1.2
   D(2, 1) = (1900 + (#4/3/1946# / 365)): D(2, 2) = 12
   D(3, 1) = (1900 + (#4/4/1985#) / 365): D(3, 2) = 13
   D(4, 1) = (1900 + (#5/13/2000#) / 365): D(4, 2) = 16.3
 
  D(1, 3) = (1900 + (#4/1/1945# / 365)): D(1, 4) = 2.2
   D(2, 3) = (1900 + (#4/3/1946# / 365)): D(2, 4) = 14
   D(3, 3) = (1900 + (#4/4/1985#) / 365): D(3, 4) = 15
   D(4, 3) = (1900 + (#5/13/2000#) / 365): D(4, 4) = 19.3
 
 
  D(1, 5) = (1900 + (#4/1/1945# / 365)): D(1, 6) = 4.2
   D(2, 5) = (1900 + (#4/3/1946# / 365)): D(2, 6) = 26
   D(3, 5) = (1900 + (#4/4/1985#) / 365): D(3, 6) = 17
   D(4, 5) = (1900 + (#5/13/2000#) / 365): D(4, 6) = 21.3
 
 
 
 
 
   With MSChart1
          .chartType = VtChChartType2dXY  ' set to X Y Scatter chart
          .Plot.Axis(VtChAxisIdX).ValueScale.Auto = False
          .Plot.Axis(VtChAxisIdX).ValueScale.Minimum = MinDate
         .Plot.Axis(VtChAxisIdX).ValueScale.Maximum = MaxDate
         .Plot.Axis(VtChAxisIdX).ValueScale.MajorDivision = (MaxDate - MinDate) / 5
.Plot.Axis(VtChAxisIdX).AxisGrid.MajorPen.Style = VtPenStyleNull
         
         .Plot.UniformAxis = False
          With .Plot.SeriesCollection(1)
              .SeriesMarker.Show = True
              .SeriesMarker.Auto = False
              .DataPoints.Item(-1).Marker.Size = 50
              .DataPoints.Item(-1).Marker.Style = VtMarkerStyleDiamond
              .DataPoints.Item(-1).Marker.Pen.Width = 25
              .DataPoints.Item(-1).Marker.Pen.VtColor.Red = 0
              .DataPoints.Item(-1).Marker.Pen.VtColor.Blue = 0
              .DataPoints.Item(-1).Marker.Pen.VtColor.Green = 0
             
              End With
     
      With .Plot.SeriesCollection(2)
              .SeriesMarker.Show = True
              .SeriesMarker.Auto = False
              .DataPoints.Item(-1).Marker.Size = 500
              .DataPoints.Item(-1).Marker.Style = VtMarkerStyleDiamond
              .DataPoints.Item(-1).Marker.Pen.Width = 25
              .DataPoints.Item(-1).Marker.Pen.VtColor.Red = 0
              .DataPoints.Item(-1).Marker.Pen.VtColor.Blue = 255
              .DataPoints.Item(-1).Marker.Pen.VtColor.Green = 0
              End With
     
     With .Plot.SeriesCollection(3)
              .SeriesMarker.Show = True
              .SeriesMarker.Auto = False
              .DataPoints.Item(-1).Marker.Size = 50
              .DataPoints.Item(-1).Marker.Style = VtMarkerStyleDiamond
              .DataPoints.Item(-1).Marker.Pen.Width = 25
              .DataPoints.Item(-1).Marker.Pen.VtColor.Red = 255
              .DataPoints.Item(-1).Marker.Pen.VtColor.Blue = 255
              .DataPoints.Item(-1).Marker.Pen.VtColor.Green = 255
             
              End With
     
     
     
     End With
     MSChart1 = D ' populate chart's data grid using Graph array
     Me.WindowState = vbMaximized
End Sub

I've learned a lot about the MSCHART control in the past week thanks to you! I'll be glad to continue until you've got the chart you're looking for.  The chart did the same thing to me that it was doing to you; the markers didn't show on one of the series. I suspected that it was related the ColumnCount property but I had difficulty getting consistent results to verify it. I finally cleaned up the code a little bit and I don't know what I did but it seems to be working for me now.   Does it work for you?  

Dim MinDate As Date, MaxDate As Date

Dim D(1 To 4, 1 To 6) As Double
Dim serx As Series

Private Sub Form_Load()
MinDate = 1942
MaxDate = 2002

D(1, 1) = (1900 + (#4/1/1945# / 365)): D(1, 2) = 1.2
D(2, 1) = (1900 + (#4/3/1946# / 365)): D(2, 2) = 12
D(3, 1) = (1900 + (#4/4/1985#) / 365): D(3, 2) = 13
D(4, 1) = (1900 + (#5/13/2000#) / 365): D(4, 2) = 16.3

D(1, 3) = (1900 + (#4/1/1945# / 365)): D(1, 4) = 2.2
D(2, 3) = (1900 + (#4/3/1946# / 365)): D(2, 4) = 14
D(3, 3) = (1900 + (#4/4/1985#) / 365): D(3, 4) = 15
D(4, 3) = (1900 + (#5/13/2000#) / 365): D(4, 4) = 19.3

D(1, 5) = (1900 + (#4/1/1945# / 365)): D(1, 6) = 4.2
D(2, 5) = (1900 + (#4/3/1946# / 365)): D(2, 6) = 26
D(3, 5) = (1900 + (#4/4/1985#) / 365): D(3, 6) = 17
D(4, 5) = (1900 + (#5/13/2000#) / 365): D(4, 6) = 21.3

With MSChart1
    .chartType = VtChChartType2dXY
    .Plot.Axis(VtChAxisIdX).ValueScale.Auto = False
    .Plot.Axis(VtChAxisIdX).ValueScale.Minimum = MinDate
    .Plot.Axis(VtChAxisIdX).ValueScale.Maximum = MaxDate
    .Plot.Axis(VtChAxisIdX).ValueScale.MajorDivision = (MaxDate - MinDate) / 5
    .Plot.Axis(VtChAxisIdX).AxisGrid.MajorPen.Style = VtPenStyleNull
    .Plot.UniformAxis = False
    .ColumnCount = 6   ' ***********
End With

For Each serx In MSChart1.Plot.SeriesCollection
    With serx
        .SeriesMarker.Show = True
        .SeriesMarker.Auto = False
        .DataPoints(-1).Marker.Visible = True
        .DataPoints(-1).Marker.Size = 50
        .DataPoints(-1).Marker.Style = VtMarkerStyleDiamond
        .DataPoints(-1).Marker.Pen.Width = 25
    End With
Next serx

With MSChart1.Plot
    .SeriesCollection(1).Pen.VtColor.Set 255, 0, 0
    .SeriesCollection(1).DataPoints(-1).Marker.Pen.VtColor.Set 0, 255, 255
    .SeriesCollection(3).Pen.VtColor.Set 0, 255, 0
    .SeriesCollection(3).DataPoints(-1).Marker.Pen.VtColor.Set 255, 0, 255
    .SeriesCollection(5).Pen.VtColor.Set 0, 0, 255
    .SeriesCollection(5).DataPoints(-1).Marker.Pen.VtColor.Set 255, 255, 0
End With

MSChart1 = D ' populate chart's data grid using Graph array
Me.WindowState = vbMaximized
End Sub

to adq..

Yes, that worked a treat!

This is FAR better than I had hoped. I must have spent a couple of days looking through MSDN. I want to select the colours for the lines to match the points. Can you help me with coordinating the line and marker colors?
I would like to have a legend to label each line on the chart. I think this would be easiest using coloured text boxes and labels next to the Chart.

I don't understand one of the one of the code operations:

"D(1, 1) = (1900 + (#4/1/1945# / 365)): D(1, 2) = 1.2"

I interpret the operations as follows:

D(1,1)  is point#1 "y" coordinate = 1.2
D(2,1)  is point#2 "y" coordinate = 12

D(1,2)  is point#1 "x" coordinate = (1900 + (#4/1/1945# / 365)) = number of years along x-axis.
D(2,2)  is point#2 "x" coordinate = (1900 + (#4/3/1946# / 365)) = number of years on x-axis

But what is the ":" doing??

Why do we get LINES out of this.. I would have thought we would have displayed a scatter plot...

Thanks

David

   

to adq..

Please don't work on the legend requirement unless you think this can be native to MSChart.

I can work on the code for the text box and labels easily enough..

David
 

to adq..

I figured out the ":"

It's just a line separator (Right?)

So I understand the rest of the nomenclature now!

Still don't understand why we don't get a scatter plot!

David

You're right about the colon being a line separator.  I don't know why I do that.  It isn't as if I'm being charged by the line.  

Regarding making the marker and line colors the same, I think you just set them to the same values.  We are explictly specifying the red,green,blue color triplet in our code for both the marker and the line. e.g., Set 255,0,0 is full red, no blue, no green. Set 0,255,255 is no red, full blue and full green which gives cyan. I'm not sure if that is your question - please let me know.

It is a scatter plot!  But since we have given the data points in ascending order it "simulates" a line plot.  Try rearranging the order of the dates in the array and you'll see the line go haywire.  If you don't want the line at all, it can be removed by setting the pen style to null.  Let me know if you need the code.  

Mschart does have a bunch of legend properties.  Here is some code that puts a simple legend to the left of the chart.  

.Legend.Location.Visible = True
.Legend.Location.LocationType = VtChLocationTypeLeft

.SeriesCollection(1).LegendText = "Blondes"
.SeriesCollection(3).LegendText = "Brunettes"
.SeriesCollection(5).LegendText = "Redheads"

adq..
You're good at this!
It works fine, and of course you don't need a common date axis, as you can see.

I want to link this thing to database array, and I wonder what the code would be for the #4/1/1945# part. This has to be obtained in days since 1900  
This works, but is it really the best way?
'D(1, 1) = (1900 + (Int(DateSerial(1945, 4, 1)) / 365))

Anyway, here is the complete thing so far... It really is something!!

David


Dim MinDate As Date, MaxDate As Date

Dim D(1 To 4, 1 To 6) As Double
Dim serx As Series

Private Sub Form_Load()
MinDate = 1942
MaxDate = 2002



D(1, 1) = (1900 + (#4/1/1943# / 365))
D(1, 2) = 1.2
D(2, 1) = (1900 + (#4/3/1946# / 365))
D(2, 2) = 12
D(3, 1) = (1900 + (#4/4/1985#) / 365)
D(3, 2) = 13
D(4, 1) = (1900 + (#5/13/2000#) / 365)
D(4, 2) = 16.3

D(1, 3) = (1900 + (#4/1/1945# / 365)): D(1, 4) = 2.2
D(2, 3) = (1900 + (#4/3/1946# / 365)): D(2, 4) = 14
D(3, 3) = (1900 + (#4/4/1985#) / 365): D(3, 4) = 15
D(4, 3) = (1900 + (#5/13/2000#) / 365): D(4, 4) = 19.3

D(1, 5) = (1900 + (#4/1/1945# / 365)): D(1, 6) = 4.2
D(2, 5) = (1900 + (#4/3/1946# / 365)): D(2, 6) = 26
D(3, 5) = (1900 + (#4/4/1985#) / 365): D(3, 6) = 17
D(4, 5) = (1900 + (#5/13/2000#) / 365): D(4, 6) = 21.3

With MSChart1
   .chartType = VtChChartType2dXY
   .Plot.Axis(VtChAxisIdX).ValueScale.Auto = False
   .Plot.Axis(VtChAxisIdX).ValueScale.Minimum = MinDate
   .Plot.Axis(VtChAxisIdX).ValueScale.Maximum = MaxDate
   .Plot.Axis(VtChAxisIdX).ValueScale.MajorDivision = (MaxDate - MinDate) / 5
   .Plot.Axis(VtChAxisIdX).AxisGrid.MajorPen.Style = VtPenStyleNull
   .Plot.UniformAxis = False

.Legend.Location.Visible = True
.Legend.Location.LocationType = VtChLocationTypeLeft


   .ColumnCount = 6   ' ***********
End With

For Each serx In MSChart1.Plot.SeriesCollection
   With serx
       
   
       
       .SeriesMarker.Show = True
       .SeriesMarker.Auto = False
       .DataPoints(-1).Marker.Visible = True
       .DataPoints(-1).Marker.Size = 50
       .DataPoints(-1).Marker.Style = VtMarkerStyleDiamond
       .DataPoints(-1).Marker.Pen.Width = 25
   End With
Next serx

With MSChart1.Plot

.SeriesCollection(1).LegendText = "Blondes"
.SeriesCollection(3).LegendText = "Brunettes"
.SeriesCollection(5).LegendText = "Redheads"

   .SeriesCollection(1).Pen.VtColor.Set 0, 255, 255
   .SeriesCollection(1).DataPoints(-1).Marker.Pen.VtColor.Set 0, 255, 255

   .SeriesCollection(3).Pen.VtColor.Set 255, 0, 255
   .SeriesCollection(3).DataPoints(-1).Marker.Pen.VtColor.Set 255, 0, 255
   .SeriesCollection(5).Pen.VtColor.Set 0, 0, 255
   .SeriesCollection(5).DataPoints(-1).Marker.Pen.VtColor.Set 255, 255, 0
End With

MSChart1 = D ' populate chart's data grid using Graph array
Me.WindowState = vbMaximized
End Sub

Oops, you just lost me - I know very little about databases.  I don't even know what a "database array" is.  I have used ADO recordsets a little - is that similar?  What database are you using? I suspect that I won't be able to offer you much help in connecting your chart to a database - I'm very sorry!
 

To adq..

I don't think I ought to elaborate this topic to include extending the link to a database. Perhaps we could
just clear up a problem I'm having with the date literal. Is there a VB function which will convert a date into the number of days since 1900? If a variable "Date1" is already set as a date in (say) myArray, how do you get it changed to the number of days?
This will be needed to get the data from myArray into the following line:

D(1, 1) = (1900 + (#4/1/1943# / 365))

I've increased the points to 300 and I am very grateful for your help!

David

to adq..

How do you set the pen style to show a true scatter plot?

David

Really worthwhile collaboration on this!

Thanks -I enjoyed it and learned alot too.