Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to destroy an object

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.

like image 299
Fadi Avatar asked Feb 10 '17 16:02

Fadi


1 Answers

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)

like image 160
Mathieu Guindon Avatar answered Oct 18 '22 18:10

Mathieu Guindon