Go Back  Xtreme Visual Basic Talk > Visual Basic .NET (2002/2003/2005/2008, including Express editions) > .NET Office Automation > Garbage collection when reassigning an object variable

Thread Tools Display Modes
Old 01-23-2009, 09:01 PM
jprg jprg is offline
Join Date: Dec 2008
Posts: 72
Default Garbage collection when reassigning an object variable

If I use a single variable of type Excel.Worksheet to reference Worksheet1, and then change it to Worksheet2, Worksheet3 and so on, is it adequate to simply do "Marshal.FinalReleaseComObject(worksheetVariable)" once at the end? Or would I be left with some references still in memory?
In other words, do I need to do a "Marshal.FinalReleaseComObject" before each re-assignment?

Thank you.
Reply With Quote
Old 01-23-2009, 09:27 PM
Roger_Wgnr's Avatar
Roger_Wgnr Roger_Wgnr is offline
CodeASaurus Hex

Forum Leader
* Expert *
Join Date: Jul 2006
Location: San Antonio TX
Posts: 2,427

You are just reusing the variable object so you only need the one call.
If you did a Marshal.FinalReleaseComObject when you reassigned it the variable would no longer exist.
Marshal.FinalReleaseComObject releases the object for GC so you can't reuse it.
Code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. ~Martin Golding
The user is a peripheral that types when you issue a read request. ~Peter Williams
MSDN Visual Basic .NET General FAQ
Reply With Quote
Old 01-23-2009, 09:56 PM
jo0ls jo0ls is offline
Senior Contributor

Forum Leader
* Expert *
Join Date: Feb 2005
Location: London
Posts: 1,050

I would take the "nuke it from orbit" approach and destroy them all before reassignment.

Make sure you check Mike_R's tips here. Oh and he makes a guest star appearance here too.

Anyway, I think you should probably release them whilst you still have the reference to them. There should be no problem reassigning the variable.
Reply With Quote
Old 01-24-2009, 09:04 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848

As jo0ls said, it is probably technically better to release your worksheet object when you are done with it and still have a reference to it, before moving on to the next object.

That said, as a practical matter, I find this style of coding to be way too cumbersome, and you are likely to forget to do it somewhere anyway at some point -- maybe lots of points!

In my own coding, I do not bother releasing each and every object as I use them and I never get a hang. Within your cleanup/shutdown section, the key is, to call GC.Collect() and then GC.WaitForPendingFinalizers() before releasing the objects to which you do have a reference, and then release your remaining objects via Marshal.FinalReleaseComObject() in order from least important object to the most important (for example, Range objects first, then Worksheets, then Workbooks, and then finally your Application object).

Take a look at the two posts that jo0ls listed, which go into how to do this in detail. I think that the StackOverflow Article on Cleaning up COM Objects is probably the most on point here, but also read the Automating Office Programs with VB.NET article that jo0ls mentioned.

If you follow this approach, your code can be a lot cleaner and you shoudn't have any hangs.
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..