图表轴标签格式VBA设置

Question

我将撰写VB脚本来生成图表。在X轴上，我有日期，在Y轴上，有温度。

在X轴上，我想以“dd-mm”的格式显示时间。我的数据如下：

2014-06-17 01:00
2014-06-17 02:00
2014-06-17 03:00
2014-06-17 04:00
2014-06-17 05:00
2014-06-17 06:00
2014-06-17 07:00
2014-06-17 08:00
2014-06-17 09:00

这是我目前用VB脚本编写的内容：

 With chtChart.Chart
    .HasTitle = True
    .ChartTitle.Text = sheetName & vbCr & "2014"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
    .Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
    .Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = True
    .Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = True
    .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd-mm"

    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature [C]"
    End With

很遗憾，当我生成图表时，整个日期值（例如2014-06-07 01:00）被应用于X轴。

您对如何修复我所拥有的内容有什么想法/建议？

更新：

创建图表的全部代码：

Function AddChartSheet(sheetName As String, title As String) As Boolean
    Dim ws As Worksheet
   Dim chtChart As ChartObject
   Dim measDataSheetName As String
   'Create a new chart.
   measDataSheetName = sheetName & "_measurements.csv"

   Dim Lastrow As Integer
   Dim seriesNames() As String

   ActiveWorkbook.Sheets.Add.name = sheetName & " chart"
   Set ws = ActiveWorkbook.Sheets(sheetName & " chart")

   Set chtChart = ActiveSheet.ChartObjects.Add(Left:=25, Top:=25, _
        Width:=700, Height:=500)

    With chtChart
        .name = sheetName
    End With


   Lastrow = ActiveWorkbook.Sheets(measDataSheetName).Cells(ActiveWorkbook.Sheets(measDataSheetName).Rows.Count, "P").End(xlUp).Ro

  With chtChart.Chart
    .HasTitle = True
    .ChartTitle.Text = sheetName & vbCr & "2014"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
    .Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
    .Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = True
    .Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = True
    .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd-mm"

    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature [C]"
    End With

   With chtChart.Chart.SeriesCollection.NewSeries
      .name = "Supply"
      .ChartType = xlXYScatterSmoothNoMarkers

      .XValues = Worksheets(measDataSheetName).Range("P2:P" & Lastrow) '. SelectRange("C3", Range("C3").End(xlDown))
      .Values = Worksheets(measDataSheetName).Range("T2:T" & Lastrow)

   End With


   With chtChart.Chart.SeriesCollection.NewSeries
      .name = "Return"
      .ChartType = xlXYScatterSmoothNoMarkers

      .XValues = Worksheets(measDataSheetName).Range("P2:P" & Lastrow) '. SelectRange("C3", Range("C3").End(xlDown))
      .Values = Worksheets(measDataSheetName).Range("U2:U" & Lastrow)

   End With
   AddChartSheet = True

End Function

- JosiP

---

代码看起来没问题。你确定 chtChart.Chart 是你要查看的对象吗？此外，你确定日期被识别为日期而非字符串吗？你可以这样调试它：1）尝试写入 chtChart.Chart.Activate 看看是否弹出。2）第二个点，尝试写入 A1-1（假设一个日期在范围 A1 中），观察是否会获得值错误。 - Matteo NNZ

isDate方法在那些值上返回true... - JosiP

当您运行此代码后检查数字格式时，会得到什么？它显示“dd-mm”还是其他值？另外，如果您在创建新系列之后设置格式逻辑会发生什么？Excel可能会在检测到新系列的数据类型后覆盖您的格式。这可能更有可能发生，因为您还使用这些新系列更改了图表类型。 - Byron Wall

Answer 1

好的，我找到了解决方案：在发送数据后添加格式：

 With chtChart.Chart
    .HasTitle = True
    .ChartTitle.Text = sheetName & vbCr & "2014"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
    .Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
    .Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = True
    .Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = True
    .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd-mm"

    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature [C]"
    End With

   With chtChart.Chart.SeriesCollection.NewSeries
      .name = "Supply"
      .ChartType = xlXYScatterSmoothNoMarkers

      .XValues = Worksheets(measDataSheetName).Range("P2:P" & Lastrow) '. SelectRange("C3", Range("C3").End(xlDown))
      .Values = Worksheets(measDataSheetName).Range("T2:T" & Lastrow)

   End With

 With chtChart.Chart
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
    .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd-mm"

   End With

然后它成功了。

- JosiP

Answer 2

交换 x 和 y 轴的好方法是：

Activechart.plotby = xcolumns 

- Liam O'brien