Garbage Collection using VBA

**mms** · 04-18-2019, 08:45 AM

Hello,

In some programming languages I use, garbage collection is taken care of automatically by the program,
in others, the programmer is responsible for this.

With Excel VBA, which is the case?

ie if I do the following:
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("Calculator Data Table")
Set ws2 = Sheets("W-Shape MC")
Must I do the following when I am finished with them?
Set ws1 = Nothing
Set ws2 = Nothing

Similarly with:
Dim MyRange
MyRange = "A1:" & Selection.SpecialCells(xlCellTypeLastCell).Address
Is the following required?
Set MyRange = Nothing

**CK76** · 04-18-2019, 08:54 AM

Not required. Most objects are destroyed when it goes out of context (i.e. when the sub finishes execution).

However, there are some external objects that will require such handling (either set to nothing, or explicitly closed etc).

You can read more about in link below.
https://blogs.msdn.microsoft.com/eri...ts-to-nothing/

**BadlySpelledBuoy** · 04-18-2019, 09:11 AM

Variables declared within a sub will be destroyed when that sub ends so there is little point in setting to nothing in most cases.

However, if you're passing variables from one sub to another and have therefore declared them at module level (outside of a sub) or they're being used even further afield so declared as public or even global then they will not clear when a sub finishes but hang around until the workbook/application is closed.

In those cases it's often a good idea to set to nothing to preserve memory for other things. However I don't think this is such an issue with modern computers (not like back when I first started learning programming languages and was limited to about 48k of memory rather than gigabytes of the stuff!

One of the times I always set variables to nothing is when I have Excel talking to Access through VBA as leaving connections open can cause problems.

BSB

**mms** · 04-18-2019, 09:54 AM

OK Thanks.

Garbage Collection using VBA

LinkBack

Thread Tools

Rate This Thread

Display

Garbage Collection using VBA

Re: Garbage Collection using VBA

Re: Garbage Collection using VBA

Re: Garbage Collection using VBA

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] When a collection created bring another column value into the collection

[SOLVED] Issue - Garbage on my screen

Make a collection of a few worksheets and check if another worksheet is in the collection

[SOLVED] Performance of iteration through Words collection degrades as collection gets bigger

How to keep the LineType of One Series Collection While Other Collection Types Change

Garbage collection in VBA

Deleting garbage

Bookmarks

Bookmarks

Posting Permissions