I am building an Column-Line on 2 Axes chart via Automation from Access.
It is basically working, but for some reason, I can't get it to set the
Category (x) axis labels now.
I am now getting a 1004 "unable to set the CategoryNames property of the
Axis class" error message.
I am setting it with:
Set oXLCAx = oXLC.Axes(xlCategory, xlPrimary)
With oXLCAx
.CategoryNames = xlWS.Range(oXAxis.RangeAddress)
<...snip...
end with

Current, my oXAxis.RangeAddress object is holding the following value:
"[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$30:$B$31"

Does it have a problem accepting a non-contiguous address range?
I have previously defines 8 series (4 each on the primary and secondary axes)

....or have I made some other sort of sequence error in creating the
seriescollections? (I found that I had to make a 4 primary series, and add
the 4 secondary ones, doing it pair by pair did not seem to work properly and
the series' wound up getting mixed up on the wrong axes somehow (despite
putting the .axisgroup = xlprimary or .axisgroup = xlsecondary on each series
object)!?

(I also tried to set the .XValues when creating/adding the series with my
oXAxis.Rangeaddress value, and it would not take that either...=1004 error.)

Any suggestions?

It would be easier to make a regular chart, not a built-in custom type, and
assign the chart type and axis group to each series as it is added. The
built-in custom types ignore what you say and place the first half or so
into one style and the last half or so into the other.

Also, I generally ignore the .CategoryNames in favor of using the same range
as .XValues for all series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Mark Burns" wrote in message
...
I am building an Column-Line on 2 Axes chart via Automation from Access.
It is basically working, but for some reason, I can't get it to set the
Category (x) axis labels now.
I am now getting a 1004 "unable to set the CategoryNames property of the
Axis class" error message.
I am setting it with:
Set oXLCAx = oXLC.Axes(xlCategory, xlPrimary)
With oXLCAx
.CategoryNames = xlWS.Range(oXAxis.RangeAddress)
<...snip...
end with

Current, my oXAxis.RangeAddress object is holding the following value:
"[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$30:$B$31"

Does it have a problem accepting a non-contiguous address range?
I have previously defines 8 series (4 each on the primary and secondary
axes)

...or have I made some other sort of sequence error in creating the
seriescollections? (I found that I had to make a 4 primary series, and add
the 4 secondary ones, doing it pair by pair did not seem to work properly
and
the series' wound up getting mixed up on the wrong axes somehow (despite
putting the .axisgroup = xlprimary or .axisgroup = xlsecondary on each
series
object)!?

(I also tried to set the .XValues when creating/adding the series with my
oXAxis.Rangeaddress value, and it would not take that either...=1004
error.)

Any suggestions?

Jon,

1st: Thanks for the feedback! I appreciate your time and insights.

Ok, so what are the "general rules" for "making a regular chart" then?
i.e.: when/how do you add axes -vs- series...?
How (and when, sequence-of-operations-wise) do you tell the charting
component what chart type to draw / what to do with each series...?
is there a list of valid chart or series format types around anywhere
(probably asking alot, I know)...

Is there a particular reason you don't assign the .categorynames to the
xlCategory, xlPrimary axis yourself - or is it more just a personal
preference thing? (or is it that .XYvalues does the same thing
behind-the-scenes?)


"Jon Peltier" wrote:

It would be easier to make a regular chart, not a built-in custom type, and
assign the chart type and axis group to each series as it is added. The
built-in custom types ignore what you say and place the first half or so
into one style and the last half or so into the other.

Well...that 'splains some of that apparent oddnesss, then.
I didn't have enough time to figure out what things that
assigning/reassigning the charttype did or didn't step on, either.

Also, I generally ignore the .CategoryNames in favor of using the same range
as .XValues for all series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Mark Burns" wrote in message
...
I am building an Column-Line on 2 Axes chart via Automation from Access.
It is basically working, but for some reason, I can't get it to set the
Category (x) axis labels now.
I am now getting a 1004 "unable to set the CategoryNames property of the
Axis class" error message.
I am setting it with:
Set oXLCAx = oXLC.Axes(xlCategory, xlPrimary)
With oXLCAx
.CategoryNames = xlWS.Range(oXAxis.RangeAddress)
<...snip...
end with

Current, my oXAxis.RangeAddress object is holding the following value:
"[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$30:$B$31"

Does it have a problem accepting a non-contiguous address range?
I have previously defines 8 series (4 each on the primary and secondary
axes)

...or have I made some other sort of sequence error in creating the
seriescollections? (I found that I had to make a 4 primary series, and add
the 4 secondary ones, doing it pair by pair did not seem to work properly
and
the series' wound up getting mixed up on the wrong axes somehow (despite
putting the .axisgroup = xlprimary or .axisgroup = xlsecondary on each
series
object)!?

(I also tried to set the .XValues when creating/adding the series with my
oXAxis.Rangeaddress value, and it would not take that either...=1004
error.)

Any suggestions?

Here are some pointers:

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

Use the ChartObjects.Add method to add a chart to the worksheet
http://peltiertech.com/Excel/ChartsH....html#addchart

Use the SeriesCollection.NewSeries method to add a series to the chart:
http://peltiertech.com/Excel/ChartsH...html#addseries

Assign the data, series type, and axis group:

