MSChart Data

[norason]

I have a string:

ChartDataStr = "aa,\21,22,23,34,44,45,55,64,73,82,91,21\" _

& "0,2,4,6,8,10,10,8,6,4,2,0\" _

& "0,4,6,8,10,12,12,10,8,6,4,0\" _

& "0,6,8,10,12,14,14,12,10,8,6,0\" _

& "0,8,10,12,14,16,16,14,12,10,8,0\" _

& "0,10,12,14,16,18,18,16,14,12,10,0\" _

& "0,12,14,16,18,20,20,18,16,14,12,0\" _

& "0,14,16,18,20,22,22,20,18,16,14,0\" _

& "0,16,18,20,22,24,24,22,20,18,16,0\" _

& "0,18,20,22,24,26,26,24,22,20,18,0\bb"



That represents several charts, in this case 10, that I can parse into individual charts into an Chartsarr( ) array where each has the form:



21,22,23,34,44,45,55,64,73,82,91,21



I can then select the chart desired with ChartDesired - Chartsarr(ChartDesired)



I can parse each chart by replacing the ,’s with a space, and place the individual value of data points into another array – ChartPointPointarr( ).



What I can’t figure out is how to get the Chartsarr(ChartDesired) - ChartPointPointarr( )

Into the MSChart:



.ChartData = Chartsarr(ChartDesired)?????





Any help would be appreciated.



Thanks,



Gary



Public Sub CmdGetCharts_Click()

Dim ChartDataStr As String

ChartDataStr = "aa,\21,22,23,34,44,45,55,64,73,82,91,21\" _

& "0,2,4,6,8,10,10,8,6,4,2,0\" _

& "0,4,6,8,10,12,12,10,8,6,4,0\" _

& "0,6,8,10,12,14,14,12,10,8,6,0\" _

& "0,8,10,12,14,16,16,14,12,10,8,0\" _

& "0,10,12,14,16,18,18,16,14,12,10,0\" _

& "0,12,14,16,18,20,20,18,16,14,12,0\" _

& "0,14,16,18,20,22,22,20,18,16,14,0\" _

& "0,16,18,20,22,24,24,22,20,18,16,0\" _

& "0,18,20,22,24,26,26,24,22,20,18,0\bb"

ChartDataLen = Len(ChartDataStr) 'Length of the Chart Data string

ChartsQty = "\"

If Len(ChartsQty) Then

ChartsCount = UBound(Split(ChartDataStr, ChartsQty)) 'Find out how many \ there are

End If

LblChartsAvailable.Caption = ChartsCount - 1

ChartCharStartPos = 5 'this is the position after the header

ReDim Chartsarr(1 To ChartsCount) 'set the number of arrays to the number of charts counted

For MakeCharts = 1 To (ChartsCount - 1) 'Calculate the number of charts

