philcrouch
asked on
MSChart question 2
Ok i have figured out that i need an xy scatter chart to display the information i have (i think)
I have some data stored in my database that run behind my VB front end. For simplicity sake i will
give explain only fields required.
RA# - agreement Number
StartDate - start date agreememnt
EndDate - end date of agreement
now i have worked out how to display this information out so that i get a visual of the RA# and the dates it happens.
my problem is that the ra# themselves can range from 1 - 10000 (or whatever)
if i was to try and display RA# 20 and 700 on the same chart, it would be so small that you could not see the result
is there a way to just spread the ra# up the y axis evenly and the lable what they are?
do you uinderstand or am i confused myself?
thanks in advance
I have some data stored in my database that run behind my VB front end. For simplicity sake i will
give explain only fields required.
RA# - agreement Number
StartDate - start date agreememnt
EndDate - end date of agreement
now i have worked out how to display this information out so that i get a visual of the RA# and the dates it happens.
my problem is that the ra# themselves can range from 1 - 10000 (or whatever)
if i was to try and display RA# 20 and 700 on the same chart, it would be so small that you could not see the result
is there a way to just spread the ra# up the y axis evenly and the lable what they are?
do you uinderstand or am i confused myself?
thanks in advance
ping
This is a link to microsofts online book about vba it might help
http://www.microsoft.com/officedev/articles/Opg/toc/pgtoc.htm
http://www.microsoft.com/officedev/articles/Opg/toc/pgtoc.htm
Hi philcrouch, I'm surprised to see you post this as an additional 200 point question. I would have posted it with zero points and a reference to https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20184017
Does this mean we are done with the previous question?
Does this mean we are done with the previous question?
Thanks for accepting my answer on the previous question! You can use Excel charting from your VB program (assuming you have Excel). Are you interested in that approach or do you want to stick with mschart?
ASKER
if i can use excel chart that would be fine
it is just i need to get my data from the databse, not static information on a excel worksheet.
or is there a way to put the recordset informatoin onto the datasheet?
thanks for your continued help
it is just i need to get my data from the databse, not static information on a excel worksheet.
or is there a way to put the recordset informatoin onto the datasheet?
thanks for your continued help
You are referring to an ADO recordset, right?
Do you have the recordset already working with the mschart control?
Do you have the recordset already working with the mschart control?
ASKER
yes i do mean an ado recordset
i do not have it working, but i can easily put it into the garray formular that you had written down for me.
the query is not the problem, it is how to put that information into the excel sheet
thanks
i do not have it working, but i can easily put it into the garray formular that you had written down for me.
the query is not the problem, it is how to put that information into the excel sheet
thanks
OK, I did something a little weird but it seems to work. I haven't yet figured out how to change the Y-axis labels so I created a set of "invisible" datapoints that are in the same position as the first datapoint in the bar. Then I used a label off that. I'm not sure if that is OK for you but I think it looks pretty good (the chart, not the program).
Also, I'm not sure if this is applicable to your situation but I've added logic to change the color of the bar depending on the number of overlapping RA's. A slider control sets the overlap threshold so you'll need to add a slider control to your form for this to work. Use the right and left arrow keys to change the slider and the bars should change color when the threshold is crossed.
Option Explicit
' garray(Datapoints,series)
Dim garray(1 To 2, 1 To 60)
Dim DayCount(1 To 31)
Dim RaLegend(1 To 15)
Const BeginCol = 1
Const EndCol = 2
Private Sub Form_Load()
Dim day As Integer, ra As Integer
Randomize Timer
With MSChart1
.chartType = VtChChartType2dXY
' .AllowSelections = False
.Plot.Axis(VtChAxisIdX).Va lueScale.M inimum = 1
.Plot.Axis(VtChAxisIdX).Va lueScale.M aximum = 31
.Plot.Axis(VtChAxisIdY).Ax isScale.Hi de = True
.Plot.Wall.Brush.FillColor .Set 150, 150, 200 ' blue
.Plot.Wall.Brush.Style = VtBrushStyleSolid
End With
'
For ra = 1 To 15 ' generate test data
RaLegend(ra) = "RA#" + CStr(Int(Rnd * 8000))
garray(BeginCol, ra * 2 - 1) = Int(Rnd * 31) + 1
garray(BeginCol, ra * 2) = ra
garray(EndCol, ra * 2 - 1) = garray(BeginCol, ra * 2 - 1) + Int(Rnd * 10) + 1
If garray(EndCol, ra * 2 - 1) > 31 Then garray(EndCol, ra * 2 - 1) = 31
garray(EndCol, ra * 2) = ra
' create datapoints used for labelling only - no line is displayed
garray(BeginCol, ra * 2 - 1 + 30) = garray(BeginCol, ra * 2 - 1)
garray(BeginCol, ra * 2 + 30) = ra
garray(EndCol, ra * 2 - 1 + 30) = garray(BeginCol, ra * 2 - 1) ' end point same as begin point so no line
garray(EndCol, ra * 2 + 30) = ra
'Debug.Print ra, garray(BeginCol, ra * 2 - 1), garray(EndCol, ra * 2 - 1)
Next ra
'
For day = 1 To 31 ' zero day counts (number of concurrent RA's for each day)
DayCount(day) = 0
Next day
For day = 1 To 31 ' sum number of concurrent RA's for each day
For ra = 1 To 15 ' for each ra
If ((garray(BeginCol, ra * 2 - 1) <= day) And _
(garray(EndCol, ra * 2 - 1) >= day)) Then
DayCount(day) = DayCount(day) + 1
End If
Next ra
'Debug.Print day, DayCount(day)
Next day
'
Me.WindowState = vbMaximized
Slider1.Min = 1
Slider1.Max = 15
Slider1.Value = 4 ' causes chart to be drawn
End Sub
Private Sub DrawChart()
Dim day As Integer, ra As Integer
Dim ExceedsThreshold As Boolean
MSChart1 = garray
' scan each ra to identify if it contains any days that are over the threshold
' and color accordingly
For ra = 1 To 15
MSChart1.Plot.SeriesCollec tion(ra * 2 - 1).Pen.Width = 100 ' set "bar" width
' set "bar" color
ExceedsThreshold = False
For day = garray(BeginCol, ra * 2 - 1) To garray(EndCol, ra * 2 - 1)
If DayCount(day) > Slider1.Value Then
ExceedsThreshold = True
End If
Next day
If ExceedsThreshold Then
MSChart1.Plot.SeriesCollec tion(ra * 2 - 1).Pen.VtColor.Set 200, 30, 30 ' red
Else
MSChart1.Plot.SeriesCollec tion(ra * 2 - 1).Pen.VtColor.Set 30, 200, 30 ' green
End If
'set the "bar" labels
With MSChart1.Plot.SeriesCollec tion(ra * 2 - 1 + 30)
.LegendText = RaLegend(ra)
With .DataPoints(-1).DataPointL abel
.LocationType = VtChLabelLocationTypeLeft
.Custom = True
.Component = VtChLabelComponentSeriesNa me
.VtFont.VtColor.Set 240, 255, 255
End With
End With
Next ra
End Sub
Private Sub Form_Resize()
If Me.WindowState = vbMinimized Then Exit Sub
Slider1.Move Me.ScaleLeft, Me.ScaleHeight - Slider1.Height - 200, Me.ScaleWidth
MSChart1.Move Me.ScaleLeft, Me.ScaleTop, Me.ScaleWidth, Me.ScaleHeight - Slider1.Height - 300
End Sub
Private Sub Slider1_Change()
DrawChart
End Sub
Also, I'm not sure if this is applicable to your situation but I've added logic to change the color of the bar depending on the number of overlapping RA's. A slider control sets the overlap threshold so you'll need to add a slider control to your form for this to work. Use the right and left arrow keys to change the slider and the bars should change color when the threshold is crossed.
Option Explicit
' garray(Datapoints,series)
Dim garray(1 To 2, 1 To 60)
Dim DayCount(1 To 31)
Dim RaLegend(1 To 15)
Const BeginCol = 1
Const EndCol = 2
Private Sub Form_Load()
Dim day As Integer, ra As Integer
Randomize Timer
With MSChart1
.chartType = VtChChartType2dXY
' .AllowSelections = False
.Plot.Axis(VtChAxisIdX).Va
.Plot.Axis(VtChAxisIdX).Va
.Plot.Axis(VtChAxisIdY).Ax
.Plot.Wall.Brush.FillColor
.Plot.Wall.Brush.Style = VtBrushStyleSolid
End With
'
For ra = 1 To 15 ' generate test data
RaLegend(ra) = "RA#" + CStr(Int(Rnd * 8000))
garray(BeginCol, ra * 2 - 1) = Int(Rnd * 31) + 1
garray(BeginCol, ra * 2) = ra
garray(EndCol, ra * 2 - 1) = garray(BeginCol, ra * 2 - 1) + Int(Rnd * 10) + 1
If garray(EndCol, ra * 2 - 1) > 31 Then garray(EndCol, ra * 2 - 1) = 31
garray(EndCol, ra * 2) = ra
' create datapoints used for labelling only - no line is displayed
garray(BeginCol, ra * 2 - 1 + 30) = garray(BeginCol, ra * 2 - 1)
garray(BeginCol, ra * 2 + 30) = ra
garray(EndCol, ra * 2 - 1 + 30) = garray(BeginCol, ra * 2 - 1) ' end point same as begin point so no line
garray(EndCol, ra * 2 + 30) = ra
'Debug.Print ra, garray(BeginCol, ra * 2 - 1), garray(EndCol, ra * 2 - 1)
Next ra
'
For day = 1 To 31 ' zero day counts (number of concurrent RA's for each day)
DayCount(day) = 0
Next day
For day = 1 To 31 ' sum number of concurrent RA's for each day
For ra = 1 To 15 ' for each ra
If ((garray(BeginCol, ra * 2 - 1) <= day) And _
(garray(EndCol, ra * 2 - 1) >= day)) Then
DayCount(day) = DayCount(day) + 1
End If
Next ra
'Debug.Print day, DayCount(day)
Next day
'
Me.WindowState = vbMaximized
Slider1.Min = 1
Slider1.Max = 15
Slider1.Value = 4 ' causes chart to be drawn
End Sub
Private Sub DrawChart()
Dim day As Integer, ra As Integer
Dim ExceedsThreshold As Boolean
MSChart1 = garray
' scan each ra to identify if it contains any days that are over the threshold
' and color accordingly
For ra = 1 To 15
MSChart1.Plot.SeriesCollec
' set "bar" color
ExceedsThreshold = False
For day = garray(BeginCol, ra * 2 - 1) To garray(EndCol, ra * 2 - 1)
If DayCount(day) > Slider1.Value Then
ExceedsThreshold = True
End If
Next day
If ExceedsThreshold Then
MSChart1.Plot.SeriesCollec
Else
MSChart1.Plot.SeriesCollec
End If
'set the "bar" labels
With MSChart1.Plot.SeriesCollec
.LegendText = RaLegend(ra)
With .DataPoints(-1).DataPointL
.LocationType = VtChLabelLocationTypeLeft
.Custom = True
.Component = VtChLabelComponentSeriesNa
.VtFont.VtColor.Set 240, 255, 255
End With
End With
Next ra
End Sub
Private Sub Form_Resize()
If Me.WindowState = vbMinimized Then Exit Sub
Slider1.Move Me.ScaleLeft, Me.ScaleHeight - Slider1.Height - 200, Me.ScaleWidth
MSChart1.Move Me.ScaleLeft, Me.ScaleTop, Me.ScaleWidth, Me.ScaleHeight - Slider1.Height - 300
End Sub
Private Sub Slider1_Change()
DrawChart
End Sub
ASKER
that is excellent
you are brilliant
at first look that is exactly what i am looking for
thanks heaps
i will get back to you very soon
you are brilliant
at first look that is exactly what i am looking for
thanks heaps
i will get back to you very soon
Thanks philcrouch! My natural modesty is the only thing that prevents me from agreeing with your assessment of my brilliance.
Hi, philcrouch did you ever get an answer to this question?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
Sorry about that
it took a little modification but i used it all
thanks again
Sorry about that
it took a little modification but i used it all
thanks again
Glad I could help - thanks for the points and the A Grade!
ASKER
is it possible to do one of those in VB with data from a recordset?