Sub AddNewSeries()
With ActiveChart.SeriesCollection.NewSeries
.Name = "Fred"
.Values = "=Sheet1!Y_Range"
.XValues = Array(1, 2, 3) .ChartType = xlLine
..AxisGroup = xlPrimary
End With
End Sub
I use .Values and .XValues, because most of my charts are XY charts, and
each series has distinct X and Y values in this chart type. For consistency
I use the same approach for all charts.For a list of chart types, go to the
VB Editor, choose Object Browser from the View menu, select Series in the
list of Classes, and select ChartType in the list of Members of 'Series'. At
the bottom it says Property ChartType As XlChartTypewhere XlChartType is a
hyperlink. Click on this to get a list of chart type constants.-
Jon-------Jon Peltier, Microsoft Excel MVPTutorials and Custom
SolutionsPeltier Technical Services, Inc. - http://PeltierTech.com_______
"Mark Burns" wrote in message
...
Jon,

1st: Thanks for the feedback! I appreciate your time and insights.

Ok, so what are the "general rules" for "making a regular chart" then?
i.e.: when/how do you add axes -vs- series...?
How (and when, sequence-of-operations-wise) do you tell the charting
component what chart type to draw / what to do with each series...?
is there a list of valid chart or series format types around anywhere
(probably asking alot, I know)...

Is there a particular reason you don't assign the .categorynames to the
xlCategory, xlPrimary axis yourself - or is it more just a personal
preference thing? (or is it that .XYvalues does the same thing
behind-the-scenes?)


"Jon Peltier" wrote:

It would be easier to make a regular chart, not a built-in custom type,
and
assign the chart type and axis group to each series as it is added. The
built-in custom types ignore what you say and place the first half or so
into one style and the last half or so into the other.

Well...that 'splains some of that apparent oddnesss, then.
I didn't have enough time to figure out what things that
assigning/reassigning the charttype did or didn't step on, either.

Also, I generally ignore the .CategoryNames in favor of using the same
range
as .XValues for all series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Mark Burns" wrote in message
...
I am building an Column-Line on 2 Axes chart via Automation from Access.
It is basically working, but for some reason, I can't get it to set the
Category (x) axis labels now.
I am now getting a 1004 "unable to set the CategoryNames property of
the
Axis class" error message.
I am setting it with:
Set oXLCAx = oXLC.Axes(xlCategory, xlPrimary)
With oXLCAx
.CategoryNames = xlWS.Range(oXAxis.RangeAddress)
<...snip...
end with

Current, my oXAxis.RangeAddress object is holding the following value:
"[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$30:$B$31"

Does it have a problem accepting a non-contiguous address range?
I have previously defines 8 series (4 each on the primary and secondary
axes)

