图表轴标签格式vba设置 [英] chart axis label format vba settings

问题描述

我正在编写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脚本中写的：

 与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]
结束
  

不幸的是，当我生成图表时，整个日期值（例如2014-06 -07 01:00）正在应用于X轴。

有什么想法可以解决我的问题？

更新：

创建图表的整个代码：

函数AddChartSheet（SheetName As String，title As String）As Boolean
Dim ws As Worksheet
Dim chtChart As ChartObject
Dim measDataSheetName As String
'创建一个新的图表。
measDataSheetName = sheetName& _measurements.csv

Dim Lastrow As Integer
Dim seriesNames（）As String

ActiveWorkbook.Sheets.Add.name = sheetName& chart
设置ws = ActiveWorkbook.Sheets（sheetName&chart）

设置chtChart = ActiveSheet.ChartObjects.Add（左：= 25，顶部：= 25，_
宽度：= 700，身高：= 500）

带有chtChart
.name = sheetName
结束


= ActiveWorkbook.Sheets（measDataSheetName）.Cells（ActiveWorkbook.Sheets（measDataSheetName）.Rows.Count，P）。End（xlUp）.Ro

使用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]
结束

带有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）

结束


带有chtChart.Chart.SeriesCollection.NewSeries
.name =返回
.ChartType = xlXYScatterSmoothNoMarkers

.XValues =工作表（measDataSheetName）.Range（P2：P& Lastrow）。 SelectRange（C3，Range（C3）。End（xlDown））
.Values = Worksheets（measDataSheetName）.Range（U2：U& Lastrow）

结束
AddChartSheet = True

结束函数

解决方案

确定我找到解决方案：发送数据后添加格式：

 使用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]
结束
 
带有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）
 
结束
 
与chtChart.Chart 
 .Axes（xlCategory，xlPrimary）.AxisTitle.Characters.Text =Date
 .Axes（xlCategory，xlPrimary）.TickLabels.NumberFormat = dd-mm
 
结束
  

/ p>

I'm writing vb script to generate charts. On the X axis, I have have the date and on the Y axis, the temp.

On the X axis I want to present time with the format "dd-mm". My data looks like this:

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

And this is what I have written in vb script so far:

 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

Unfortunately, when I generate the chart, the entire date value (e.g. 2014-06-07 01:00) is being applied to the X axis.

Any thoughts/ideas on how I can fix what I have?

Update:

whole code to create charts:

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

解决方案

Ok i did find solution: add formatting after sending data:

 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

and it worked out.

这篇关于图表轴标签格式vba设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！