deleting all objects in a collection

[venkman]

I'm trying to write a subroutine to delete all objects in a collection and then end the macro. I thought the following would work, but it's not:

Sub mydelete(col As Collection)
Dim myobj As Object
For Each myobj In col
Set myobj = Nothing
Next
Set col = Nothing
End
End Sub

'with a test sub being:

Sub tester()
Dim x As MyClass
Set x = New MyClass
Dim mycol As New Collection
mycol.Add x
mydelete mycol
End Sub

'myclass has these method declarations:

Private Sub Class_Initialize()
MsgBox "Constructor called"
End Sub

Private Sub Class_Terminate()
MsgBox "Destructor called"
End Sub


Any suggestions? Is this possible?
-Venkman

 

[rmikesmith]

Venkman,

Try this modified version of mydelete:

Sub mydelete(col As Collection)
   Dim i as integer
   For i = 1 to col.Count
     col.Remove i
   Next i
   Set col = Nothing
   End
End Sub

HTH
Mike

 

[venkman]

Mike,

Thanks for the response, but that didn't work.
The problem is that in that the original subroutine (tester) still has a reference to the object. For instance adding the following line to "tester" will make the destructor get called:

set x = nothing

The collection must also be deleted since it contains a reference to the object also(I think using the remove method has the same effect as setting the collection to nothing).

A possibly outdated Java book I read recently said that Java's garbage collector could not be counted on to call class destructors. The book said not to do in Java what I'm trying to do with vba, which is include close connection/file commands in a class destructor, but to instead call them explicitly when you finish with an object. Is this the same in vba? Or can you force the terminate event somehow? Anybody?

-Venkman

 

[rmikesmith]

Venkman,

I'm not 100% sure I know what you are trying to achieve. Do you want the mydelete procedure to "de-allocate" the variable(s) of Type myClass that are in the collection? I'm not sure this can be done. I tried the following and it didn't work:

Sub mydelete(col As Collection)
   Dim i As Integer
   For i = 1 To col.Count
     Set col.Item(1) = Nothing  'throws error 438
     col.Remove 1
   Next i
   Set col = Nothing
End Sub

Why is this necessary? Can't you set each instance of myClass = Nothing inside tester (after calling mydelete)? Actually, this isn't even necessary here since x is a local variable which is de-allocated when tester finishes (demonstrated by removing the End statement and observing that the destructor is called).

Am I off base?


Regards,
Mike

 

[venkman]

Hi Mike,

I apologize for posting before I fully thought out what I was looking for. What I really really needed was the easiest way to abort a macro and call the terminate method of each object it was using. I was hoping there was some quick easy way to do this by writing some kind of abort sub which did all this. On further consideration, I think probably the best way to do it is just to raise somekind of user-defined error everytime I want to abort, and to put error handling in the top level sub which skips to the end of the sub when an error occurs, thus making all variables go out of scope and be deleted automaticaly.

Sorry, for not thinking this out a little more before posting... I got too lazy.

Thanks,
Venkman

 

[rmikesmith]

Venkman,

No problem. I think your last-posted strategy should work fine.

BTW. If you also want the user to be able stop the procedure gracefully, you can set

Application.EnableCancelKey = xlErrorHandler

inside your procedure. If the user presses Ctrl-Break, Error 18 is raised and your error handler code will run.

Regards,
Mike