vba - 使用VBA创建图表，无法将X轴格式化为文本

Question

我正在创建一个生成图表的宏。

图表创建按我的预期进行，没有任何问题。我遇到的唯一问题是 X 轴上显示的日期不正确。

Sub generateChart()
' Select a range starting in row 2.
' This macro will use that range, and create a chart just for them.
Dim rng As Range
Dim randR As Long, randG As Long, randB As Long

Set rng = Selection

Dim numCharts As Long
numCharts = ActiveSheet.ChartObjects.Count

Dim newChart As ChartObject

Dim num As Long
num = rng.Columns.Count

Dim i       As Long

For i = 1 To num
    randR = Application.WorksheetFunction.RandBetween(1, 200)
    randG = Application.WorksheetFunction.RandBetween(0, 255)
    randB = Application.WorksheetFunction.RandBetween(0, 255)

    With ActiveSheet
        Set newChart = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=75, Height:=225)
        With newChart.Chart
            .ChartType = xlXYScatterLines
            Debug.Print rng.Address

            .SetSourceData Source:=rng

            With .FullSeriesCollection(1)
                .Name = Cells(1, rng.Columns(i).Column).Value
                .Values = Range(Cells(2, rng.Columns(i).Column), _
                                Cells(rng.Rows.Count + 1, rng.Columns(i).Column))
                .XValues = "=Sheet2!$J$2:$J$10"
                .Format.Fill.ForeColor.RGB = RGB(randR, randG, randB)
                .Format.Line.Visible = msoFalse
                .MarkerStyle = 1
                .MarkerSize = 8
            End With

            .SeriesCollection.NewSeries
            With .FullSeriesCollection(2)
                .Name = "=Sheet2!$Q$1"
                .Values = "=Sheet2!$Q$2:$Q$10"
                .XValues = "=Sheet2!$J$2:$J$10"
                .Format.Line.Visible = msoTrue
                .MarkerStyle = 0
            End With

            .SetElement (msoElementLegendBottom)

            ' Add titles
            Dim titleStr As String
            .SetElement (msoElementChartTitleAboveChart)
            titleStr = Cells(1, rng.Columns(i).Column).Value & " Time Delay"

            With .ChartTitle
                .Text = titleStr
                .Format.TextFrame2.TextRange.Characters.Text = Cells(1, rng.Columns(i).Column).Value & " Time Delay"
                .Format.TextFrame2.TextRange.ParagraphFormat.TextDirection = msoTextDirectionLeftToRight
                .Format.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
            End With

            ' Now, hide the points that are 0 value
            hideZeroValues newChart

            ' I thought this would work, but it doesn't seem to do anything
            .Axes(xlCategory).CategoryType = xlCategoryScale

        End With 'newchart.chart

    End With                 ' ActiveSheet
Next i

End Sub

还有截图: 请注意，我什至没有设置文本格式的选项。

(请注意平均值是正确的，有隐藏列)

但是!如果我使用“内置”图表创建图表，只需选择数据，我就可以选择将格式设置为文本。

我在宏中忽略了什么？为什么我似乎无法正确设置 X 值？选择“数字”，然后格式化为日期类别会保留不正确的日期。最后，也许这暗示了出了什么问题，如果我右键单击图表并尝试选择日期，“水平轴”将显示为灰色。

感谢您的任何想法/想法!

编辑:这是一个 link to a .gif, showing the formatting working correctly, if I insert the chart via Excel's chart menu

Answer 1

我认为您看到的是分类 x 轴和连续 x 轴之间的区别。 “散点图”类型的图使用连续轴(即，它们绘制数据的“范围”，而不仅仅是单个点，并且显示的日期由主要/次要刻度间隔确定)。

您应该使用“常规”折线图(而不是“散点”版本)，如果它仍然不起作用，那么:

newChart.Chart.Axes(xlCategory).CategoryType = xlCategoryScale

应强制 x 轴进入分类模式