How to correctly add a series of time values to an Excel chart

Question

I am trying to add a series composed of Time values to a chart. The y-axis is formatted as Time. I am using the variable myVal to hold the value I want to repeat, inside an array, as the series plot values.

I know that Time is stored in the Date datatype. I am unsure how to how do the correct conversion to add a series to the chart via VBA.

My attempts to assign the series values and plot via code keep resulting in a line being plotted around 0 on the y-axis indicating I have the wrong scale. See red line on chart image.

Chart:

Trying to do conversions on myVal with DateValue("00:07:00") and TimeValue("00:07:00") ,and then scaling, are of no use as they return 0.

I have also seen that there is FormatDateTime function, with argument vbLongTime, but I can't see how to apply this in my situation.

I know I can directly add the series from the sheet with :

.FullSeriesCollection(2).Values = _
        "=ChartDataResponseTimes!$BC$548:$BC$552"

Where $BC$548:$BC$552 is the range, as shown in the image above, containing "00:07:00" repeated. This is not the method I want to use as I want to assign the series values via an array within the code.

This, I am certain, is so simple to do, but I am failing miserably today.

Can anyone please solve this?

Code:

Sub AddingSeries()

    With ActiveSheet.ChartObjects("hidRTMeanCat1").Chart

        .SeriesCollection.NewSeries

        .FullSeriesCollection(1).Values = _
                                        "=ChartDataResponseTimes!$B$548:$B$552"

        .FullSeriesCollection(1).XValues = _
                                         "=ChartDataResponseTimes!$A$548:$A$552"

        .SeriesCollection.NewSeries

        Dim myVal As Date '**************************************Problem code***************

        myVal = DateValue("00:07:00")

        .FullSeriesCollection(2).Values = Array(myVal, myVal)

                          '**************************************Problem code***************
    End With

End Sub

Note:

ChartDataResponseTimes!$A$548:$A$552" is the first column of data in the sample below.

ChartDataResponseTimes!$B$548:$B$552 is the second column of data in the sample below.

Data sample:

| Date   | North West |
|--------|------------|
| Aug-17 | 00:10:07   |
| Sep-17 | 00:09:50   |
| Oct-17 | 00:09:29   |
| Nov-17 | 00:09:44   |
| Dec-17 | 00:11:17   |

What do you mean? The axis is already formatted as Time. I have a blank chart which I add the series to. — QHarr, Feb 07 '18 at 10:14

score 1 · Accepted Answer · answered Feb 07 '18 at 14:45

1

Change myVal variable type to Double.

Dim myVal As Double

answered Feb 07 '18 at 14:45

Dy.Lee

7,527
1
12
14

I get a type mismatch with Dim myVal As Double : myVal = CDbl("00:07:00") . Though this sounds like the right thinking. – QHarr Feb 07 '18 at 14:52
@QHarr, myVal = TimeValue("00:07:00") – Dy.Lee Feb 07 '18 at 15:05
Doh! Back to where i started but had wrong datatype. Thanks so much for sorting. – QHarr Feb 07 '18 at 15:11
@QHarr, Good luck. – Dy.Lee Feb 07 '18 at 15:13

How to correctly add a series of time values to an Excel chart

1 Answers1