Unload objects and instances (VBA)

Topic

New Reply

I looked up this topic in the forum, but didn’t find anything. Maybe someone could help me on that.

Anytime I program on VBA, I repeatedly debug my code to find errors and omissions. When I find my error, I stop the macro. By doing so, objects (like Excel spreadsheets, other Databases, instances, etc) are kept in memory and running. Therefore, if I stop a macro in the middle of a debugging process many times, I arrive to a point in which the program cannot continue creating these objects or instances anymore. So, I should unload all these from memory.

Is there any internal VBA program to do so? Or should I locate all them through the Program Manager (as I do now)? How could I write something like:

Set ‘all instances opened’ = Nothing and close all objects.

Well, that’s all. I have the same question with varibales. Imagine I had a very long string matrix which I would like to clean in order to free up memory, is it enough to set vMatrix = “” ? To be clear, what I would like to do is simply kill the variable. I mean, up to a point I would like to get rid of it.

Thank you very much in advance.

Federico.

Reply | Quote

Replies

The usual way to recover space used by an object in Visual Basic is to set the value of the object to be Nothing. Here is some example code.

Sub Example
Dim doc as Document
On Error goto CleanupAndExit
set doc = ActiveDocument
' Now do things with doc
CleanupAndExit:
set doc = Nothing
End Sub


StuartR

Reply | Quote

Yes. But I normally stop the macro before it has opportunity to set the instance to nothing (jump to the error handler). Let me broaden the question: is there any way to list all the instances and objects created?

Thank you again.

Federico.

Reply | Quote

The problems you are experiencing are a good reason why you shouldn’t simply end program execution, but allow VBA to clean up through an error handler and exit section. Here is a variation on Stuart’s example:

Sub Example()
Dim doc As Document
On Error GoTo ErrHandler
Set doc = Documents.Open("MyDocument.doc")
' Now do things with doc
...

ExitHandler:
' Clean up
On Error Resume Next
doc.Close SaveChanges:=False
Set doc = Nothing
Exit Sub

ErrHandler:
' Inform user
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

If an error occurs, the code jumps to ErrHandler, displays the error message and then jumps to ExitHandler.
If no error occurs, the code continues to the ExitHandler section.
So the ExitHandler section will always be executed, whether an error occurs or not, unless you end code execution prematurely. So you shouldn’t end it prematurely but let it finish.

Reply | Quote

And by the way, I don’t think there is an easy way to list all the objects created by your code. It’s your responsibility as a programmer to keep track of them and to dispose of them when they are no longer needed.

Reply | Quote

Several techniques that I use ease the pain in this regard.

• When building the code any Set Object = command has a corresponding Set Object = Nothing command in the ExitHandler area of the procedure; the latter command written immediately (in time), after the former.
• Any ErrorHandler contains a command setting Abort to True and a GoTo ExitHandler command.
• Every Call command is followed by an If Abort Then GoTo ExitHandler command.
  [/list]With this technique, any code interruption can be followed by placing the cursor in the appropriate ErrorHandler; and restarting the code there (Ctrl + F9), allowing the code to clean up after itself as it closes.

  H.T.H.

Reply | Quote

Theoretically, VBA should do that for you. But there are many situations where it fails to do so, especially when you are using collections of classes which in turn have instances of other classes.
Also, if you’re doing automation things (like running Access from Excel), the application in question may be left behind.

A good way to avoid your problem is by using an error handler in each of your routines which makes sure you jump to the cleanup part of the routine in the end.
Always include a class_Terminate event in your class modules to kill all objects instantiated in that class.

Reply | Quote