在X轴上设置日期 [英] Setting dates on X axis

问题描述

我正在尝试在Excel VBA中创建图表，但在使X轴正确显示日期方面遇到问题；代码如下:

I'm trying to create a chart in Excel VBA and am having problems getting the X-Axis to display the dates correctly; the code is below:

Function CreateChart()
Dim objChart As Chart

ReDim detached_price(detachedProps.count - 1) As Double
ReDim detached_date(detachedProps.count - 1) As Date

ReDim semi_price(semiProps.count - 1) As Double
ReDim semi_date(semiProps.count - 1) As Date

Dim minDate As Date
Dim maxDate As Date
minDate = Date

Dim detachedCount As Integer
detachedCount = 0
Dim semiCount As Integer
semiCount = 0

Set objChart = Charts.Add
With objChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Price Paid"
    .ChartType = xlXYScatter
    .Location xlLocationAsNewSheet
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
    .Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
    .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "yyyy"
    .Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = True
    .Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = True

    For Each prop In properties
        Select Case prop.PropertyType
            Case "Detached"
                detached_price(detachedCount) = prop.Amount
                detached_date(detachedCount) = prop.SellDate
                detachedCount = detachedCount + 1
            Case "Semi-detached"
                semi_price(semiCount) = prop.Amount
                semi_date(semiCount) = prop.SellDate
                semiCount = semiCount + 1
        End Select

        If prop.SellDate < minDate Then
            minDate = prop.SellDate
        End If

        If prop.SellDate > maxDate Then
            maxDate = prop.SellDate
        End If
    Next

    .SeriesCollection.NewSeries

    .SeriesCollection(DETACHED).Name = "Detached"
    .SeriesCollection(DETACHED).Values = detached_price
    .SeriesCollection(DETACHED).XValues = detached_date

    .SeriesCollection.NewSeries
    .SeriesCollection(SEMI).Name = "Semi-Detached"
    .SeriesCollection(SEMI).Values = semi_price
    .SeriesCollection(SEMI).XValues = semi_date
End With End Function

填充For..Each循环中的properties变量，并正确填充数组.

The properties variable in the For..Each loop is populated, and fills the arrays correctly.

但是，尽管显示了散点图数据点，但轴上的日期都显示为1900.

However, although the Scatter Graph data points are shown, the dates on the axis all show 1900.

我尝试添加以下行:

    .Axes(xlCategory, xlPrimary).MinimumScale = CDbl(minDate)
    .Axes(xlCategory, xlPrimary).MaximumScale = CDbl(maxDate)

哪一个沿轴显示了正确的年份，但是现在两个系列的所有数据点都消失了.

Which showed the correct years along the axis, but now all the data points for both series have disappeared.

我已经尝试了其他一些方法，但这完全是基于反复试验的结果.

I've tried a few other things, but it's been purely on a trial and error basis.

数据如下

结果图表为:

正确的日期，没有数据点

Correct dates, no data points

日期不正确，但我们有数据点

Incorrect dates, but we have data points

推荐答案

即使我不同意他们对公共"日期格式:q的定义，我也认为@chiliNUT确实适用.日期格式的强制似乎有些问题.

Even though I disagree with their definition of "common" date format :q, I think that @chiliNUT is on to something. There seems to be some problem with the coercion of the date format.

如果将所有Date类型变量都更改为Double或Long，它应该可以工作.

If you change all of your Date type variables to Double or Long, it should work.

例如更改

ReDim detached_date(detachedProps.count - 1) As Date

到

ReDim detached_date(detachedProps.count - 1) As Double

或

ReDim detached_date(detachedProps.count - 1) As Long

这样，XValues方法不会将日期转换为String.它们存储为日期序列号，并且轴例程可以将其成功强制转换为本地日期格式.

This way the dates are not converted into String by the XValues method. They are stored as date serial numbers and the axis routine is able to coerce them successfully into the local date format.

代码中发生的事情是Date类型被XValues方法强制转换为String，并且轴渲染例程似乎无法将其强制转换为正确的日期.

Whats happening in your code is the Date types are coerced to String by the XValues method and the axis rendering routine seems to be unable to coerce them back into dates properly.

我不认为它实际上与国际设置有关，因为我使用这样的日期进行了尝试:

I don't think it is actually related to the international settings as I tried it using dates like this:

1/01/2013
1/01/2014
1/01/2015
1/01/2016
1/01/2017
1/01/2018
1/01/2019
1/01/2020
1/01/2021

在任何一个系统中均可使用.

which works in either system.

我认为它只是轴渲染例程中的一个错误，无法正确地将字符串强制转换为日期.

I think its just a bug in the axis rendering routine where its unable to properly coerce strings into dates.

我很想听听别人说比我更多的东西.

I would be interested to hear from others more knowledgeable than me.

我对此也很好奇:

.SeriesCollection.NewSeries

.SeriesCollection(DETACHED).Name = "Detached"
.SeriesCollection(DETACHED).Values = detached_price
.SeriesCollection(DETACHED).XValues = detached_date

它怎么知道要参考哪个系列? DETACHED是您在其他地方定义的常数吗?

How does it know which series to reference? Is DETACHED a constant you have defined elsewhere?

我认为这样会更好:

with objChart.SeriesCollection.NewSeries
    .Name = "Detached"
    .Values = detached_price
    .XValues = detached_date
end with

这篇关于在X轴上设置日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！