CODEWITHSUNDEEP
vbaexcelscopeinstance
geofurb
geofurb

Reputation: 41

VBA preserve instances after macro completes (Excel)

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:

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.)

Upvotes: 2

Views: 16352

Answers (3)

lazov
lazov

Reputation: 11

why not trying to use array? You dim it out of your macros and it will keep the data until you clear it with another macro or shoot the workbook off.

Upvotes: 0

Jook
Jook

Reputation: 4682

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

Upvotes: 2

Imre Greilich
Imre Greilich

Reputation: 3402

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.

Upvotes: 1

Related Questions