Some folks have searched, from time to time, for a dual axis MSChart
with different scales on the two Y axes. The sample, extracted from
running code I wrote, produces a graph with MSChart (VB9) which shows a
time axis on X and two somewhat different value ranges on Y and Y2.
This code produces two axes with positive and negative values, if called
for, and also ranges the Y and Y2 values to match the data and still
remain readable.
Now there is a little "gotcha" waiting for you here. In fact, there are
two and both are solved in this routine.
1. If you make the two sets of values for the axes NOT harmonically
related, you will get a bunch of confusing lines across the graph and
quite probably two "0" lines. This can make the chart difficult to read
and probably defeat the reason for drawing two sets of data on one
chart.
By harmonically related, I mean something like 10 to 1 (Y to Y2 or vice
verse) or 2 to 1 or maybe 5 to 1 (may look bad, not tried). If you do 3
to 2, you will have a mess!
2. When you change controls, let's say Label or Textbox, and you exit
the routine, the message queue is emptied and the label or text is
updated. Not so for MSChart. You have to force it to change after
assigning the new axis range numbers. It will answer back that it knows
the number but will not show it until the routine comes through a second
time. This is plain dumb, but fixable. There are two "DoEvents"
herewith. Only one of them might be needed but it works fine with both
so they are left in in case they do anything useful. I can't test right
now since the data collection and GUI are still in the same code module.
I want to split them apart but still have not figured out how to send
data between them. Just not enough time for all my activities!
There should be an attachment showing the output from this routine with
today's data (to present time). It shows the two Y values related at 10
to 1 which works out fairly well for the data I am plotting. There are
two colors for the axes and the same two colors for the graph lines
corresponding to the values. No way to change the value text
independently, sadly...
Enjoy, Mike.
(For those still reading and interested, this is the output plot for the
difference between yesterday's and today's production from my solar
array. I had some clouds yesterday and today is smooth so it shows the
variation, higher means today is better and, so far, I am slightly ahead
of yesterday)
---------------------------------------------
Private Sub GraphDifference()
Dim i As Integer
Dim iMin As Integer
Dim sTitle As String
Dim sMy4DTime As String
Dim lLowArrayElement As Integer
Dim lHighArrayElement As Integer
Dim lLowArrayElementToday As Integer
Dim lHighArrayElementToday As Integer
Dim lLowArrayElementYesterday As Integer
Dim lHighArrayElementYesterday As Integer
Dim lYMax As Integer = 0
Dim lYMin As Integer = 0
Dim lY2Max As Integer = 0
Dim lY2Min As Integer = 0
Dim lTempMax As Integer
Dim lTempMin As Integer
Dim dPowerDiff As Decimal = 0
giWhichGraphShowing = DAYDIFF_SHOWING
If glTrendCtToday < 2 Or glTrendCtYesterday < 2 Then
MSChart1.Visible = False
Exit Sub
Else
MSChart1.Visible = True
End If
With MSChart1
.AllowSelections = False
.chartType = CType(glChartTypeDayDiff, VtChChartType)
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY).Va lueScale.Auto =
False
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY2).V alueScale.Auto =
False
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY).Ca tegoryScale.Auto
= False
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY2).C ategoryScale.Auto =
False
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY).Pe n.VtColor.Red =
0
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY).Pe n.VtColor.Green
= 255
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY).Pe n.VtColor.Blue =
0
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY).Pe n.Width = 25
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY2).P en.VtColor.Red =
255
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY2).P en.VtColor.Green
= 0
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY2).P en.VtColor.Blue
= 0
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY2).P en.Width = 25
.RowCount = 2
For iMin = 0 To MAX_MINS
'glDifferenceArray(i, DIFF_TIME) = 0 ' Do not clear this one.
glDifferenceArray(iMin, DIFF_WATTS_TODAY) = 0
glDifferenceArray(iMin, DIFF_DC_TODAY) = 0
glDifferenceArray(iMin, DIFF_WATTS_YESTERDAY) = 0
glDifferenceArray(iMin, DIFF_WATTS_YESTERDAY) = 0
Next
'Pick up values for today
For i = 1 To glTrendCtToday - 1
sMy4DTime = Strings.Right("000" & gsTrendTimeToday(i), 4)
iMin = CInt(Strings.Left(sMy4DTime, 2)) * 60
iMin = CInt(iMin + Val(Strings.Right(sMy4DTime, 2)))
glDifferenceArray(iMin, DIFF_WATTS_TODAY) = glTrendPowerToday(i)
glDifferenceArray(iMin, DIFF_DC_TODAY) = glTrendDCVoltsToday(i)
Next
'Pick up the values for yesterday
For i = 1 To glTrendCtYesterday - 1
sMy4DTime = Strings.Right("000" & gsTrendTimeYesterday(i), 4)
iMin = CInt(Strings.Left(sMy4DTime, 2)) * 60
iMin = CInt(iMin + Val(Strings.Right(sMy4DTime, 2)))
glDifferenceArray(iMin, DIFF_WATTS_YESTERDAY) =
glTrendPowerYesterday(i)
glDifferenceArray(iMin, DIFF_DC_YESTERDAY) =
glTrendDCVoltsYesterday(i)
Next
'First, find low element in use today
For lLowArrayElementToday = 0 To MAX_MINS
If glDifferenceArray(CInt(lLowArrayElementToday),
DIFF_WATTS_TODAY) <0 Or _
glDifferenceArray(lLowArrayElementToday, DIFF_DC_TODAY) <0 Then
Exit For
Next
'Now find high element in use today
For lHighArrayElementToday = MAX_MINS To 0 Step -1
If glDifferenceArray(lHighArrayElementToday, DIFF_WATTS_TODAY)
<0 Or _
glDifferenceArray(lHighArrayElementToday, DIFF_DC_TODAY) <0
Then Exit For
Next
'Find lowest element in use for yesterday
For lLowArrayElementYesterday = 0 To MAX_MINS
If glDifferenceArray(lLowArrayElementYesterday,
DIFF_WATTS_YESTERDAY) <0 Or _
glDifferenceArray(lLowArrayElementYesterday,
DIFF_WATTS_YESTERDAY) <0 Then Exit For
Next
'Find highest element in use for yesterday
For lHighArrayElementYesterday = MAX_MINS To 0 Step -1
If glDifferenceArray(lHighArrayElementYesterday,
DIFF_WATTS_YESTERDAY) <0 Or _
glDifferenceArray(lHighArrayElementYesterday,
DIFF_WATTS_YESTERDAY) <0 Then Exit For
Next
lLowArrayElement = lLowArrayElementToday
If lLowArrayElementYesterday lLowArrayElement Then
lLowArrayElement = lLowArrayElementYesterday
lHighArrayElement = lHighArrayElementToday
If lHighArrayElementYesterday < lHighArrayElement Then
lHighArrayElement = lHighArrayElementYesterday
.ColumnCount = CShort(lHighArrayElement - lLowArrayElement)
'.Column = 1
For i = lLowArrayElement To lHighArrayElement - 1
.Column = CShort(i - lLowArrayElement + 1)
.Row = 1
If glDifferenceArray(i, DIFF_WATTS_TODAY) = 0 Then
glDifferenceArray(i, DIFF_WATTS_TODAY) = glDifferenceArray(i - 1,
DIFF_WATTS_TODAY)
If glDifferenceArray(i, DIFF_WATTS_YESTERDAY) = 0 Then
glDifferenceArray(i, DIFF_WATTS_YESTERDAY) = glDifferenceArray(i - 1,
DIFF_WATTS_YESTERDAY)
.Data = CStr(glDifferenceArray(i, DIFF_WATTS_TODAY) -
glDifferenceArray(i, DIFF_WATTS_YESTERDAY))
'Watts are on Y2 axis
If .Data lY2Max Then lY2Max = .Data
If .Data < lY2Min Then lY2Min = .Data
dPowerDiff = CDec(dPowerDiff + ((glDifferenceArray(i,
DIFF_WATTS_TODAY) - glDifferenceArray(i, DIFF_WATTS_YESTERDAY)) / 60))
If i = 1 Then .Data = CStr(0)
.Row = 2
'This is to make up for missing datapoints. If one is missing,
the effect is a 0 and that makes spikes I just don't want to see.
If glDifferenceArray(i, DIFF_DC_TODAY) = 0 Then
glDifferenceArray(i, DIFF_DC_TODAY) = glDifferenceArray(i - 1,
DIFF_DC_TODAY)
If glDifferenceArray(i, DIFF_DC_YESTERDAY) = 0 Then
glDifferenceArray(i, DIFF_DC_YESTERDAY) = glDifferenceArray(i - 1,
DIFF_DC_YESTERDAY)
.Data = CStr(glDifferenceArray(i, DIFF_DC_TODAY) -
glDifferenceArray(i, DIFF_DC_YESTERDAY))
'Volts are on the Y axis
If .Data lYMax Then lYMax = .Data
If .Data < lYMin Then lYMin = .Data
.ColumnLabel = Format(glDifferenceArray(i, DIFF_TIME), "#0:00")
Next
.Column = 1
.Row = 1
.Data = CStr(0)
.RowLabel = "AC Watts"
.Row = 2
.Data = CStr(0)
.RowLabel = "DC Volts"
sTitle = "Today's Production minus Yesterday's (power difference =
" & Format(dPowerDiff, "+###,##0;-###,##0")
If Math.Abs(CInt(dPowerDiff)) = 1 Then
sTitle &= " watt.)"
Else
sTitle &= " watts.)"
End If
.TitleText = sTitle
Application.DoEvents()
lTempMax = lYMax * 10
If lTempMax lY2Max Then
lY2Max = lTempMax
End If
lTempMax = Math.Ceiling(lY2Max / 100)
lY2Max = lTempMax * 100
' Now, set the scales' maximum values
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY2).V alueScale.Maximum =
lY2Max
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY).Va lueScale.Maximum
= .Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY2).V alueScale.Maximum /
10
lTempMin = lYMin * 10
If lTempMin < lY2Min Then
lY2Min = lTempMin
End If
lTempMin = Math.Floor(lY2Min / 100)
lY2Min = lTempMin * 100
'And, finally, set the scales' minimums
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY2).V alueScale.Minimum =
lY2Min
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY).Va lueScale.Minimum
= .Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY2).V alueScale.Minimum /
10
Application.DoEvents()
..Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY).V alueScale.MajorDivision
=
..Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdY2). ValueScale.MajorDivision
'.Refresh()
.Plot.Axis(MSChart20Lib.VtChAxisId.VtChAxisIdX, 1).Tick.Style =
MSChart20Lib.VtChAxisTickStyle.VtChAxisTickStyleNo ne
End With
lblTodayKW.Text = " "
lblLblTodayKW.Text = " "
End Sub
