Line chart - time series - category axis date format (2007 VBA)

[Paul Jacob]

I'm using VBA in Excel 2007 to plot time series as line charts (i.e., data on
Y axis, dates on X axis, ChartType = xlLineMarkers). My data's not in a neat
"block", so I can't use the ChartWizard or SetSourceData methods. For Y-axis
data I'm using:

With ActiveChart
..SeriesCollection.Add Source:= [a range variable with my data]

How do I set up the X-Axis so that I can format the dates? I've tried:
..Axes(xlCategory).CategoryType = xlTimeScale
..Axes(xlCategory).CategoryNames = [a range variable which is a column of
dates]

However, Excel treats the CategoryNames as strings. CategoryType appears to
be ignored.

Anyone know what I'm doing wrong?

 

[Jon Peltier]

Make sure that all of the cells which look like dates to you are real
numeric dates. It only takes on to be entered as text for Excel to ignore
the time scale setting.

Another point, though you have some kind of routine to use data from diverse
locations, Excel is more reliable when charting from orderly ranges. You
should either bring the data together, or set up another range which links
to the data wherever it is.

- Jon