vba 在 X 轴上设置日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20109036/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Setting dates on X axis
提问by Eric Yeoman
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:
我正在尝试在 Excel VBA 中创建图表，但在让 X 轴正确显示日期时遇到问题；代码如下：
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
The properties variable in the For..Each loop is populated, and fills the arrays correctly.
For..Each 循环中的属性变量已填充，并正确填充数组。
However, although the Scatter Graph data points are shown, the dates on the axis all show 1900.
但是，虽然显示了 Scatter Graph 数据点，但轴上的日期都显示为 1900。
I tried adding the lines:
我尝试添加以下行：
.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.
我已经尝试了一些其他的东西，但它纯粹是在反复试验的基础上。
The data is as follows
数据如下
The resulting charts are:
结果图表是：
Correct dates, no data points
正确的日期，没有数据点
Incorrect dates, but we have data points
日期不正确，但我们有数据点
回答by Cool Blue
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.
尽管我不同意他们对“通用”日期格式的定义 :q，但我认为 @chiliNUT 有所作为。日期格式的强制似乎存在一些问题。
If you change all of your Datetype variables to Doubleor Long, it should work.
如果您将所有Date类型变量更改为Doubleor Long，它应该可以工作。
For example change
例如改变
ReDim detached_date(detachedProps.count - 1) As Date
to
到
ReDim detached_date(detachedProps.count - 1) As Double
or
或者
ReDim detached_date(detachedProps.count - 1) As Long
This way the dates are not converted into Stringby the XValuesmethod. They are stored as date serial numbers and the axis routine is able to coerce them successfully into the local date format.
这样日期就不会String被XValues方法转换成。它们存储为日期序列号，轴例程能够成功地将它们强制转换为本地日期格式。
Whats happening in your code is the Datetypes are coerced to Stringby the XValuesmethod and the axis rendering routine seems to be unable to coerce them back into dates properly.
您的代码中发生的事情是Date类型被String该XValues方法强制转换，并且轴渲染例程似乎无法将它们正确地强制转换回日期。
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.
我很想听听比我知识渊博的人的意见。
Also, I'm curious about this:
另外，我对这个很好奇：
.SeriesCollection.NewSeries
.SeriesCollection(DETACHED).Name = "Detached"
.SeriesCollection(DETACHED).Values = detached_price
.SeriesCollection(DETACHED).XValues = detached_date
How does it know which series to reference? Is DETACHED a constant you have defined elsewhere?
它如何知道要引用哪个系列？DETACHED 是您在别处定义的常量吗？
I would think this would be better:
我认为这会更好：
with objChart.SeriesCollection.NewSeries
.Name = "Detached"
.Values = detached_price
.XValues = detached_date
end with
