VBA Excel Charts: Enter Array as XValue on Date Axis

Question

I am trying to create/modify an Excel line chart with a Date x-axis using VBA and assigning the Series.Xvalues using an array rather than a range:

Sub test()
  Dim c As Chart: Set c = ActiveChart
  With c.SeriesCollection(1)
    .XValues = Array(42228, 42229, 42235, 42277, 42338, 42613)        
    .Values = Array(1, 2, 3, 4, 5, 6)  
  End With
End Sub

The problem is when I run that the .resulting .XValues are of type Variant/String rather than Variant/Double, so the chart shows as if the x-axis were a Text axis rather than a Date axis (i.e. the points appear evenly spaced along the x-axis):

However if I save the file, close it and then reopen it magically appears as a Date axis, with the points spaced along the x-axis according to their true distance:

If I inspect the Series.XValues object after reopening, the array elements have been transformed into Variant/Double. I tried coercing them manually before reopening but it causes Excel to crash. Actually even if I just copy the chart it's enough to make it appear properly, so somehow Excel "knows" it's a date axis but is just not showing it as such. I'm going to have to use that as the solution (i.e. make a copy and delete the old one whenever I change the chart), however it's pretty clunky and I would love to figure out what's going on here. I found this http://www.ozgrid.com/forum/showthread.php?t=66504 but he doesn't seem to have the same problem. I'm using Excel 2010.

Answer 1

When I execute this expression in the Immediate Window:

TypeName(Array(42228, 42229, 42235, 42277, 42338, 42613)(1))

VBA returns "Long". So I believe your input is already numeric.

I think Excel is "helping you" by changing the number format of your tick labels to a non-date format. You can change the format back to date with something like this:

ch.Axes(xlCategory).TickLabels.NumberFormat = "m/d/yyyy"

Hope that helps

Answer 2

When your X value array was passed in, they were not in a date format. Excel, being ever so helpful, decided the axis was no longer a date scale axis. But you can easily fix that:

Sub test()
  Dim c As Chart
  Set c = ActiveChart
  With c.SeriesCollection(1)
    .XValues = Array(42228, 42229, 42235, 42277, 42338, 42613)
    .Values = Array(1, 2, 3, 4, 5, 6)
  End With
  With c.Axes(xlCategory, xlPrimary)
    .CategoryType = xlTimeScale
    .TickLabels.NumberFormat = "m/dd/yy"
  End With
End Sub

My initial thought, which didn't work, was to enter the values as dates:

Array(#8/12/2015#, #8/13/2015#, #8/19/2015#, #9/30/2015#, #11/30/2015#, #8/31/2016#)

but this made the axis behave as a text axis, with the dates equally spaced. Although the Date Axis option was selected, the rest of the axis options was the same as a text axis, and didn't show the date scaling section of a date axis.