How to destroy an object

excel ms-access vb6 vba
Fadi picture Fadi · Feb 10, 2017 · Viewed 12.7k times · Source

It seems that Set Object = Nothing didn't destroy the Fs Object in this code:

Sub Test2()
    Dim Fs As New FileSystemObject
    Set Fs = Nothing
    MsgBox Fs.Drives.Count ' this line works
End Sub

The last line works with no errors!. thats mean Fs Object is still exists, right?.

So how to destroy this Fs Object.

Answer

Mathieu Guindon picture Mathieu Guindon · Feb 10, 2017

Another way to ensure proper destruction of an object, is to yield its object reference to a With block (i.e. don't declare a local variable):

Sub Test()
    With New FileSystemObject
        MsgBox .Drives.Count
    End With
End Sub

The object only ever exists inside the With block, and when execution reaches the End With token, if you try it with a custom class module you'll notice that the the class' Class_Terminate handler runs, effectively confirming the proper destruction of the object.

As for the As New quirk, as was already explained, if you intend to set the object reference to Nothing inside that scope, don't declare it with As New, because VBA will set the object reference to Nothing, but will also happily ("helpfully") create a new instance for you as soon as you re-reference it again, be it only to verify that the object Is Nothing.

Side note, this (annoying) counter-intuitive behavior is specifically what's behind the reasoning for Rubberduck's Object variable is self-assigned code inspection:

RD website's inspection results

(note: if you've got code you want to inspect, know that it runs much faster in the actual VBE than on the website)

(if it wasn't clear already: I'm heavily involved with the Rubberduck project)

Related questions

Get length of array?

I'm trying to get the length of an array, yet I keep getting this error: Object required Am I doing something wrong? Dim columns As Variant columns = Array( _ "A", "ID", _ "D", "Name") Debug.Print columns.Length ' Error: Object required

Read More
Error: "Could Not Find Installable ISAM"

I've written some VBA code in an Excel workbook to retrieve data from an Access database in the same directory on a desktop. It works fine on my machine and several other machines running Windows XP, but when we tested …

Read More
Export tables to an excel spreadsheet in same directory

I have two tables in my access database that I want to be able to export to excel. I can do it by opening the table and then doing File->Export... and then choosing the format and typing in …

Read More