ChartCharPos = InStr(ChartCharStartPos + 2, ChartDataStr, "\") 'find the first "\" - get the string up to that position, then set the search to that position plus 1

ChartQtyToRead = ChartCharPos - ChartCharStartPos

Chartsarr(MakeCharts) = Mid$(ChartDataStr, ChartCharStartPos, ChartQtyToRead)

CommaCount = UBound(Split(Chartsarr(MakeCharts), ",")) 'count the commas

ChartPointQty = CommaCount + 2

Chartsarr(MakeCharts) = Replace$(Chartsarr(MakeCharts), ",", " ") 'replace commas with spaces



' **************** this will separate each data point into an individual graph point *****************

ReDim ChartPointPointarr(1 To ChartPointQty) 'set the number of point arrays to the number of points counted

ChartPointStartPos = 1

ChartPointLen = Len(Chartsarr(MakeCharts))

ChartLastPos = InStrRev(Chartsarr(MakeCharts), " ")

For MakePoints = 1 To (ChartPointQty - 1) 'Calculate the number of points

ChartPointPos = InStr(ChartPointStartPos, Chartsarr(MakeCharts), " ") 'find the first " " - get the string up to that position, then set the search to that position plus 1

UseThisData = Chartsarr(MakeCharts)

ChartPointQtyToRead = ChartPointPos - ChartPointStartPos

If ChartPointStartPos >= ChartLastPos Then

ChartPointStartPos = ChartLastPos + 1

ChartPointQtyToRead = ChartPointLen - ChartLastPos

End If

ChartPointPointarr(MakePoints) = Val(Mid$(UseThisData, ChartPointStartPos, ChartPointQtyToRead))

ChartPointStartPos = ChartPointStartPos + ChartPointQtyToRead + 1

Next MakePoints

ChartCharStartPos = ChartCharPos + 1

Next MakeCharts

End Sub









Private Sub cmdDrawGraph_Click()

If ChartDesired = 0 Then

MsgBox "Select Graph", vbExclamation, "Select Graph"

Exit Sub

End If

With MSChart1

.ChartType = VtChChartType2dLine

.ShowLegend = True

.Plot.Axis(VtChAxisIdX).Labels(1).VtFont.Name = ariel

.Plot.Axis(VtChAxisIdX).Labels(1).VtFont.Size = 12

.Plot.Axis(VtChAxisIdX).Labels(1).VtFont.Style = Bold

.Plot.Axis(VtChAxisIdX).Labels(1).VtFont.VtColor.Set 204, 204, 204

.TitleText = "FLOW GRAPH"

.ChartData = Chartsarr(ChartDesired)

With .DataGrid

.SetSize 1, 7, 125, 7 '(rowLabelCount, columnLabelCount, dataRowCount, columnLabelCount)

Dim intIndex As Integer '*******************

MSChart1.RowCount = 125

End With

With .Plot

With .Axis(VtChAxisIdY).ValueScale

.Auto = False

.Minimum = 0

.Maximum = 100

End With

With .Axis(VtChAxisIdX).AxisGrid

.MajorPen.VtColor.Set 204, 204, 204

.MajorPen.Width = 2

.MinorPen.VtColor.Set 204, 204, 204

.MinorPen.Width = 2

End With

With .Axis(VtChAxisIdY).AxisGrid

.MajorPen.VtColor.Set 204, 204, 204

.MajorPen.Width = 2

.MinorPen.VtColor.Set 204, 204, 204

.MinorPen.Width = 2

End With

With .Item(1).DataPoints(-1) 'controls text for flow line - grid too small - can't see the text

.DataPointLabel.LocationType = VtChLabelLocationTypeAbovePoint

.DataPointLabel.VtFont.VtColor.Set 0, 0, 0 '# black text

.Brush.FillColor.Set 255, 0, 0 '# red line

End With

With .Item(2).DataPoints(-1)

.DataPointLabel.VtFont.VtColor.Set 0, 102, 255 '# blue

.Brush.FillColor.Set 0, 102, 255

End With

With .Item(3).DataPoints(-1)

.DataPointLabel.VtFont.VtColor.Set 0, 102, 255 '# blue

.Brush.FillColor.Set 0, 102, 255

End With

With .Item(4).DataPoints(-1)

.DataPointLabel.VtFont.VtColor.Set 0, 204, 0 '# green

.Brush.FillColor.Set 0, 204, 0

End With

With .Item(5).DataPoints(-1)

.DataPointLabel.VtFont.VtColor.Set 204, 204, 204 '# green

.Brush.FillColor.Set 0, 204, 0

End With

With .Item(6).DataPoints(-1)

.DataPointLabel.VtFont.VtColor.Set 205, 153, 0 '# brown

.Brush.FillColor.Set 205, 153, 0

End With

With .Item(7).DataPoints(-1)

.DataPointLabel.VtFont.VtColor.Set 205, 153, 0 '# brown

.Brush.FillColor.Set 205, 153, 0

End With

End With '#seriescoll

End With '# plot

End With '#chart

End Sub

 

[HarleyQuinn]

Why not skip all the replacing with spaces etc and just use:

.ChartData = Split(Chartsarr(ChartDesired),",")

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[norason]

That works for the first point, but I need to show the array of points.


Thanks,

Gary

 

[HarleyQuinn]

Do you want to display all the points for a particular chart (1 - 10) or all the points for all the charts?

If for example Chartsarr(ChartDesired) returned the value: 0,2,4,6,8,10,10,8,6,4,2,0 then Split() ing that should return all 12 points to the .ChartData property.

What is it returning in your code?

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[norason]

Chartsarr(ChartDesired) does return:

21 22 23 24 44 45 55 64 73 82 91 21

for Chart 1 and

2 4 6 8 10 10 8 6 4 2 0

for Chart 2...

But it doesn't draw a graph, it just shows 21 on the left side of the graph.

Eventually I need to show 10 graphs, but I'm just focussing on one for now.

Thanks,

Gary

 

[HarleyQuinn]

Hmm, doesn't draw lines using that type of graph for me either.

However, passing in values using a 2d array (as per the helpfile for .ChartData) does put the values onto my graph.

Perhaps that would be worth looking into for you?

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[norason]

Sorry I'm such a newbie, but I don't understand how to get Split(Chartsarr(ChartDesired),",") into the .ChartData.

Thanks,

Gary

 

[dilettante]

MSChart normally expects a 2D table of values, like:

X    Y1   Y2   Y3
Jan  23   2    13
Feb  31   14   20
Mar  29   10   19

If you are plotting just one Y-series you have two columns of data: X and Y.

I haven't seen anywhere above where you are setting any X values.

 

[norason]

I got it working! Basically, I changed the logic to read the Data String first, break the string down to the individual charts, then when requested, call up the chart, and break out each point.

Thanks for the help!

Gary


Here's the code:

Private Sub cmdDrawGraph_Click()
If ChartDesired = 0 Then
MsgBox "Select Graph", vbExclamation, "Select Graph"
Exit Sub
End If
Dim arr() As Integer
ReDim Dispensearr(1 To 125, 1 To 10) As Variant
CommaCount = UBound(Split(Chartsarr(ChartDesired), ","))
ChartPointQty = CommaCount + 2
ReDim Dispensearr(1 To 125, 1 To 10)
' **************** this will separate each data point of the selected chart into an individual graph point *****************
ReDim ChartPointPointarr(1 To ChartPointQty)
ChartPointStartPos = 1
ChartPointLen = Len(Chartsarr(ChartDesired))
ChartLastPos = InStrRev(Chartsarr(ChartDesired), ",")
MakePoints = 1
UseThisData = Chartsarr(ChartDesired)
For MakePoints = 1 To (ChartPointQty - 1)
ChartPointPos = InStr(ChartPointStartPos, Chartsarr(ChartDesired), ",")
ChartPointQtyToRead = ChartPointPos - ChartPointStartPos
If ChartPointStartPos >= ChartLastPos Then
ChartPointStartPos = ChartLastPos + 1
ChartPointQtyToRead = ChartPointLen - ChartLastPos
End If
ChartPointPointarr(MakePoints) = Val(Mid$(UseThisData, ChartPointStartPos, ChartPointQtyToRead))
ChartPointStartPos = ChartPointStartPos + ChartPointQtyToRead + 1
ChartPointQtyToRead = ChartPointPos - ChartPointStartPos
Dispensearr(MakePoints, 1) = ChartPointPointarr(MakePoints)
Next MakePoints
With MSChart1
.ChartType = VtChChartType2dLine
.ShowLegend = True
.Plot.Axis(VtChAxisIdX).Labels(1).VtFont.Name = ariel
.Plot.Axis(VtChAxisIdX).Labels(1).VtFont.Size = 12
.Plot.Axis(VtChAxisIdX).Labels(1).VtFont.Style = Bold
.Plot.Axis(VtChAxisIdX).Labels(1).VtFont.VtColor.Set 204, 204, 204
.TitleText = "FLOW GRAPH"
.ChartData = Dispensearr
With .DataGrid
.SetSize 1, 10, 125, 10 '(rowLabelCount, columnLabelCount, dataRowCount, columnLabelCount)
Dim intIndex As Integer
MSChart1.RowCount = 125
End With
With .Plot
With .Axis(VtChAxisIdY).ValueScale
.Auto = False
.Minimum = 0
.Maximum = 100
End With
With .Axis(VtChAxisIdX).AxisGrid
.MajorPen.VtColor.Set 204, 204, 204
.MajorPen.Width = 2
.MinorPen.VtColor.Set 204, 204, 204
.MinorPen.Width = 2
End With
With .Axis(VtChAxisIdY).AxisGrid
.MajorPen.VtColor.Set 204, 204, 204
.MajorPen.Width = 2
.MinorPen.VtColor.Set 204, 204, 204
.MinorPen.Width = 2
End With
.Item(2).Pen.Style = VtPenStyleDitted

.Item(4).Pen.Style = VtPenStyleDitted

.Item(5).Pen.Style = VtPenStyleDashed

.Item(6).Pen.Style = VtPenStyleDitted

.Item(7).Pen.Style = VtPenStyleDashed

.Item(8).Pen.Style = VtPenStyleDitted

.Item(9).Pen.Style = VtPenStyleDashed

.Item(10).Pen.Style = VtPenStyleSolid
With .Item(1).DataPoints(-1)
.DataPointLabel.LocationType = VtChLabelLocationTypeAbovePoint
.DataPointLabel.VtFont.VtColor.Set 0, 0, 0
.Brush.FillColor.Set 255, 0, 0
End With
With .Item(2).DataPoints(-1)
.DataPointLabel.VtFont.VtColor.Set 0, 102, 255
.Brush.FillColor.Set 0, 102, 255
End With
With .Item(3).DataPoints(-1)
.DataPointLabel.VtFont.VtColor.Set 0, 102, 255
.Brush.FillColor.Set 0, 102, 255
End With
With .Item(4).DataPoints(-1)
.DataPointLabel.VtFont.VtColor.Set 0, 204, 0
.Brush.FillColor.Set 0, 204, 0
End With
With .Item(5).DataPoints(-1)
.DataPointLabel.VtFont.VtColor.Set 204, 204, 204
.Brush.FillColor.Set 0, 204, 0
End With
With .Item(6).DataPoints(-1)
.DataPointLabel.VtFont.VtColor.Set 205, 153, 0
.Brush.FillColor.Set 205, 153, 0
End With
With .Item(7).DataPoints(-1)
.DataPointLabel.VtFont.VtColor.Set 205, 153, 0
.Brush.FillColor.Set 205, 153, 0
End With
With .Item(10).DataPoints(-1)
.DataPointLabel.VtFont.VtColor.Set 0, 153, 0
.Brush.FillColor.Set 0, 153, 0
End With
End With
End With
End With
End Sub

Public Sub CmdGetCharts_Click()
Dim ChartDataStr As String
ChartDataStr = "aa,\221,22,23,34,44,45,55,64,73,82,91,21\" _

& "0,2,4,6,8,10,10,8,6,4,2,0\" _

& "0,4,6,8,10,12,12,10,8,6,4,0\" _

& "0,6,8,10,12,14,14,12,10,8,6,0\" _

& "0,8,10,12,14,16,16,14,12,10,8,0\" _

& "0,10,12,14,16,18,18,16,14,12,10,0\" _

& "0,12,14,16,18,20,20,18,16,14,12,0\" _

& "0,14,16,18,20,22,22,20,18,16,14,0\" _

& "0,16,18,20,22,24,24,22,20,18,16,0\" _

& "0,18,20,22,24,26,26,24,22,20,18,0\bb"

ChartDataLen = Len(ChartDataStr) 'Length of the Chart Data string
ChartsQty = "\"
If Len(ChartsQty) Then
ChartsCount = UBound(Split(ChartDataStr, ChartsQty)) 'Find out how many \ there are
End If
LblChartsAvailable.Caption = ChartsCount - 1

ChartCharStartPos = 5
ReDim Chartsarr(1 To ChartsCount)
For MakeCharts = 1 To (ChartsCount - 1)
ChartCharPos = InStr(ChartCharStartPos + 2, ChartDataStr, "\")
ChartQtyToRead = ChartCharPos - ChartCharStartPos
Chartsarr(MakeCharts) = Mid$(ChartDataStr, ChartCharStartPos, ChartQtyToRead)
ChartCharStartPos = ChartCharPos + 1
Next MakeCharts
End Sub