VBA preserve instances after macro completes (Excel)

Question

I have some objects of my own that I instantiate inside ThisWorkbook in Excel. I'm having some trouble and I think the issue is that VBA is killing my instances as soon as the macro ends.

We're using ThisWorkbook:

• If I define my own variable for ThisWorkbook in a macro, that variable's value is back to the initialization value after the macro completes, even if the variable is Dim'd in the class of ThisWorkbook and not in the module. (i.e. 0, Nothing, etc.)

• If I instantiate an object for ThisWorkbook in the Workbook_Open() event, the object is destroyed when the event is done firing. Again, even if the variable is Dim'd in the class of ThisWorkbook and not in the sub for Workbook_Open()

Is there something I can do to make sure these objects survive and these variables retain their values?

In ThisWorkbook:

'Object we want to survive
Dim myInstance As MyObject

Private Sub Open_Workbook()
    Set myInstance = new MyObject ' Object is instantiated
End Sub

' Macro 1
Public Sub MyMacro()
    Set myInstance = new MyObject ' Object is instantiated
End Sub

' Macro 2
Public Sub CheckInstance()
    If Not myInstance is Nothing Then
        MsgBox "I found an instance!"
    End If
End Sub

Neither opening the workbook nor running Macro 1 will cause Macro 2 to find myInstance. In both cases, Macro2 will think that myInstance is Nothing. Macro 2 will ONLY find myInstance if it's called inside Macro 1 or Open_Workbook (before their End Sub). I need a fix for this. (I'm running these macros from separate form buttons, NOT inside some other macro.)

Solution 1

Yes, you need to create variables outside of your macros.

Otherwise they will be always destroyed with the end of the macro.

'will be available as long the file is open
Private lngTest as long

Private Sub Worksheet_Change()
    'will be available as long as routine is running   
    Dim lngTest2 as long
    lngTest = lngTest + 1
    lngTest2 = lngTest2 + 1

    debug.print lngTest
    debug.print lngTest2
End Sub

Because you were talking about Workbook_Open - to save your variables, even through closing and reopening a workbook, you would need another construct. My suggestion would be storing them in Worksheets, but I belive there are other methods too.

Edit:

Tested your posted code with this - works fine, finds instance.

MyObject as a class:

Private lngTest As Long

Public Property Get test() As Long
    test = lngTest
End Property

Public Property Let test(ByVal lngValue As Long)
    lngTest = lngValue
End Property

Solution 2

Create a new module for the project in the VBA editor (Module1), and insert this code:

Dim testVar As Integer

Sub Test()
    testVar = testVar + 1
    MsgBox testVar
End Sub

Then add a row to for example the Worksheet's Activate or the Open event:

Module1.Test

It worked for me, the value was incrementing each time I activated the sheet.