Drawing Charts with VBA
 

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

Drawing Charts with VBA
 

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)

--
Regards,
Tom Ogilvy

"Fred Russell" wrote in message
...

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

Drawing Charts with VBA
 

Tom,

How do I fix this?

Best regards,
Fred

Drawing Charts with VBA
 

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.

--
Regards,
Tom Ogilvy

"Fred Russell" wrote in message
...
Tom,

How do I fix this?

Best regards,
Fred

Drawing Charts with VBA
 

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.

Drawing Charts with VBA
 

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.

--
Regards,
Tom Ogilvy

"Fred Russell" wrote in message
...
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.