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
LVL 8
stochasticAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

antratCommented:
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
0
billd1Commented:
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
0
billd1Commented:
HAHAHA.....stupid program...
Sub ChartTestOriginal()
Dim ch As Chart
    Set ch = Charts.Add
    ch.SetSourceData Source:=Sheets("Sheet1").Range("E12:G16"), PlotBy _
        :=xlRows
    ch.ChartType = xlStockOHLC
    ch.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

It works if you choose the chart type after setting the source data.  Don't ask me why, I have noooo idea.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stochasticAuthor Commented:
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
0
antratCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.