CODEWITHSUNDEEP
vbaexcelcharts
user1635148
user1635148

Reputation: 59

VBA - Show Time in the X axis

I need create a chart xlXYScatter. I know the Excel see this number in Seconds Column as day. How to put this Seconds in X axis in time format mm:ss: 00:00 00:10 00:20 and so on. I tried this: .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "[m]:mm:ss". But the Excel sees these numbers that are seconds as days, the values shown on the X axis, are with defect.

This is my sample data:

Seconds    DEV1    DEV2    DEV3
  0           0      0       0
 10        6162    769     753
 20        6160    771     753
 30        6162    766     748
 40        6139    765     740
 50        6141    762     740
 60        6126    761     742
 70        6119    764     745
 80        6114    766     740
 90        6103    763     745
100        6098    768     745
110        6095    767     748
120        6095    768     737
130        6093    763     732
140        6093    764     729
150        6082    765     726
160        6078    764     729
170        6072    762     729
180        6074    760     726
190        6067    766     721
200        6067    762     724
210        6072    760     724
220        6070    756     729
230        6069    757     732
240        6063    757     734
250        6067    749     750
260        6063    751     753
270        6056    756     753
280        6057    758     753
290        6059    760     750
300        6064    761     753
310        6087    765     734

And this is my VBA code:

Sub NewChart()

Sheets(1).Select 'Select the active Sheet

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter

LastLine = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row

Dim MaxScale As Integer
Dim MTotal As Integer
Dim Aprox As Integer
Dim vName As String
vName = Sheets(2).Range("B3")

Dim Qx As Integer
Qx = LastLine - 1
Dim Rangg As Integer
Rangg = (LastLine * 10) - 60
MTotal = Rangg
MaxScale = Rangg + 20

    With ActiveChart
    .ChartType = xlXYScatter
    'Set data source range.
    .SetSourceData Source:=Sheets(2).Range("A5:A" & Qx & ",B5:B" & Qx & ",E5:E" & Qx & ",H5:H" & Qx & ", K5:K" & Qx & ", N5:N" & Qx & ", Q5:Q" & Qx) ', 'PlotBy:=xlRows
    .HasTitle = True
    .ChartTitle.Text = vName '"2-7µm"

    'The Parent property is used to set properties of the Chart.
    With .Parent
      .Top = 2
      .Left = 2
      .Width = 540
      .Height = 252
      .Name = "2micron"
    End With

ActiveChart.Legend.Select
        With Selection.Format.TextFrame2.TextRange.Font
            .NameComplexScript = "Tahoma"
            .NameFarEast = "Tahoma"
            .Name = "Tahoma"
        End With

    .Axes(xlCategory).MajorTickMark = xlInside
    .Axes(xlCategory).MinorTickMark = xlInside
    .Axes(xlCategory, xlPrimary).Select
    .Axes(xlCategory, xlPrimary).TickLabels.Font.Size = 5
    .Axes(xlCategory, xlPrimary).TickLabels.Font.Name = "Tahoma"
    .Axes(xlCategory, xlPrimary).TickLabels.Font.Bold = msoTrue
    .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "[m]:mm:ss"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (seconds)"
    .Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 11
    .Axes(xlCategory, xlPrimary).AxisTitle.Font.Bold = msoTrue
    .Axes(xlCategory, xlPrimary).AxisTitle.Font.Name = "Tahoma"
    .Axes(xlCategory, xlPrimary).MinorUnitIsAuto = False
    .Axes(xlCategory, xlPrimary).MajorUnit = 300
    .Axes(xlCategory, xlPrimary).MinorUnit = 60
    .Axes(xlCategory, xlPrimary).MaximumScale = MaxScale
    .Axes(xlCategory, xlPrimary).MinimumScale = 0
    .Axes(xlCategory, xlPrimary).HasMajorGridlines = False
    .Axes(xlCategory, xlPrimary).HasMinorGridlines = False
    .Axes(xlCategory).Format.Line.ForeColor.RGB = RGB(0, 0, 0)

    .Axes(xlValue).MajorTickMark = xlInside
    .Axes(xlValue).MinorTickMark = xlInside
    .Axes(xlValue, xlPrimary).Select
    .Axes(xlValue, xlPrimary).HasMajorGridlines = True
    .Axes(xlValue, xlPrimary).HasMinorGridlines = True
    .Axes(xlValue, xlPrimary).TickLabels.Font.Size = 11
    .Axes(xlValue, xlPrimary).TickLabels.Font.Name = "Tahoma"
    .Axes(xlValue, xlPrimary).TickLabels.Font.Bold = msoTrue
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y axis Legend"
    .Axes(xlValue, xlPrimary).AxisTitle.Font.Size = 11
    .Axes(xlValue, xlPrimary).AxisTitle.Font.Name = "Tahoma"
    .Axes(xlValue, xlPrimary).AxisTitle.Font.Bold = msoTrue
    .Axes(xlValue).Format.Line.ForeColor.RGB = RGB(0, 0, 0)

    .Legend.IncludeInLayout = False
    .Legend.Select
    Selection.Position = xlTop
    Selection.Font.Size = 11
    Selection.Font.Name = "Tahoma"
    Selection.Font.Bold = msoTrue

    ActiveSheet.Shapes("2micron").ScaleWidth 1, msoFalse, _
        msoScaleFromTopLeft

    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.ChartTitle.Select
    Selection.Left = 2
    Selection.Top = 2
    Selection.Format.TextFrame2.TextRange.Font.Size = 13.2
    Selection.Format.TextFrame2.TextRange.Font.Name = "Tahoma"
    Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue
    ActiveChart.Legend.Select
    Selection.Left = 180
    Selection.Top = 2
        With Selection.Format.Line
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
        End With
    ActiveChart.PlotArea.Select
    Selection.Top = 22
    Selection.Left = 20
    Selection.Height = 207
    Selection.Width = 540
    Selection.Border.LineStyle = xlSolid
    Selection.Border.Color = vbBlack
    Selection.Interior.Color = vbWhite
    End With

