Chart Property (e.g.: .HasTitle) reset fails with run-time error 1

[Joseph Felcon]

Hello,

I am getting a run-time 1004 error when trying to program properties for a
chart. Actually, it is the second chart which I create which is invoking
the error message.

I am creating multiple worksheets, each with one chart. The first worksheet
and chart work fine, but the second and third worksheet/chart cause 1004
errors on certain property values...

' create a new worksheet for chart flow
Application.DisplayAlerts = False
Set wsChartFlow = Worksheets.Add
wsChartFlow.Name = "Chart Flow"

' create the chart flow object
Set coFlow = wsChartFlow.ChartObjects.Add(0, 0, 800, 600)
coFlow.Name = "Net Flow over Time"
Set chFlow = coFlow.Chart
Application.DisplayAlerts = True

' add data to chart for Flow
chFlow.HasTitle = True
chFlow.ChartTitle.Caption = "Net Flow over Time"
chFlow.ChartType = xlXYScatterLines
chFlow.PlotBy = xlColumns

.... HEREIN I ADD SERIES AND GENERALLY FORMAT THE CHART TO MY LIKING ...
.... I ORIGINALLY TRIED ...

' create a new worksheet for chart fuel
Application.DisplayAlerts = False
Set wsChartFuel = Worksheets.Add
wsChartFuel.Name = "Chart Usage"

' create the chart fuel object
Set coFuel = wsChartFuel.ChartObjects.Add(0, 0, 5 * iCopy * m_iSelected,
600)
coFuel.Name = "Fuel Use over Time"
Set chFuel = coFuel.Chart
Application.DisplayAlerts = True

' add data to chart for Fuel
chFuel.HasTitle = True
chFuel.ChartTitle.Caption = "Fuel Use over Time"
chFuel.ChartType = xlColumnClustered
chFuel.PlotBy = xlColumns

.... IN THIS SEQUENCE, .HasTitle AND .ChartTitle.Caption GENERATE 1004'S, BUT
THE .ChartType AND .PlotBy PROPERTIES WORK JUST FINE.

I tried adding chFuel.Activate and received a 1004 for my trouble; so I
tried wsChartFuel.Activate (the sheet) and
wsChartFuel.ChartObjects(1).Activate (and the equivalent coFuel.Activate),
all of which work perfectly well, but still don't let me set any of the
problematic chart properties.

Being able to set some properties of the "chart" and getting a runtime error
when trying to set others is really most annoying. In fact, I cannot even
modify these properties in the debug "watches" window (I get 1004 there too).

Any help would be greatly appreciated.

Joseph A. J. Felcon

 

[Peter T]

Are you sure you are doing

' add data to chart for Fuel
chFuel.HasTitle = True

If instead you are actually doing

'no series added yet
chFuel.HasTitle = True
' add data to chart for Fuel

would be one reason why doing HasTitle = true fails. IOW add at least one
series first.

In passing I don't follow how you are adding multiple sheets like this (all
with same name?)

Set wsChartFuel = Worksheets.Add
wsChartFuel.Name = "Chart Usage"

also can't think why you'd need to disable DisplayAlerts (unless deleting
sheets) though you may well want to disable screenupdating

Regards,
Peter T

 

[Joseph Felcon]

Sir,

Thanks for the information. I was unaware that one had to put data into a
chart before being able to add a title to it. I have tried it and it works,
but I don't like it.

This is a hidden "feature" (so typical of our friends at MS), because it is
nowhere stated in the documentation. It also violates several principles of
object oriented design to tie together unrelated properties in this way,
especially when the owned objects, themselves, have a property of the same
name. It may be right, but it is definitely not proper. It is also both
arbitrary and counterintuitive, and I am curious as to how you would know
this.

As for your comments about the code, I think that you misread. The
worksheet objects have distinct names (one is Fuel and one is Flow). Turning
off Alerts relieves my customers from seeing a lot of messages about internal
ops and having to click OK a few hundred times, while seeing screen updates
under the driver form is a positive, because it gives them a sense of
activity (i.e.: that something is actually happening while they wait).

Thanks again for information. It works and I will keep it in mind while
working with this, or similar, Excel objects, but I still think that it is
improper.

Joseph A. J. Felcon

 

[Peter T]

It may be right, but it is definitely not proper.

I like it!
I'll file it with "The customer is always right but the customer is not
always correct"

It is also both arbitrary and counterintuitive, and I am curious
as to how you would know this.

There are many things about the Excel chart object and how it is exposed to
VBA which are, for want of a better word, odd. The one you have picked up on
I'd describe as no more than a quirk.

Regards,
Peter T

 

[Joseph Felcon]

Sir,

Odd, indeed! And it starts with functionality shared between a "Chart
Object" object and a "Chart" object, which is semantically confusing to say
the least.

And I don't mind the documented oddities; what makes me a little nuts are
those undocumented quirks that one is just supposed to intuit. They can make
a poor developer into a drooling idiot (or at least, to be so perceived by
his/her manager, which may have a very detrimental effect on one's career
path).

Joseph A. J. Felcon