excel - XY Scatter Plot and Dictionary problems -

i have function generate xy scatter plots based on number of dictionaries (with each dictionary representing line on graph), each containing date key , number value. far values seem work on y axis, date axis (x) seems broken. every time add series graph dictionary, forces bar graph, when want scatter plot. if force scatter plot after assigning it, refuses display date axis @ all.

here examples.

i want graph

if tell not use date, graph looks this

when set data type of series xldate, graph changes this. has mysteriously changed bar graph

if change scatter plot after setting use xldate, looks this

any appreciated. here vba code

sub generateprogressgraph()   dim dictionaries(1 2) new dictionary      dictionaries(1).add datevalue("1/2/2012"), 1     dictionaries(1).add datevalue("2/2/2012"), 2     dictionaries(1).add datevalue("3/2/2012"), 3     dictionaries(1).add datevalue("4/2/2012"), 4      dictionaries(2).add datevalue("1/2/2012"), 1     dictionaries(2).add datevalue("2/2/2012"), 1     dictionaries(2).add datevalue("3/2/2012"), 3     dictionaries(2).add datevalue("4/2/2012"), 4      call processprogressgraph(dictionaries) end sub  sub processprogressgraph(dict() dictionary)      dim graph shape     dim graphrange range      activesheet          'set graph area         set graphrange = application.range("e4:p21")          'add new chart         set graph = shapes.addchart(xlxyscatterlinesnomarkers, graphrange.left, _                                     graphrange.top, graphrange.width, graphrange.height)          graph.chart              .axes(xlcategory)                 .hastitle = true                 .axistitle.characters.text = "dates"             end              .hastitle = true             .charttitle.text = "chart title"             .charttype = xlxyscatterlinesnomarkers              'clear chart data             '(excel has tendency give silly resultsets default)             each srs in .seriescollection                 srs.delete             next              each dictionary in dict                 dim ss series                 set ss = .seriescollection.newseries                 ss.name = "values"                 ss.xvalues = dictionary.keys                 ss.type = xldate                 .charttype = xlxyscatterlinesnomarkers 'this forces scatter plot since auto makes bar graph                 ss.values = dictionary.items             next         end     end end sub 

the problem lies in excel's native handling of x axis values. honest, have no idea why happens, know how solve it:

1. get x axis date values , cast them type long using this:

   redim longdates(dictionary.count) long = lbound(dictionary.keys) ubound(dictionary.keys)     longdates(i) = dictionary.keys(i) next 

2. assign longdates x axis values using ss.xvalues = longdates

3. set ticklabel number format date @ end of function using:

   .axes(xlcategory).ticklabels.numberformat = "d/mm/yyyy" 

that should work