none
excel vba about the class_terminate sub fired condition RRS feed

  • Question

  • The VBA class module has a destructor sub named class_terminate.I declare a class variable ATest in a sub at first. When the sub run over, the class_terminate will be called automatically. like:
    ==================
    sub test1()
    dim ATest as UDT
    ATest = new UDT;
    ---
    ...calculation...
    ---
    end sub

    ==================

    However, when I declare a global class variable ATest in the workbook, which means the declaration out of the sub test1, and take an new instance ATest of the declared class in a module sub,the class_terminate can not be called in testing.
    Even I shut down the excel active book, it still not working.
    =================
    dim ATest as UDT --declare it out of the sub
    sub test1()
    ATest = new UDT;
    ---
    ...calculation...
    ---
    end sub

    ==================

    Any one can tell me about the fired condition of the class_terminate sub?
    Do I need to use the below codes to call the class_terminate?

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Set ATest = Nothing
    End Sub

    • Moved by Steve Fan Tuesday, January 27, 2015 5:52 AM coding related
    Monday, January 26, 2015 7:13 AM

Answers

  • Hello,

    Have you debug the code snippets in the Excel VBA editor? Because as I tested, the code snippet actually didn't compile. The Class_Terminate event will be fired when the class instance is set to Nothing, or goes out of the scope. If you define a global variable of the class module, after you set an instance for it, it's still in the module scope, other functions may call this variable at other times. So only when you call Set ATest=Nothing will the Class_Terminate event be fired. Check this example:


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by workheng Monday, February 2, 2015 7:06 AM
    Thursday, January 29, 2015 5:37 AM
    Moderator

All replies

  • Hi,

    Since this issue is more related to coding, I'd like to move it to the Excel for Developers forum:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Steve Fan
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Tuesday, January 27, 2015 5:51 AM
  • Hello,

    Have you debug the code snippets in the Excel VBA editor? Because as I tested, the code snippet actually didn't compile. The Class_Terminate event will be fired when the class instance is set to Nothing, or goes out of the scope. If you define a global variable of the class module, after you set an instance for it, it's still in the module scope, other functions may call this variable at other times. So only when you call Set ATest=Nothing will the Class_Terminate event be fired. Check this example:


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by workheng Monday, February 2, 2015 7:06 AM
    Thursday, January 29, 2015 5:37 AM
    Moderator

  • hi, thanks a lot. I get this issue done.
    Monday, February 2, 2015 7:06 AM