...or have I made some other sort of sequence error in creating the
seriescollections? (I found that I had to make a 4 primary series, and
add
the 4 secondary ones, doing it pair by pair did not seem to work
properly
and
the series' wound up getting mixed up on the wrong axes somehow
(despite
putting the .axisgroup = xlprimary or .axisgroup = xlsecondary on each
series
object)!?

(I also tried to set the .XValues when creating/adding the series with
my
oXAxis.Rangeaddress value, and it would not take that either...=1004
error.)

Any suggestions?

Jon,

Yo! DUDE!
....Wow!
I wish I'd known to come ask you all these questions a few days ago!
You are a veritable FOUNT of most useful knowledge.

"..AxisGoup"? I never know you could stack the . operators in VBA as you can
in DOS filespecs...and I've been doing VB/VBA for a LONG TIME too. Where'd I
miss that little tidbit at? (is .. the limit, or can it stack to ... and ....
too? nevermind I'll find out. Dang but I see how THAT could be trouble too.)

Oh, you do know that if you do that
"With ActiveChart.SeriesCollection.NewSeries"
trick with some COM objects, (creating the new object instance on the WITH
line, I mean) you _can_ create yourself some serious COM reference-count
problems and sometimes leave yourself with seemingly "hung"
applications/sessions...? <explanation follows - primarily for the lurkers
(The reason for that is the With construct creates a "phantom" object
reference in memory, but the pointer to it gets lost at the End With. The
hung COM object problem arises if that object instance holds referencing
pointers to itself or other COM class instances within its application - or
worse yet, if other instances manage to hold references to it when it goes
out of scope and VB/VBA "loses" it after the End With - then the object stays
alive in memory - meaning no Terminate Event fires - because its COM
reference count stays 0...)

It is *always* recommended to do this instead:
Set oSomeVarHere = <New WhateverObjectType
(or <any object/method which results in a new object instance here i.e.
".NewSeries")
With oSomeVarHere
....
<Do stuff with . object properties/methods here
....
End With
....
oSomeVarHere.Dispose() 'if you're trying to follow .NET iDisposable Semantics
set oSomeVarHere = Nothing 'assures destruction of the COM object...

....especially if the .Dispose() call makes sure that the oSomeVarHere
instance releases all its internal reference to other objects.
<end of explanation for benefit of lurkers

ps. FWIW, I've found that following the .Net iDisposable semantics is a good
idea even in VB6/VBA coding scenarios...it tends to make you a more careful
object/class instance consumer and help you avoid LOTS of potential COM
headaches in the bargain - primarily because it makes you THINK about your
object destruction calls in your code designs.

"Jon Peltier" wrote:

Here are some pointers:

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

Use the ChartObjects.Add method to add a chart to the worksheet
http://peltiertech.com/Excel/ChartsH....html#addchart

Use the SeriesCollection.NewSeries method to add a series to the chart:
http://peltiertech.com/Excel/ChartsH...html#addseries

Assign the data, series type, and axis group:

Sub AddNewSeries()
With ActiveChart.SeriesCollection.NewSeries
.Name = "Fred"
.Values = "=Sheet1!Y_Range"
.XValues = Array(1, 2, 3) .ChartType = xlLine
..AxisGroup = xlPrimary
End With
End Sub
I use .Values and .XValues, because most of my charts are XY charts, and
each series has distinct X and Y values in this chart type. For consistency
I use the same approach for all charts.For a list of chart types, go to the
VB Editor, choose Object Browser from the View menu, select Series in the
list of Classes, and select ChartType in the list of Members of 'Series'. At
the bottom it says Property ChartType As XlChartTypewhere XlChartType is a
hyperlink. Click on this to get a list of chart type constants.-
Jon-------Jon Peltier, Microsoft Excel MVPTutorials and Custom
SolutionsPeltier Technical Services, Inc. - http://PeltierTech.com_______
"Mark Burns" wrote in message
...
Jon,

1st: Thanks for the feedback! I appreciate your time and insights.

Ok, so what are the "general rules" for "making a regular chart" then?
i.e.: when/how do you add axes -vs- series...?
How (and when, sequence-of-operations-wise) do you tell the charting
component what chart type to draw / what to do with each series...?
is there a list of valid chart or series format types around anywhere
(probably asking alot, I know)...

Is there a particular reason you don't assign the .categorynames to the
xlCategory, xlPrimary axis yourself - or is it more just a personal
preference thing? (or is it that .XYvalues does the same thing
behind-the-scenes?)


"Jon Peltier" wrote:

It would be easier to make a regular chart, not a built-in custom type,
and
assign the chart type and axis group to each series as it is added. The
built-in custom types ignore what you say and place the first half or so
into one style and the last half or so into the other.

Well...that 'splains some of that apparent oddnesss, then.
I didn't have enough time to figure out what things that
assigning/reassigning the charttype did or didn't step on, either.

Also, I generally ignore the .CategoryNames in favor of using the same
range
as .XValues for all series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Mark Burns" wrote in message
...
I am building an Column-Line on 2 Axes chart via Automation from Access.
It is basically working, but for some reason, I can't get it to set the
Category (x) axis labels now.
I am now getting a 1004 "unable to set the CategoryNames property of
the
Axis class" error message.
I am setting it with:
Set oXLCAx = oXLC.Axes(xlCategory, xlPrimary)
With oXLCAx
.CategoryNames = xlWS.Range(oXAxis.RangeAddress)
<...snip...
end with

Current, my oXAxis.RangeAddress object is holding the following value:
"[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$30:$B$31"

Does it have a problem accepting a non-contiguous address range?
I have previously defines 8 series (4 each on the primary and secondary
axes)

...or have I made some other sort of sequence error in creating the
seriescollections? (I found that I had to make a 4 primary series, and
add
the 4 secondary ones, doing it pair by pair did not seem to work
properly
and
the series' wound up getting mixed up on the wrong axes somehow
(despite
putting the .axisgroup = xlprimary or .axisgroup = xlsecondary on each
series
object)!?

(I also tried to set the .XValues when creating/adding the series with
my
oXAxis.Rangeaddress value, and it would not take that either...=1004
error.)

Any suggestions?

Mark -

Far as I know, there's no limit to the levels of "." you can use, as long as
the OM goes that deep.

I generally use Set Blah = New Object/With Blah, unless it's a small little
thing like this. The variable Blah gets reused in the loop (adding a set of
series), then soon goes out of scope when the function or sub ends.

I'm not familiar with the .Net stuff. I've been told it's okay to let VB/VBA
set everything to nothing internally when a procedure ends.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Mark Burns" wrote in message
...
Jon,

Yo! DUDE!
...Wow!
I wish I'd known to come ask you all these questions a few days ago!
You are a veritable FOUNT of most useful knowledge.

"..AxisGoup"? I never know you could stack the . operators in VBA as you
can
in DOS filespecs...and I've been doing VB/VBA for a LONG TIME too. Where'd
I
miss that little tidbit at? (is .. the limit, or can it stack to ... and
....
too? nevermind I'll find out. Dang but I see how THAT could be trouble
too.)

Oh, you do know that if you do that
"With ActiveChart.SeriesCollection.NewSeries"
trick with some COM objects, (creating the new object instance on the WITH
line, I mean) you _can_ create yourself some serious COM reference-count
problems and sometimes leave yourself with seemingly "hung"
applications/sessions...? <explanation follows - primarily for the
lurkers
(The reason for that is the With construct creates a "phantom" object
reference in memory, but the pointer to it gets lost at the End With. The
hung COM object problem arises if that object instance holds referencing
pointers to itself or other COM class instances within its application -
or
worse yet, if other instances manage to hold references to it when it goes
out of scope and VB/VBA "loses" it after the End With - then the object
stays
alive in memory - meaning no Terminate Event fires - because its COM
reference count stays 0...)

It is *always* recommended to do this instead:
Set oSomeVarHere = <New WhateverObjectType
(or <any object/method which results in a new object instance here i.e.
".NewSeries")
With oSomeVarHere
...
<Do stuff with . object properties/methods here
...
End With
...
oSomeVarHere.Dispose() 'if you're trying to follow .NET iDisposable
Semantics
set oSomeVarHere = Nothing 'assures destruction of the COM object...

...especially if the .Dispose() call makes sure that the oSomeVarHere
instance releases all its internal reference to other objects.
<end of explanation for benefit of lurkers

ps. FWIW, I've found that following the .Net iDisposable semantics is a
good
idea even in VB6/VBA coding scenarios...it tends to make you a more
careful
object/class instance consumer and help you avoid LOTS of potential COM
headaches in the bargain - primarily because it makes you THINK about your
object destruction calls in your code designs.

"Jon Peltier" wrote:

Here are some pointers:

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

Use the ChartObjects.Add method to add a chart to the worksheet
http://peltiertech.com/Excel/ChartsH....html#addchart

Use the SeriesCollection.NewSeries method to add a series to the chart:
http://peltiertech.com/Excel/ChartsH...html#addseries

Assign the data, series type, and axis group:

Sub AddNewSeries()
With ActiveChart.SeriesCollection.NewSeries
.Name = "Fred"
.Values = "=Sheet1!Y_Range"
.XValues = Array(1, 2, 3) .ChartType = xlLine
..AxisGroup = xlPrimary
End With
End Sub
I use .Values and .XValues, because most of my charts are XY charts, and
each series has distinct X and Y values in this chart type. For
consistency
I use the same approach for all charts.For a list of chart types, go to
the
VB Editor, choose Object Browser from the View menu, select Series in the
list of Classes, and select ChartType in the list of Members of 'Series'.
At
the bottom it says Property ChartType As XlChartTypewhere XlChartType is
a
hyperlink. Click on this to get a list of chart type constants.-
Jon-------Jon Peltier, Microsoft Excel MVPTutorials and Custom
SolutionsPeltier Technical Services, Inc. - http://PeltierTech.com_______
"Mark Burns" wrote in message
...
Jon,

1st: Thanks for the feedback! I appreciate your time and insights.

Ok, so what are the "general rules" for "making a regular chart" then?
i.e.: when/how do you add axes -vs- series...?
How (and when, sequence-of-operations-wise) do you tell the charting
component what chart type to draw / what to do with each series...?
is there a list of valid chart or series format types around anywhere
(probably asking alot, I know)...

Is there a particular reason you don't assign the .categorynames to the
xlCategory, xlPrimary axis yourself - or is it more just a personal
preference thing? (or is it that .XYvalues does the same thing
behind-the-scenes?)


"Jon Peltier" wrote:

It would be easier to make a regular chart, not a built-in custom
type,
and
assign the chart type and axis group to each series as it is added.
The
built-in custom types ignore what you say and place the first half or
so
into one style and the last half or so into the other.

Well...that 'splains some of that apparent oddnesss, then.
I didn't have enough time to figure out what things that
assigning/reassigning the charttype did or didn't step on, either.

Also, I generally ignore the .CategoryNames in favor of using the same
range
as .XValues for all series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Mark Burns" wrote in message
...
I am building an Column-Line on 2 Axes chart via Automation from
Access.
It is basically working, but for some reason, I can't get it to set
the
Category (x) axis labels now.
I am now getting a 1004 "unable to set the CategoryNames property of
the
Axis class" error message.
I am setting it with:
Set oXLCAx = oXLC.Axes(xlCategory, xlPrimary)
With oXLCAx
.CategoryNames = xlWS.Range(oXAxis.RangeAddress)
<...snip...
end with

Current, my oXAxis.RangeAddress object is holding the following
value:
"[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28,
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$30:$B$31"

Does it have a problem accepting a non-contiguous address range?
I have previously defines 8 series (4 each on the primary and
secondary
axes)

...or have I made some other sort of sequence error in creating the
seriescollections? (I found that I had to make a 4 primary series,
and
add
the 4 secondary ones, doing it pair by pair did not seem to work
properly
and
the series' wound up getting mixed up on the wrong axes somehow
(despite
putting the .axisgroup = xlprimary or .axisgroup = xlsecondary on
each
series
object)!?

(I also tried to set the .XValues when creating/adding the series
with
my
oXAxis.Rangeaddress value, and it would not take that either...=1004
error.)

Any suggestions?

Jon,

(replies inline below)

"Jon Peltier" wrote:

Mark -

Far as I know, there's no limit to the levels of "." you can use, as long as
the OM goes that deep.

Yeah, but what, exactly will you get given, say:
dim o as Excel.Chart

Set o = Activesheet.charts(1)
with o
(if there is no real .name property for what follows, then let's pretend
there is for this example)
?.name ' = the chart object's name
?..name ' = the charts collection's name? or the activesheet's name?
?...name '= the workbook's name? or the application's name?
end with
set o = nothing
....know what I mean?
In other words, will you get the "absolute" object heirarchy tree's
ascendants, or (far less likely, I think) only the object ascendants directly
in the tree branch for how the variable was declared/instantiated?

I generally use Set Blah = New Object/With Blah, unless it's a small little
thing like this. The variable Blah gets reused in the loop (adding a set of
series), then soon goes out of scope when the function or sub ends.

I'm not familiar with the .Net stuff. I've been told it's okay to let VB/VBA
set everything to nothing internally when a procedure ends.

Most times, that's true for VBA. However, there are some circumstances where
it is certainly NOT true (relying on VBA to clean up your phantom object
instances from While blocks being just one example of such - if you do not
think this is an issue, try using that approach to automate one office app
from another, and see what happens when you try to shut your app down - the
"automated" app instance may not close because there are open object
instances hanging around in memory which can hold it open despite calling
oOfficeApp.Quit correctly 20 times).

Additionally, since Microsoft is obviously pushing rather hard to get
developers to move from VBA for Office development into using Visual
Studio.Net and Visual Studio Tools for Office (VSTO) instead, the proverbial
handwriting is on the wall for all to see which direction they would prefer
us to go.

(If only because one can infer from their allowing the VBA tool development
effort pretty much languish while enhancing VSTO - and VS.NET - with each
version of Office, so that where they are and are NOT spending their time and
$$ becomes pretty obvious. If you want proof of that statement, look no
farther then the list of improvements for VBA developers in Office 2007. It's
basically a one-item list of improvements: we now get to use the mouse wheel
in VBA in Office 2007. End of list. Want to compare that with the list of
enhancements for VSTO for Office 2003 to the VSTO for Office 2007 list? <a
long list)

Since .Net is obviously the direction MS would prefer us to go, it would
probably be better to start using coding methodologies which are MORE
compatible with .Net then less. Continuing to use the "Oh, it'll all get
cleaned up when VB closes the sub/function and the variables all fall out of
scope" mindset is NOT a compatible development pattern for the .Net world.
....unless you want to really cause yourself tons of unpredictable and
erratic-seeming problems with file handles, recordsets, connections, locks,
etc.,. because you're still letting object variables bound to application,
database, or even some UI resources (like pallettes and cursors) just "fall
out of scope"...where they continue to sit around in memory holding on to
their precious, limited-commodity resources like record locks and database
connection licenses, until the .NET Garbage collector "gets around to"
cleaning up and finalizing those obejcts at some random interval "later"...

Look, I'm really not trying to preach here or tell you what to do. I'm just
trying to point out the pitfalls which lie ahead on that road, and offer an
easier path forward to .net - one that isn't very incompatible with what we
do now. It just requires a little more thought and care with our object
instances and a few simple rules:

1) *ALWAYS* clean up your object variables when you are done playing with
them.
(This is the same rule your parents and teachers taught you in/before
kindergarten: "Put away your Toys when you are done with them!")
Ex: *always* Put a Set oMyObjectVariable = Nothing
*Before* End/Exit Sub or End/Exit Function (just make sure you .Close any
recordsets or similar items before setting them to nothing too)

2) if you are defining your own classes, in addition to any Class_Initialize
and Class_Terminate code you may have, Add a Public DISPOSE() method.
In your DISPOSE method, add code to clean up any object variables your class
may use during its lifespan. (You may also want to add a private Class-level
bIsDisposed boolean variable that gets set True during DISPOSE() calls. Then
you can add some simple logic like:

If Not bIsDisposed then
<do whatever it is you do here
else
Err.Raise 5, "MyClass.WhateverPropertyOrMethodNameHere()", _
"Illegal procedure Call - This object instance has been DISPOSEd already."
endif

around the properties and methods you use (at least those which reference
any objects needing cleanup when Disposing of the class instance).

Then, using your classes, becomes simply:
<sub/function whatever
Set o = MyClassNameHere
<do stuff with with o
o.Dispose()
set o = nothing
<end/exit sub or end/exit function

Do this, and your code will move MUCH MORE SMOOTHLY over to the .net world
if and when necessary.

That's all I'm trying to say (that, and by way of thanks I'm trying to share
what I've learned the hard way a long time ago as you did with me here
yesterday and today).

I see that I've misunderstood your reply to what turns out to be a typo. My
code had ".AxisGroup" with a single dot, but in your reply it was changed to
"..AxisGroup" with two dots. Each dot in the hierarchical reference string
must separate an object on the left from a member of that object on the
right. To go back up the chain, you need to use ".Parent."

Sub ParentTrap()
Dim o As ChartObject
Set o = ActiveSheet.ChartObjects(1)
With o
Debug.Print .Name ' name of chart object
Debug.Print .Parent.Name ' name of sheet
Debug.Print .Parent.Parent.Name ' name of workbook
Debug.Print .Parent.Parent.Parent.Name ' name of application
Debug.Print .Parent.Parent.Parent.Parent.Name ' name of application
Debug.Print .Parent.Parent.Parent.Parent.Parent.Name ' name of
application
End With
End Sub

Note that the parent of the application object is the application object.

I'm not familiar with the .Net stuff. I've been told it's okay to let
VB/VBA
set everything to nothing internally when a procedure ends.

Most times, that's true for VBA. However, there are some circumstances
where
it is certainly NOT true.....

Actually, I've been told that sometimes it's better to let VBA clean up. I
understand the rationale for cleaning up after yourself, and in many cases I
do so anyway (e.g., with the object variables which relate to automation).

Additionally, since Microsoft is obviously pushing rather hard to get
developers to move from VBA for Office development into using Visual
Studio.Net and Visual Studio Tools for Office (VSTO) instead, the
proverbial
handwriting is on the wall for all to see which direction they would
prefer
us to go.

In my experience, the vast majority of VSTO users are .Net programmers who
are now branching into Office, not Office VBA users who are branching into
..Net. The only reason I'm a VBA programmer is that VBA is what has been
provided as a programming interface for Excel. Before VBA I was an XLM
programmer. If they force VSTO down our throats and remove VBA, then I and
many of my colleagues will finally have to move to VSTO.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Mark Burns" wrote in message
...
Jon,

(replies inline below)

"Jon Peltier" wrote:

Mark -

Far as I know, there's no limit to the levels of "." you can use, as long
as
the OM goes that deep.

Yeah, but what, exactly will you get given, say:
dim o as Excel.Chart

Set o = Activesheet.charts(1)
with o
(if there is no real .name property for what follows, then let's pretend
there is for this example)
?.name ' = the chart object's name
?..name ' = the charts collection's name? or the activesheet's name?
?...name '= the workbook's name? or the application's name?
end with
set o = nothing
...know what I mean?
In other words, will you get the "absolute" object heirarchy tree's
ascendants, or (far less likely, I think) only the object ascendants
directly
in the tree branch for how the variable was declared/instantiated?

I generally use Set Blah = New Object/With Blah, unless it's a small
little
thing like this. The variable Blah gets reused in the loop (adding a set
of
series), then soon goes out of scope when the function or sub ends.

I'm not familiar with the .Net stuff. I've been told it's okay to let
VB/VBA
set everything to nothing internally when a procedure ends.

Most times, that's true for VBA. However, there are some circumstances
where
it is certainly NOT true (relying on VBA to clean up your phantom object
instances from While blocks being just one example of such - if you do not
think this is an issue, try using that approach to automate one office app
from another, and see what happens when you try to shut your app down -
the
"automated" app instance may not close because there are open object
instances hanging around in memory which can hold it open despite calling
oOfficeApp.Quit correctly 20 times).

Additionally, since Microsoft is obviously pushing rather hard to get
developers to move from VBA for Office development into using Visual
Studio.Net and Visual Studio Tools for Office (VSTO) instead, the
proverbial
handwriting is on the wall for all to see which direction they would
prefer
us to go.

(If only because one can infer from their allowing the VBA tool
development
effort pretty much languish while enhancing VSTO - and VS.NET - with each
version of Office, so that where they are and are NOT spending their time
and
$$ becomes pretty obvious. If you want proof of that statement, look no
farther then the list of improvements for VBA developers in Office 2007.
It's
basically a one-item list of improvements: we now get to use the mouse
wheel
in VBA in Office 2007. End of list. Want to compare that with the list of
enhancements for VSTO for Office 2003 to the VSTO for Office 2007 list? <a
long list)

Since .Net is obviously the direction MS would prefer us to go, it would
probably be better to start using coding methodologies which are MORE
compatible with .Net then less. Continuing to use the "Oh, it'll all get
cleaned up when VB closes the sub/function and the variables all fall out
of
scope" mindset is NOT a compatible development pattern for the .Net world.
...unless you want to really cause yourself tons of unpredictable and
erratic-seeming problems with file handles, recordsets, connections,
locks,
etc.,. because you're still letting object variables bound to application,
database, or even some UI resources (like pallettes and cursors) just
"fall
out of scope"...where they continue to sit around in memory holding on to
their precious, limited-commodity resources like record locks and database
connection licenses, until the .NET Garbage collector "gets around to"
cleaning up and finalizing those obejcts at some random interval
"later"...

Look, I'm really not trying to preach here or tell you what to do. I'm
just
trying to point out the pitfalls which lie ahead on that road, and offer
an
easier path forward to .net - one that isn't very incompatible with what
we
do now. It just requires a little more thought and care with our object
instances and a few simple rules:

1) *ALWAYS* clean up your object variables when you are done playing with
them.
(This is the same rule your parents and teachers taught you in/before
kindergarten: "Put away your Toys when you are done with them!")
Ex: *always* Put a Set oMyObjectVariable = Nothing
*Before* End/Exit Sub or End/Exit Function (just make sure you .Close any
recordsets or similar items before setting them to nothing too)

2) if you are defining your own classes, in addition to any
Class_Initialize
and Class_Terminate code you may have, Add a Public DISPOSE() method.
In your DISPOSE method, add code to clean up any object variables your
class
may use during its lifespan. (You may also want to add a private
Class-level
bIsDisposed boolean variable that gets set True during DISPOSE() calls.
Then
you can add some simple logic like:

If Not bIsDisposed then
<do whatever it is you do here
else
Err.Raise 5, "MyClass.WhateverPropertyOrMethodNameHere()", _
"Illegal procedure Call - This object instance has been DISPOSEd
already."
endif

around the properties and methods you use (at least those which reference
any objects needing cleanup when Disposing of the class instance).

Then, using your classes, becomes simply:
<sub/function whatever
Set o = MyClassNameHere
<do stuff with with o
o.Dispose()
set o = nothing
<end/exit sub or end/exit function

Do this, and your code will move MUCH MORE SMOOTHLY over to the .net world
if and when necessary.

That's all I'm trying to say (that, and by way of thanks I'm trying to
share
what I've learned the hard way a long time ago as you did with me here
yesterday and today).

Jon,

Yeah, after writing all that I went back to play with it for myself, and
found out the ..'d syntax version didn't fly.
So...
<in my best Gilda Radner as Emily Litella voice
"Oh! a typo. Nevermind."
</in my best Gilda Radner as Emily Litella voice

Actually, I've been told that sometimes it's better to let VBA clean up. I
understand the rationale for cleaning up after yourself, and in many cases I
do so anyway (e.g., with the object variables which relate to automation).

?? who ever said that?

In my experience, the vast majority of VSTO users are .Net programmers who
are now branching into Office, not Office VBA users who are branching into
..Net. The only reason I'm a VBA programmer is that VBA is what has been
provided as a programming interface for Excel. Before VBA I was an XLM
programmer. If they force VSTO down our throats and remove VBA, then I and
many of my colleagues will finally have to move to VSTO.

I don't dispute any of that one bit.
....thanks for the help, Jon.

Mark -

Actually, I've been told that sometimes it's better to let VBA clean up.
I
understand the rationale for cleaning up after yourself, and in many
cases I
do so anyway (e.g., with the object variables which relate to
automation).

?? who ever said that?

Matt Curland (Advanced Visual Basic 6, P110):

"[DAO provides] another example of poor teardown code. DAO has Close methods
that must be called in the correct order, and the objects must be released
in the correct order as well (Recordset before Database, for example). This
single poor object model behavior has led to the misconception that VB leaks
memory unless you explicitly set all the local variables to nothing at the
end of a function. This is a completely false notion in a well-designed
object model. VB can clear the variables faster at the End Sub line than you
can from code, and it checks the variables even if you explicitly release
your references. Any effort you make is duplicated."


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

(comments inline below)

"Jon Peltier" wrote:

Mark -

Actually, I've been told that sometimes it's better to let VBA clean up.
I
understand the rationale for cleaning up after yourself, and in many
cases I
do so anyway (e.g., with the object variables which relate to
automation).

?? who ever said that?

Matt Curland (Advanced Visual Basic 6, P110):

Ah, OK, but he was talking about full VB6. VBA(6.x) is not quite the same
things as the full VB6 compiler. _Some_ things were removed/changed to make
it vbA.
I do not know if any of the changes made from VB6-VBA6 apply to what Matt
is speaking about here, or not - probably not.

"[DAO provides] another example of poor teardown code. DAO has Close methods
that must be called in the correct order, and the objects must be released
in the correct order as well (Recordset before Database, for example). This
single poor object model behavior has led to the misconception that VB leaks
memory unless you explicitly set all the local variables to nothing at the
end of a function. This is a completely false notion in a well-designed
object model. VB can clear the variables faster at the End Sub line than you
can from code, and it checks the variables even if you explicitly release
your references. Any effort you make is duplicated."

....and yet there are STILL times when this does not work, and COM objects
can be left sitting orphaned in memory because their reference counts are not
0 when the variables go out of scope. For example, picture a custom typed
collection class where the collection items hold a .Parent pointer to the
collection object they are part of. This is the classic example used to
describe circular references which will hold the COM obejcts in memory and
never fire off their _Terminate events. If the collection class instance is
just allowed to drop out of scope while it still contains items which hold
pointers back to it, it never really dies until you reboot the machine. So,
unless a proper tear-down sequence is followed (and VB6/VBA CAN NOT do that
for you) VB6/VBA CAN and WILL "leak memory" that way (by orphaning COM class
instances holding "circular references" in memory). Note that the COM
references don't have to be "circular" for this to apply - they just have to
be long-lasting, as in any case when a reference is passed to any other
object which is not also shut down/closed when the "originating" class
instance variable goes out of scope at the end of a given sub or function
(picture the case of a Public declared variant variable in a code module
which is later set to a COM object reference - the variable, surviving the
end of a function which declares and instantiates the object, keeps the
object instance alive in memory past the End Function which gave it birth).

My point here is that even if you DO wind up wasting some few CPU cycles
with seeming unnecessary "myClass.Dispose : Set myClass = Nothing" lines of
code, it is STILL a good habit to be in because as a programmer it helps make
you _think_ about what is happening with the classes and objects you are
creating, using, and tearing down when done. This will only help make your
code more robust and less error-prone, especially if your code may someday
have to find a way to live in a .NET-type of environment.

....and I got much of that rationalle from Chris Sells and Paul Vick who were
on the .NET 1.0 architecture team, back when some of us VB.NOTers (see Carl
Peterson's MVP web site or <http://vb.mvps.org/vfred/Trust.asp for more)
were arguing with MS-folk that VB.NET was "NOT VB anymore" because of this
whole deterministic finalization thing (among many others) that .NET was
"killing off".

Mark -

I just happened to find that citation in another context, and thought the
timing was interesting.

I think the deal with VBA is that if you are working just in the VBA of the
host application, you don't need to be too retentive about closing your
references. However, if you are automating another application or dealing
with COM, then you have to be more careful.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Mark Burns" wrote in message
...
(comments inline below)

"Jon Peltier" wrote:

Mark -

Actually, I've been told that sometimes it's better to let VBA clean
up.
I
understand the rationale for cleaning up after yourself, and in many
cases I
do so anyway (e.g., with the object variables which relate to
automation).

?? who ever said that?

Matt Curland (Advanced Visual Basic 6, P110):

Ah, OK, but he was talking about full VB6. VBA(6.x) is not quite the same
things as the full VB6 compiler. _Some_ things were removed/changed to
make
it vbA.
I do not know if any of the changes made from VB6-VBA6 apply to what Matt
is speaking about here, or not - probably not.

"[DAO provides] another example of poor teardown code. DAO has Close
methods
that must be called in the correct order, and the objects must be
released
in the correct order as well (Recordset before Database, for example).
This
single poor object model behavior has led to the misconception that VB
leaks
memory unless you explicitly set all the local variables to nothing at
the
end of a function. This is a completely false notion in a well-designed
object model. VB can clear the variables faster at the End Sub line than
you
can from code, and it checks the variables even if you explicitly release
your references. Any effort you make is duplicated."

...and yet there are STILL times when this does not work, and COM objects
can be left sitting orphaned in memory because their reference counts are
not
0 when the variables go out of scope. For example, picture a custom typed
collection class where the collection items hold a .Parent pointer to the
collection object they are part of. This is the classic example used to
describe circular references which will hold the COM obejcts in memory and
never fire off their _Terminate events. If the collection class instance
is
just allowed to drop out of scope while it still contains items which hold
pointers back to it, it never really dies until you reboot the machine.
So,
unless a proper tear-down sequence is followed (and VB6/VBA CAN NOT do
that
for you) VB6/VBA CAN and WILL "leak memory" that way (by orphaning COM
class
instances holding "circular references" in memory). Note that the COM
references don't have to be "circular" for this to apply - they just have
to
be long-lasting, as in any case when a reference is passed to any other
object which is not also shut down/closed when the "originating" class
instance variable goes out of scope at the end of a given sub or function
(picture the case of a Public declared variant variable in a code module
which is later set to a COM object reference - the variable, surviving the
end of a function which declares and instantiates the object, keeps the
object instance alive in memory past the End Function which gave it
birth).

My point here is that even if you DO wind up wasting some few CPU cycles
with seeming unnecessary "myClass.Dispose : Set myClass = Nothing" lines
of
code, it is STILL a good habit to be in because as a programmer it helps
make
you _think_ about what is happening with the classes and objects you are
creating, using, and tearing down when done. This will only help make your
code more robust and less error-prone, especially if your code may someday
have to find a way to live in a .NET-type of environment.

...and I got much of that rationalle from Chris Sells and Paul Vick who
were
on the .NET 1.0 architecture team, back when some of us VB.NOTers (see
Carl
Peterson's MVP web site or <http://vb.mvps.org/vfred/Trust.asp for more)
were arguing with MS-folk that VB.NET was "NOT VB anymore" because of this
whole deterministic finalization thing (among many others) that .NET was
"killing off".

"Jon Peltier" wrote:

Mark -

I just happened to find that citation in another context, and thought the
timing was interesting.

It was indeed.

I think the deal with VBA is that if you are working just in the VBA of the
host application, you don't need to be too retentive about closing your
references. However, if you are automating another application or dealing
with COM, then you have to be more careful.

While that is most likely true in the great majority of cases, My reply
would have to be this: Why would you ever not want to cultivate a more
careful approach to programming anyway?

I think the deal with VBA is that if you are working just in the VBA of
the
host application, you don't need to be too retentive about closing your
references. However, if you are automating another application or dealing
with COM, then you have to be more careful.

While that is most likely true in the great majority of cases, My reply
would have to be this: Why would you ever not want to cultivate a more
careful approach to programming anyway?

In general I do. I avoid relying on defaults. I declare variables in as
tight a scope as possible and always use Option Explicit. Out of habit I
generally do close all my references explicitly. I write code to be as
modular and insular as possible.

While I was pointing out that certain measures are not always strictly
required, it's probably best that I refrain from doing so.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Jon,

I just re-read the last part of our exchange here, and realized that I may
have come off sounded like I was finger-pointing at you, rather than trying
to hold a more generic discussion. So, if you or others took my words that
way, my apologies, as finger-pointing was not my intent.

So, Jon, thanks for your time and an interesting conversation.
....not to mention the terrific help you gave me.

"Jon Peltier" wrote:

I think the deal with VBA is that if you are working just in the VBA of
the
host application, you don't need to be too retentive about closing your
references. However, if you are automating another application or dealing
with COM, then you have to be more careful.

While that is most likely true in the great majority of cases, My reply
would have to be this: Why would you ever not want to cultivate a more
careful approach to programming anyway?

In general I do. I avoid relying on defaults. I declare variables in as
tight a scope as possible and always use Option Explicit. Out of habit I
generally do close all my references explicitly. I write code to be as
modular and insular as possible.

While I was pointing out that certain measures are not always strictly
required, it's probably best that I refrain from doing so.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______