Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Castelar
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unload objects and instances (VBA)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Unload objects and instances (VBA)

    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.
    <code>
    Sub Example
    Dim doc as Document
    On Error goto CleanupAndExit
    set doc = ActiveDocument
    <font color=448800>' Now do things with doc </font color=448800>
    CleanupAndExit:
    set doc = Nothing
    End Sub
    </code>

    StuartR

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unload objects and instances (VBA)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    New Lounger
    Join Date
    Feb 2004
    Location
    Castelar
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unload objects and instances (VBA)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unload objects and instances (VBA)

    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:
    <code>
    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
    </code>
    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unload objects and instances (VBA)

    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.

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unload objects and instances (VBA)

    Several techniques that I use ease the pain in this regard.
    <UL><LI>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.
    <LI>Any ErrorHandler contains a command setting Abort to True and a GoTo ExitHandler command.
    <LI>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.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •