On May 25, 2:24 pm, "Peter T" wrote:
I haven't fully followed this thread so apologies in
advance if I've missed something, however I don't follow
how this example relates to Isabelle's wanting to explicitly
destroy worksheet references.

You may be right. In a last-minute effort to simplify things, I
removed optional code that I believe would have related to Isabelle's
example better.

The VBA help page states: "memory and system resources associated
with the object to which the variables refer are released only after
all of them have been set to Nothing, either explicitly using Set, or
implicitly after the last object variable set to Nothing goes out of
scope."

I believe that says that non-static local object variables are
implicitly set to Nothing when a procedure is exited.

The better demonstration of that fact would be if in testit1st, I set
the global object variable "a" to Nothing explicitly some time after
returning from testit2, and use Task Manager to show that the memory
is freed.

That could only happen if the reference count for the object went to
zero at that point, which could only happen if the testit2 reference
were implicitly decreased when leaving the testit2 scope by implicitly
setting the local object variable "a" to Nothing.

In fact, I had done exactly that in one version of the example. "The
exercise is left to the student".

Not a very good example, I agree. I could clean all this up. But I
think I'm beating a dead horse at this point.

You wrote:
FWIW issues can arise with classes and circular
references (classes refer to each other), then they can
be difficult if not impossible to tear down and release
memory, but that's a different matter.

Yes it is. Nonetheless, I had intended to demonstrate that very
situation with a separate example anyway, primarily for GS's benefit.
I forgot.

You wrote:
if the reference was to a sheet in an automated instance,
failure to release could leave the Excel instance hanging
in memory.

Can you explain that with an example? I do not understand the
terminology "automated instance" and how that can lead to a memory
leak.

One example that I believe would "leave the Excel instance hanging in
memory" is if we Set a global object variable to a Worksheet or Range
object and neglect to Set the global object variable to Nothing.

That was really the point of the last step in my testit1st macro,
which goes beyond the question I had for Isabelle. It is certainly
the programmer's responsibility to know when it is necessary to Set a
global object variable to Nothing. It depends on the program design.

But that is not the kind of memory leak I thought Isabelle is
referring to. Since the VBA help documentation explains that we can
expect that exiting from the scope effectively sets non-static local
object variables to Nothing (i.e. decrease the object reference
count), I thought Isabelle might be referring to a __VBA__ defect, not
a programming error.

In any case, I believe that you and I are in "violent agreement" not
only on when we __must__ use Set=Nothing, but also when it is really
not necessary, notwithstanding some people's idea of "good practice".