Failure to Clear Control References

  • I am working on a very complicated project using a userform where I set references to several combo box controls. When I close the userform it should clear all of these references. However, for some reason it is not, & so when I change any data in the sheets to which combo boxes have their rowsource property set, it fires the change event for those combo boxes & causes all sorts of errors since the userform is not open. Is there any good way to track my references so that I can try to figure out why the memory is not being released?

  • Re: Failure to Clear Control References


    Maybe you could use the watch window in VBE to watch the userform and combobox, espcially when the userform closes and unloads.


    How are you closing and unloading the userform?

  • Re: Failure to Clear Control References


    Quote from Andy Pope

    Maybe you could use the watch window in VBE to watch the userform and combobox, espcially when the userform closes and unloads.


    How are you closing and unloading the userform?


    Yeah, I have thought of using the watch window, but it would be rather complicated (I am using classes & dynamically allocated arrays so it would be a bit tricky to figure out exactly how to reference what I want to reference in the watch window. & I have quite a few controls that I would have to watch...)


    I am closing the userform with a command button:


    Code
    Private Sub cmdClose_Click()
        
        '   Close the wizard
            Unload Me
    End Sub


    And freeing the memory in the Userform_Terminate event:


    Code
    Private Sub UserForm_Terminate()
    
    
    '   Release memory
        Set kWorkbookData = Nothing
    End Sub


    kWorkbookData is an instance of the complicated class that I use for... well, pretty much everything in this project.


    In C++ I developed a method to save the address of the target memory every time that memory was dynamically allocated or deallocated & then I could compare those two files to see if there was any memory being allocated but not deallocated. I am not really sure if there would be any way to do this with VBA...

  • Re: Failure to Clear Control References


    You can just add a watch to 1 of the combobox that is using the cell that you change and which causes the error.


    Set the context of the watch to be All Procedures in the Userform module.
    The set a breakpoint at the Unload Me line and then F8 to step through the code.


    Maybe you have code that references the userform causing it to be reloaded when you think is unloaded. Stick a debug.print statement in the Initialize and Activate events of the userform.

  • Re: Failure to Clear Control References


    At what level are your Variables being scoped to? Procedure, Module or Project Level? You use plural, but your code only shows 1 being destroyed?


    Quote

    However, for some reason it is not, & so when I change any data in the sheets to which combo boxes have their rowsource property set, it fires the change event for those combo boxes & causes all sorts of errors since the userform is not open

    Don't get that. A variable STILL set to a ComboBox wouldn't fire any events simply by adding/modifying the RowSource it refers to.

  • Re: Failure to Clear Control References


    Well, the "kWorkbookData" object is a project level variable & the variables referencing the combo boxes are members of kWorkbookData, or members of members of kWorkbookData, or... well, you get the picture. So destroying the kWorkbookData object SHOULD start a cascade of Class_Terminate events destroying all of the member objects. Point being there are too many different places that objects are supposed to get destroyed to easily trace what is happening & find out where the reference is not being cleared.


    When I hide or delete cells that are referred to by the RowSource property of a combobox in my code is causes all sorts of weird errors. Sometimes it just causes a "catastrophic error" & crashes Excel, but sometimes it lets me debug & I can trace the problem back to the Change event of the combo box being fired.


    In any case, I think I found (just by luck) where I was missing a "Set _____ = Nothing" & so have solved the problem (hopefully. I am still crossing my fingers). So I suppose you could count this thread as solved?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!