Call f_l2m1(1, 8, RGB(0, 176, 240))
Call f_l2m1(2, 3, RGB(255, 0, 0))
Call f_l2m1(3, 1, RGB(255, 0, 255))
Call f_l2m1(4, 2, RGB(153, 0, 255))
Call f_l2m1(5, 4, RGB(153, 0, 255))
Call f_l2m1(6, 9, RGB(146, 208, 80))

Range("a22").Select
End Sub

Sub f_l2m1(LineNo, MStyle, vRGB)

With ActiveSheet.ChartObjects("2micron").Chart

                ActiveChart.SeriesCollection(LineNo).Select
                With Selection
                    .MarkerStyle = MStyle
                    .MarkerSize = 7
                    .MarkerForegroundColor = vRGB
                End With
                Selection.Format.Fill.Visible = msoFalse
                Selection.Format.Line.Visible = msoFalse
                Selection.Format.Line.ForeColor.RGB = vRGB
End With
End Sub

Upvotes: 0

Views: 7905

Answers (1)

barrowc
barrowc

Reputation: 10679

Excel and VBA deal with date and time values as a number with a whole part and a decimal part - e.g. 41176.0828. The whole part - 41176 - represents the date (24th September 2012) and the decimal part - .0828 - represents the time (01:59:12)

The values in your Seconds column look like date values to Excel because they have a whole number part. Convert them into seconds by dividing by 86400 (24 * 60 * 60) and your graph axis labels should display properly. If need be, the divided values could be in a hidden column so as not to mess up the display of your table of values

edit: the following changes got the chart to work for me in Excel 2003. I couldn't test a lot of the formatting commands as they were introduced in Excel 2007 but the actual data is being plotted OK:

Uncomment the PlotBy parameter and set it to xlColumns:

old:

.SetSourceData Source:=Sheets(2).Range("A5:A" & Qx & ",B5:B" & Qx & ",E5:E" & Qx & ",H5:H" & Qx & ", K5:K" & Qx & ", N5:N" & Qx & ", Q5:Q" & Qx) ', 'PlotBy:=xlRows

new:

.SetSourceData Source:=Sheets(2).Range("A5:A" & Qx & ",B5:B" & Qx & ",E5:E" & Qx & ",H5:H" & Qx & ", K5:K" & Qx & ", N5:N" & Qx & ", Q5:Q" & Qx), PlotBy:=xlColumns

Create a column with the values in seconds by dividing the current values by 86400, ensure this is the first column in the data source for the chart, comment out the NumberFormat line, set the NumberFormatLinked property to True

old:

.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "[m]:mm:ss"

new:

'.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "[m]:mm:ss"
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormatLinked = True

Comment out the lines setting the MinorUnitIsAuto, MinorUnit and MajorUnit values

old:

.Axes(xlCategory, xlPrimary).MinorUnitIsAuto = False
.Axes(xlCategory, xlPrimary).MajorUnit = 300
.Axes(xlCategory, xlPrimary).MinorUnit = 60

new:

'.Axes(xlCategory, xlPrimary).MinorUnitIsAuto = False
'.Axes(xlCategory, xlPrimary).MajorUnit = 300
'.Axes(xlCategory, xlPrimary).MinorUnit = 60

Divide the MaximumScale value by 86400 so it represents values in seconds:

old:

.Axes(xlCategory, xlPrimary).MaximumScale = MaxScale

new:

.Axes(xlCategory, xlPrimary).MaximumScale = (MaxScale / 86400)

Upvotes: 1

Related Questions