Failing to set some .Axes(xlCategory) properties

Guraknugen

New Member
Joined
Mar 15, 2017
Messages
30
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:
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 Function
A 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 hours
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:
Code:
XMinScale=0.407395833333333
XMaxScale=0.434131944444491
XMajorUnit=1.388888888888889E-03
My 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...
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:
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.
 

Guraknugen

New Member
Joined
Mar 15, 2017
Messages
30
I couldn't find a way to edit my post... wow...
Anyway, my data is located at Templogg!B1:D2312, not Templogg!B1:D1312... not that important, maybe...
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Welcome to the forum.

You'll need to use an XY scatter chart (you can connect the points with lines) in order to be able to set the minimum and maximum scales on the X axis.
 

Guraknugen

New Member
Joined
Mar 15, 2017
Messages
30
Welcome to the forum.

You'll need to use an XY scatter chart (you can connect the points with lines) in order to be able to set the minimum and maximum scales on the X axis.
Thank you!

So it's either .ChartType = xlScatterLine or .ChartType = xlScatterSmooth then?
I'll try that. I guess that would give me new problems, but I'll figure them out. Or ask more... :p
Thanks!
 

Guraknugen

New Member
Joined
Mar 15, 2017
Messages
30
Thank you!

So it's either .ChartType = xlScatterLine or .ChartType = xlScatterSmooth then?
I'll try that. I guess that would give me new problems, but I'll figure them out. Or ask more... :p
Thanks!
Oops... bad spelling there... forgot the XY.
So it's obviously one of the following types that comes to mind:
xlXYScatterLines
xlXYScatterLinesNoMarkers
xlXYScatterSmooth
xlXYScatterSmoothNoMarkers

If "markers" are what I think they are, I'll go with one of the NoMarkers types.
 

Guraknugen

New Member
Joined
Mar 15, 2017
Messages
30
Okay, I have changed the function "DrawChart" now and ran into a few problems, but I solved most of them. Except one, that I'm currently having. The problem now is that I get a runtime error when running it, but not when running step by step...
My new function looks like this, I didn't change anything else, I think:
Code:
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, _
        YMinorUnit As Long)
        
    With ChartObject
        .ChartType = xlXYScatterLinesNoMarkers
        .Parent.Name = ChartName
        .HasTitle = True
        With .ChartTitle
            .Text = ChartTitle
            .Font.Size = 12
        End With
        
        With .Axes(xlValue)
            With .TickLabels
                .NumberFormat = NumberFormat0
            End With
            .HasMajorGridlines = True
            .HasMinorGridlines = True
            .MinimumScale = YMinScale
            .MaximumScale = YMaxScale
            .MajorUnit = YMajorUnit
            .MinorUnit = YMinorUnit
            With .MajorGridlines.Format.Line
                .Weight = 1#
                .ForeColor.RGB = Black
            End With
            With .MinorGridlines.Format.Line
                .Weight = 0.25
                .ForeColor.RGB = Gray
            End With
        End With
        
        ' Calculate how the x scale values are going to be represented.
        Dim XMinScale As Double, XMaxScale As Double, XMajorUnit As Double
        XMinScale = CellNumToVal(SheetLog, StartRow, 2)
        XMaxScale = CellNumToVal(SheetLog, EndRow, 2)
        XMajorUnit = CalcXMajorUnit(XMinScale, XMaxScale, 30)
        Dim UnitsCount As Double
        UnitsCount = Int(XMinScale / XMajorUnit)
        XMinScale = XMajorUnit * UnitsCount
        
        With .Axes(xlCategory)
            With .TickLabels
                .NumberFormat = TimeFormat
                .Orientation = xlTickLabelOrientationUpward
            End With
            .MinimumScale = XMinScale
            .MaximumScale = XMaxScale
            .MajorUnit = XMajorUnit
            .HasMajorGridlines = True
        End With
        .ChartArea.Border.LineStyle = xlNone
        .PlotArea.Height = 260 ' Runtime error here unless running step by step!
        .HasLegend = True
        With .Legend
            .Position = xlLegendPositionBottom
            .Left = 168
        End With
        .SetSourceData Source:=Sheets(SheetLog).Range(DataRange)
    End With
End Sub
The error message is, translated from my native language to my home made school English:
Code:
Runtime error no. '-2147467259 (80004005)'.:
The 'Height" method in the 'PlotArea' object failed.
If I set a breakpoint at that line (8th line from the bottom line) and then hit F5 to continue, it works fine.

I tried to search for it and one suggestion is that this happens on protected sheets, but after cancelling the macro I checked the sheet, and it's NOT protected.

So now I'm not sure what to do next...

Another thing I find a little weird, is that if I add the following line right above the PlotArea line, no error occurs:
Code:
Debug.Print .PlotArea.Height
The result of the Debug.Print in this case is 288.35, by the way.

Any ideas?
I could keep that line of course, but I still want to know why this happens or what I'm doing wrong.


Thanks in advance.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You're not doing anything wrong. The chart engine introduced in 2007 still has some bugs (though it's a hell of a lot better than it was in 2007!) like this. Sometimes a DoEvents line will cure it, other times reading the property before you set it will work.
 

Guraknugen

New Member
Joined
Mar 15, 2017
Messages
30
You're not doing anything wrong. The chart engine introduced in 2007 still has some bugs (though it's a hell of a lot better than it was in 2007!) like this. Sometimes a DoEvents line will cure it, other times reading the property before you set it will work.
Ok, I suspected it was a bug. In this case it's Excel 2013, so maybe it works better in a newer version.

The Debug.Print workaround looks a little (!) ugly and after a few more test rounds I found that replacing it with .PlotArea.Select also does the trick and it looks a little better, I think.

Thanks for replying. :cool:
 
Last edited:

Forum statistics

Threads
1,081,556
Messages
5,359,555
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top