asked on

problem generating stock chart through macro

I use Excel 97. I want to create an Open-High-Low-Close stock chart through a macro. I keep macro recording on; invoke chartwizard; choose Stock; choose OpenHighLowClose; then point to the range, and then click Finish (skipping other details), and then stop recording.

On playing back, this macro does not work. ("Run-time error 1004, Method 'ChartType' of object '_Chart' failed")
It fails on the charttype= statement.

Here is the macro:
Sub Macro5()
'
' Macro5 Macro
' Macro recorded 28-12-99 by stochastic
'

'
Charts.Add
ActiveChart.ChartType = xlStockOHLC
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("E12:G16"), PlotBy _
:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

However, if I record again, this time by selecting the data range first and then invoking the chartwizard, the rest of the steps same, it works!

The difference in the macro is just the statement
Range("E12:G16").Select

at the start.

Now I would like to avoid the select statement if possible, and logically, it does not seem to be a necessity.

What do you think is the problem?

- stochastic

antrat

Hi stochastic

It seems the problem is due to the chart type, it looks as if Excel needs to know the data before it can create the ChartType xlStockOHLC. To see this you can change your chart type to xlLine and it should create it without a problem.

So below is a way that should work, it simply sets the chart range without selecting it.

Sub Macro5()
Dim ChrtRnge

Set ChrtRnge = Sheets("Sheet1").Range("E12:G16")

Application.ScreenUpdating=False
Charts.Add Before: = Worksheets("Sheet1")
ActiveChart.ChartType = xlStockOHLC
ActiveChart.SetSourceData Source:=ChrtRnge, PlotBy _
:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
Application.ScreenUpdating=True
End Sub

Hope it helps

antrat

billd1

I've checked both sets of code here, and neither are working. It seems as if it doesn't like the xlStock charttype. I can get it to work with just about any other chart type, but that one. It seems as if we're stuck with selecting the range first, then activating the chart wizard. But I'm still looking for an explaination

ASKER CERTIFIED SOLUTION

billd1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stochastic

ASKER

antrat,
I'm afraid your suggestion didn't work. Same error continues. If you did get it to work, maybe you could describe what you did, a bit more.

One more discovery: out of the range e12:g16, the top row e12:g12 contains labels. This seems to be necessary for Excel to understand that data is to be taken "byrows"; if these cells are also values, there are more problems!

billd1,
your 'stupid' program worked!

Even in this case, e12:g12 need to be labels; otherwise if they are values, it doesn't work! Try it out for some more fun!

I really enjoy these glorious uncertainties of how all things Microsoft work (or don't!) Imagine - but for Microsoft, life would have been so boring!

Please permit me to wait and watch for some more comments before giving you the points.

- stochastic

antrat

Hi stochastic

looks like billd1 found the answer, guess that will teach me to test the code before posting it, sorry about that I felt sure it would work.

antrat