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