宏完成后 VBA 保留实例 (Excel)

VBA preserve instances after macro completes (Excel)

提问by geofurb

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

我有一些自己的对象，可以ThisWorkbook在 Excel 中实例化。我遇到了一些麻烦，我认为问题在于宏一结束，VBA 就会终止我的实例。

We're using ThisWorkbook:

我们正在使用ThisWorkbook：

• If I define my own variable for ThisWorkbookin 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 ThisWorkbookand not in the module. (i.e. 0, Nothing, etc.)

• If I instantiate an object for ThisWorkbookin 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 ThisWorkbookand not in the sub for Workbook_Open()

• 如果我ThisWorkbook在宏中定义自己的变量，则该变量的值会在宏完成后恢复为初始化值，即使该变量在 的类中ThisWorkbook而不是在模块中是 Dim的。（即 0、Nothing 等）

• 如果我ThisWorkbook在Workbook_Open()事件中实例化一个对象，则在事件触发时该对象将被销毁。同样，即使变量在 的类中变暗ThisWorkbook而不是在子类中Workbook_Open()

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

我能做些什么来确保这些对象存活下来并且这些变量保留它们的值吗？

In ThisWorkbook:

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

打开工作簿或运行宏 1 都不会导致宏 2 找到 myInstance。在这两种情况下，Macro2 都会认为 myInstance 是 Nothing。如果在宏 1 或 Open_Workbook 中（在它们的 End Sub 之前）调用它，宏 2 只会找到 myInstance。我需要解决这个问题。（我从单独的表单按钮运行这些宏，而不是在其他一些宏中。）

回答by Jook

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.

因为您在谈论Workbook_Open- 为了保存您的变量，即使通过关闭和重新打开工作簿，您也需要另一个构造。我的建议是将它们存储在工作表中，但我相信还有其他方法。

Edit:

编辑：

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

用这个测试了你发布的代码 - 工作正常，找到实例。

MyObject as a class:

MyObject 作为一个类：

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

回答by Imre Greilich

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

在 VBA 编辑器 ( Module1) 中为项目创建一个新模块，并插入以下代码：

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:

然后向例如工作表的 Activate 或 Open 事件添加一行：

Module1.Test

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

它对我有用，每次激活工作表时该值都会增加。

回答by lazov

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.

为什么不尝试使用数组？你把它从你的宏中调暗，它会保留数据，直到你用另一个宏清除它或关闭工作簿。