Guraknugen
New Member
- Joined
- Mar 15, 2017
- Messages
- 34
Hi!
I try to make a function that draws a line chart for me, but I ran into some difficulties.
I have three columns on a sheet. The first one is time, formatted as hh:mm:ss. I want these on the x axis.
The second and third columns are my values, in this case temperatures. The y axis is supposed to be the temperature axis.
My data is located on a separate sheet called "Templogg" and row 1 is the header.
My function (test code and needed functions included, unless I missed something) looks like this:
A fem global constants are needed:
I think that's all code and variables needed.
Anyway, in the DrawChart function, either of the lines with the "Fails" comments fails. The error message is in my native language, but means something like:
"The method 'MinimumScale' in the 'Axis' object failed."
First I thought that the problem was that MinimumScale etc. needed to be integers, but using integers didn't help.
In this particular test, using my data (not included here since it's more than 2000 rows), I have the following values:
My data (in this case Templogg!B1:D1312) looks something like this:
Anyway, what am I missing here? Why do I get that error message and how do I correct it?
By the way, what I'm trying to achieve is to control the time labels of the x axis. There shouldn't be more than 30 of them. In this case I think it's 120 seconds between them, så they should look like this:
In this case I have more than 2000 values, but that's not the same every time. Let's say that there are only 300 values, then there should be ten seconds between each one:
What I don't want is something like this:
Sorry for bad English.
I try to make a function that draws a line chart for me, but I ran into some difficulties.
I have three columns on a sheet. The first one is time, formatted as hh:mm:ss. I want these on the x axis.
The second and third columns are my values, in this case temperatures. The y axis is supposed to be the temperature axis.
My data is located on a separate sheet called "Templogg" and row 1 is the header.
My function (test code and needed functions included, unless I missed something) looks like this:
Code:
Sub TestDrawingAChart() Dim ChartAll As Object
Set ChartAll = ActiveSheet.ChartObjects.Add(7, 86, 453, 334).Chart
Call DrawChart(ChartAll, "AllProcess", "All process", "B1:D2312", _
2, 2312, 10, 90, 10)
End Sub
Sub DrawChart( _
ChartObject As Object, _
ChartName As String, _
ChartTitle As String, _
DataRange As String, _
StartRow As Long, _
EndRow As Long, _
YMinScale As Long, _
YMaxScale As Long, _
YMajorUnit As Long)
With ChartObject
.ChartType = xlLine
.Parent.Name = ChartName
.HasTitle = True
.ChartTitle.Text = ChartTitle
.ChartTitle.Font.Size = 12
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
With .Axes(xlValue)
.TickLabels.NumberFormat = NumberFormat0
.MinimumScale = YMinScale
.MaximumScale = YMaxScale
.MajorUnit = YMajorUnit
End With
Dim XMinScale As Double, XMaxScale As Double, XMajorUnit As Double
XMinScale = CellNumToVal(SheetLog, StartRow, 2)
XMaxScale = CellNumToVal(SheetLog, EndRow, 2)
XMajorUnit = CalcMajorUnit(XMinScale, XMaxScale, 30)
With .Axes(xlCategory)
.TickLabels.NumberFormat = TimeFormat
.MinimumScale = XMinScale ' Fails
.MaximumScale = XMaxScale ' Fails
.MajorUnit = XMajorUnit ' Fails
.HasMajorGridlines = True
End With
.ChartArea.Border.LineStyle = xlNone
.SetSourceData Source:=Sheets(SheetLog).Range(DataRange)
End With
End Sub
Function CellNumToVal(SheetName As String, Row As Long, Col As Long) As Double
Dim CellAddr As String
CellAddr = CellNumToStr(Row, Col)
CellNumToVal = Worksheets(SheetName).Range(CellAddr).Value
End Function
Function CalcMajorUnit(MinValue As Double, _
MaxValue As Double, _
ValueCount) As Double
Dim Steps As Double
Steps = sp24h * (MaxValue - MinValue) / ValueCount
Dim StepArray As Variant
StepArray = Array(10#, 15#, 20#, 30#, 60#, 120#, 300#, 600#, 900#, 1200#)
Dim Index As Long
For Index = 0 To UBound(StepArray)
If StepArray(Index) >= Steps Then
Exit For
End If
Next Index
If Index > UBound(StepArray) Then
Dim Title As String, Message As String
Title = "Something went wrong"
Message = "Something is seriously wrong with the temperature log data!"
MsgBox Message, vbOKOnly & vbCritical, Title
End
End If
CalcMajorUnit = StepArray(Index) / sp24h
End FunctionA fem global constants are needed:
Code:
Public Const SheetLog As String = "Templogg"
Public Const TimeFormat As String = "h:mm:ss"
Public Const NumberFormat0 As String = "# ### ##0"
Public Const sp24h = 86400 ' Seconds per 24 hoursI think that's all code and variables needed.
Anyway, in the DrawChart function, either of the lines with the "Fails" comments fails. The error message is in my native language, but means something like:
"The method 'MinimumScale' in the 'Axis' object failed."
First I thought that the problem was that MinimumScale etc. needed to be integers, but using integers didn't help.
In this particular test, using my data (not included here since it's more than 2000 rows), I have the following values:
Code:
XMinScale=0.407395833333333
XMaxScale=0.434131944444491
XMajorUnit=1.388888888888889E-03My data (in this case Templogg!B1:D1312) looks something like this:
Code:
[B]Time 1 [°C] 2 [°C]
[/B]09:46:39 27.6 27.3
09:46:40 27.6 27.3
09:46:41 27.6 27.3
09:46:42 27.6 27.3
09:46:43 27.6 27.3
09:46:44 27.6 27.3
...
10:13:25 81.8 81.9
10:13:26 81.9 82.0
10:13:27 81.9 82.0
10:13:28 82.0 82.1
10:13:29 82.0 82.1
...
10:19:30 83.0 85.5
10:19:31 81.3 84.1
10:19:32 79.7 82.7
10:19:33 78.0 81.3
10:19:34 76.4 79.9
...Anyway, what am I missing here? Why do I get that error message and how do I correct it?
By the way, what I'm trying to achieve is to control the time labels of the x axis. There shouldn't be more than 30 of them. In this case I think it's 120 seconds between them, så they should look like this:
Code:
9:46:00 9:48:00 09:50:00...
Code:
9:46:30 9:46:40 9:46:50 9:47:00...What I don't want is something like this:
Code:
9:46:39 9:47:11 9:47:43...Sorry for bad English.
