Solved

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

Posted on 2001-08-20
40
2,956 Views
Last Modified: 2012-05-04
MSChart produces 2D Line charts, but is there a way to display data points (for example as circles, crosses, or squares)on each line? Also, can someone help with code to get the data displayed with an x-axis time scale, with tick marks for each year, ending with the current year?
0
Comment
Question by:DrD
  • 19
  • 19
  • +1
40 Comments
 
LVL 3

Expert Comment

by:adg
ID: 6408675
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
0
 
LVL 1

Expert Comment

by:rskathait
ID: 6408678
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
0
 
LVL 3

Expert Comment

by:adg
ID: 6408792
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
0
 

Expert Comment

by:nickwoolley
ID: 6409351
listening...
0
 

Author Comment

by:DrD
ID: 6410659
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  
0
 
LVL 3

Expert Comment

by:adg
ID: 6411078
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
0
 

Author Comment

by:DrD
ID: 6411894
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
0
 
LVL 3

Expert Comment

by:adg
ID: 6411984
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!
0
 
LVL 3

Expert Comment

by:adg
ID: 6420469
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
0
 

Author Comment

by:DrD
ID: 6422837
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
0
 
LVL 3

Expert Comment

by:adg
ID: 6423094
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?



0
 

Author Comment

by:DrD
ID: 6423165
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...
0
 
LVL 3

Expert Comment

by:adg
ID: 6423452
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?    




0
 

Author Comment

by:DrD
ID: 6423673
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



 
0
 

Author Comment

by:DrD
ID: 6423796
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
0
 
LVL 3

Expert Comment

by:adg
ID: 6423900
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!
0
 
LVL 3

Expert Comment

by:adg
ID: 6424041
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
0
 

Author Comment

by:DrD
ID: 6424065
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
0
 
LVL 3

Expert Comment

by:adg
ID: 6434103
Are you waiting on a response from me?  
0
 

Author Comment

by:DrD
ID: 6434586
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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Expert Comment

by:adg
ID: 6434866
You do know that you can use Excel charting in your VB project, right?  
0
 
LVL 3

Expert Comment

by:adg
ID: 6434878
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
0
 
LVL 3

Expert Comment

by:adg
ID: 6434989
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
0
 

Author Comment

by:DrD
ID: 6435298

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
0
 
LVL 3

Expert Comment

by:adg
ID: 6435548
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
0
 

Author Comment

by:DrD
ID: 6437402
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

0
 

Author Comment

by:DrD
ID: 6437454
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
0
 

Author Comment

by:DrD
ID: 6437521

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
0
 
LVL 3

Expert Comment

by:adg
ID: 6438796
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
0
 

Author Comment

by:DrD
ID: 6439053
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

   
0
 

Author Comment

by:DrD
ID: 6439056
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
 
0
 

Author Comment

by:DrD
ID: 6439077
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
0
 
LVL 3

Expert Comment

by:adg
ID: 6439305
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"
0
 

Author Comment

by:DrD
ID: 6439432

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
0
 
LVL 3

Expert Comment

by:adg
ID: 6439550
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!
 
0
 

Author Comment

by:DrD
ID: 6439662
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




0
 

Author Comment

by:DrD
ID: 6439668
to adq..

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

David
0
 
LVL 3

Accepted Solution

by:
adg earned 300 total points
ID: 6442799
Hi David, I'm really glad this question worked out so well.

The code to remove the lines is .Pen.Style = VtPenStyleNull.  

I hope I understood your date question correctly.  If so, the code to get the number of days between 4/1/1943 and 1/1/1900 would be Cdbl(#4/1/1943#-#1/1/1900#).  Cdbl is a function that converts variables to type double.
0
 

Author Comment

by:DrD
ID: 6454559
Really worthwhile collaboration on this!
0
 
LVL 3

Expert Comment

by:adg
ID: 6455655
Thanks -I enjoyed it and learned alot too.  
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now