VBA error when creating certain chart types

[John Peterson]

I'm trying to create a stock chart in Excel 2002 using
the VBA snippet below, but it always gives me the
following 1004 error:

Method 'ChartType' of object '_Chart' failed.

Here is the code snippet:

Dim Cht As Chart
Set Cht = ThisWorkbook.Charts.Add()
Cht.ChartType = xlStockHLC ' ERROR!

This works for every type of chart _except_ stock charts
and surface charts (types 83 - 91, interestingly).
Also, I can create those types of charts manually and
with the macro recorder, but they fail when run in VBA.

Any ideas?

[John Walkenbach]

A stock chart is a special type of chart that requires a certain number of
series. You'll need to specify the data before you specify the type.
Something like this:

Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C10")
ActiveChart.ChartType = xlStockHLC

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss

"John Peterson" <JEPet...@foglo.com> wrote in message
news:42e801c1fe9e$58df4680$a4e62ecf@tkmsftngxa06...

[John Peterson]

John,

Thanks! That did the trick.

Now I have another question: WHERE OH WHERE is stuff like
this documented? I can't find good documentation on Excel
charting anywhere. Sure, the object model
is "documented", but not with the kind of info that you
have so kindly provided.

I have run into other order-dependent things in Excel
charting, so I'm not surprised by this. Maybe I would
have discovered the secret myself after wasting more time
experimenting. But I have never found trial-and-error to
be a satisfactory method of creating code. Hopefully
there is a better way!

[John Walkenbach]

> . But I have never found trial-and-error to
> be a satisfactory method of creating code. Hopefully
> there is a better way!

When you're dealing with VBA and Excel, trial-and-error simply can't be
avoided. When I set out to develop an Excel/VBA application, I usually end
up doing dozens of small "experiments" to determine how a particular method
or property works under various situations. These experiments are usually
done in a separate workbook in which everything is simplified and under
control (i.e., not within my app itself).

In the case you site, I remember trying to create a stock market chart with
an insufficient number of series. The Chart Wizard displayed a message
saying that it was not possible. Your question reminded me of that, and the
solution then seemed obvious. It took me 60 seconds to create a simple
experiment to verify it. I never consider these experiments to be a waste
of time.

After working with Excel for many years, my brain is crammed with tiny facts
similar to that. Some of them are archived in my books, but most of them
aren't. Mastering Excel is not something that can be done by reading books.
It definitely takes on-the-job experience -- lots of it. And, of course, a
browser window aimed at www.groups.google.com is also useful.

Your code is also a good example of a recorded macro that generates an error
when it is executed. Further proof that the best use of the macro recorder
is as a learning aid -- not as a way to generate usable code.

Hang in there, and let trial-and-error be your friend. :)