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).