Drawing Charts with VBA

[Fred Russell]

Straight line charts work fine using VBA, but I couldn't get Stock charts to
work at all.
I used the following code:

Charts.Add
ActiveChart.ChartType = xlStockHLC
etc.
etc.


When running the code I get the following error message -

Run-time error '1004'

Method 'ChartType' of object '_Chart' failed.

When debugging, the line "ActiveChart.ChartType = xlStockHLC" is highlighted
in yellow and the values on both sides of the = sign are different. The left
side (ActiveChart.ChartType) = 55 and the right side (xlStockHLC) = 81.

Why would this happen with an assignment of chart type?

Best regards,
Fred

 

[Tom Ogilvy]

It would mean the assignment was not completed and the activechart is still
of charttype 55. On the right side, the constant xlStockHLC still retains
its value (apparently of 81)

 

[Fred Russell]

Tom,

How do I fix this?

Best regards,
Fred

 

[Tom Ogilvy]

have the macro completely build the line chart, then change it to a stock
chart.

If you don't meet all the required criteria to have a stock chart, you will
get an error.

A test would be to run the macro to build the complete chart. Then try to
change the chart type manually. If you succeed, that would be a good
indication that you can do it with code as well.

 

[Fred Russell]

Thanks Tom

FYI the problem with the ChartType was that even though the macro recorder
in excel records the order as:

Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Range("GraphTable"), PlotBy:=xlColumns

for certain chart types (Stock charting in particular) the data source has
to appear before the ChartType
i.e.

Charts.Add
ActiveChart.SetSourceData Source:=Range("GraphTable"), PlotBy:=xlColumns
ActiveChart.ChartType = xlStockVHLC

after making that correction it worked fine.

 

[Tom Ogilvy]

That's what I was driving at. I guess I should have said order is important
and the macro recorder doesn't always get it right.