"joeu2004" wrote in message
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.

"Automation" is where code (in particular object variables) in one app
refers to another application, whether after creating a new instance or
grabbing an existing one. All code in say VB6 doing anything with Excel is
"Automation", and any instance of Excel so referenced is an "automated
instance".

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.

*Only* if the reference is to an automated instance, eg code in Excel1
refers to Excel2, otherwise no problem (good practice is another matter).
Try this -

Private mWs As Worksheet
Sub Test1()
Dim xl As Excel.Application
Dim wb As Workbook
Set xl = New Excel.Application
Set wb = xl.Workbooks.Add
Set mWs = wb.Worksheets(1)
xl.Quit
End Sub
Sub CleanUp()
Set mWs = Nothing
End Sub

Look at Processes in Task Manager (maybe sort "Image Name"), step through
Test1 and see the 2nd Excel instance appear, and remain after doing xl.Quit.
Step through CleanUp and see the 2nd instance removed as mWs is destroyed.
Actually before doing CleanUp stop or break on 'end sub' and look at mWs in
Locals.

Now try this -
Sub Test2()
Dim wb As Workbook
Set wb = Workbooks.Add
Set mWs = wb.Worksheets(1)
wb.Close False
End Sub

See the difference with mWs this time in Locals. mWs retains the now
redundant pointer but that's all, no memory leak. However it's not good
practice, eg "mWs = Not Nothing" would give a misleading result. Of course
in this example no need to explicity release 'wb'.


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

Violent, ?, -:) But sure, I think we said pretty much the same in response
to Garry's quote from the book.

Regards,
Peter T