VBA - 在 X 轴上显示时间

VBA - Show Time in the X axis

提问by user1635148

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.

我需要创建一个图表 xlXYScatter。我知道 Excel 将秒列中的这个数字视为天。如何以时间格式 mm:ss: 00:00 00:10 00:20 等将这个秒放在 X 轴上。我试过这个：.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "[m]:mm:ss"。但是 Excel 将这些以秒为单位的数字视为天，X 轴上显示的值存在缺陷。

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:

这是我的 VBA 代码：

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

回答by barrowc

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)

Excel 和 VBA 将日期和时间值处理为带有整数部分和小数部分的数字 - 例如 41176.0828。整个部分 - 41176 - 代表日期（2012 年 9 月 24 日），小数部分 - .0828 - 代表时间（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

Seconds 列中的值在 Excel 中看起来像日期值，因为它们包含整数部分。通过除以 86400 (24 * 60 * 60) 将它们转换为秒，您的图形轴标签应正确显示。如果需要，分割后的值可以位于隐藏列中，以免弄乱值表的显示

---

---

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:

编辑：以下更改使图表在 Excel 2003 中对我有用。我无法测试很多格式化命令，因为它们是在 Excel 2007 中引入的，但实际数据绘制正常：

Uncomment the PlotByparameter and set it to xlColumns:

取消注释PlotBy参数并将其设置为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 NumberFormatline, set the NumberFormatLinkedproperty to True

通过将当前值除以 86400 创建一个以秒为单位的列，确保这是图表数据源中的第一列，注释掉该NumberFormat行，将NumberFormatLinked属性设置为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, MinorUnitand MajorUnitvalues

注释掉设置MinorUnitIsAuto,MinorUnit和MajorUnit值的行

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 MaximumScalevalue by 86400 so it represents values in seconds:

将该MaximumScale值除以 86400，以秒为单位表示值：

old:

老的：

.Axes(xlCategory, xlPrimary).MaximumScale = MaxScale

new:

新的：

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