VBA-安全存储变量参考

Question

我熟悉通过参考将参数传递给程序。或者，ParamArray也使我可以通过参考将0或更多参数传递给程序的灵活性。但是，这种方法使我怀疑是否有一种方法可以保留一个或多个变量超出程序范围的变量。当我看到这样的声明时，我的第一个希望是VBA Array功能：

Array(ParamArray ArgList() As Variant)

所以，我整理了以下测试代码：

Private Sub Test()
    Dim a   As Object
    Dim b() As Variant
    ParamArrayTest a
    Debug.Print TypeName(a) ' Output is 'Dictionary'
    b = Array(a)            ' b should be like ParamArray ArgList()
    Set b(0) = Nothing      ' This should clear a
    Debug.Print TypeName(a) ' Output is still 'Dictionary'
End Sub
Private Sub ParamArrayTest(ParamArray ArgList() As Variant)
    Set ArgList(0) = CreateObject("Scripting.Dictionary")
End Sub

不幸的是，这没有我预期的。尽管参数通过ParamArray传递到Array函数中，但似乎返回的数组是按值而不是参考。

。

进一步的研究使我进入了无证件的VBA VarPtr/StrPtr/ObjPtr功能。我发现了许多与API RtlMoveMemory函数一起使用它们的示例。但是，我读到的所有文章都强烈敦促使用该方法，因为它很容易崩溃。我的一些测试确实确实崩溃了。

我的另一个想法是看看我是否可以直接将一个变量的引用分配给另一个变量：

Private Sub Test()
    Dim a As Object
    Dim b As Variant
    b = ByRef a ' Throws a compiler error
End Sub

可以说，编译器根本不允许这样做。然后，我的问题是，可以安全地存储/保存一个变量参考(最好是在另一个变量中(？

编辑

我认为，如果我阐明自己想构建的东西会更有帮助。

我目前正在创建一个包装类别的过程，该类别将将所有表单/控制事件传递给我的一个模块中的一个过程。它将与具有相同控制结构但连接到不同源表的两种形式一起使用。请记住，该代码不完整，但应该足以说明我要克服的问题。另外，Database是我的VBA项目名称。

代码有四个部分：

1. Form_TEST_FORM-表格模块

   Private Sub Form_Open(Cancel As Integer)
       FormHub.InitForm Me, Cancel
   End Sub
   

2. FormHub-模块

   Public Sub InitForm( _
       ByRef Form As Access.Form, _
       ByRef Cancel As Integer _
   )
       Dim Evt As Database.EventHandler
       Set Evt = New Database.EventHandler
       Evt.InitFormObject Form, Cancel
       FormList.Add Evt, Form.Name
   End Sub
   Private Function FormList() As VBA.Collection
       Static Init As Boolean
       Static Coll As VBA.Collection
       If Not Init Then
           Set Coll = New VBA.Collection
           Init = True
       End If
       Set FormList = Coll
   End Function
   

3. FormControl-类模块

   Public Ptr      As Variant ' Pointer to form control variable
   Public acType   As Access.AcControlType
   

4. EventHandler-类模块

   Private WithEvents Form     As Access.Form
   Private WithEvents SForm    As Access.SubForm
   Private CtrlList            As VBA.Collection
   Private Sub Class_Initialize()
       InitCtrlList
   End Sub
   Public Sub InitFormObject(FormObj As Access.Form, ByRef Cancel As Integer)
       Dim ErrFlag As Boolean
       Dim Ctrl    As Access.Control
       Dim FCtrl   As Database.FormControl
       On Error GoTo Proc_Err
       Set Form = FormObj
       If Form.Controls.Count <> CtrlList.Count Then
           Err.Raise 1, , _
           "Form has incorrect number of controls"
       End If
       ' This is where I want to validate the form controls
       ' and also initialize my event variables.
       For Each Ctrl In Form.Controls
           If Not CtrlExists(FCtrl, Ctrl.Name) Then
               Err.Raise 2, , _
               "Invalid control name"
           ElseIf FCtrl.acType <> Ctrl.ControlType Then
               Err.Raise 3, , _
               "Invalid control type"
           Else
               ' Initialize the correct variable with it's
               ' pointer.  This is the part I haven't been
               ' able to figure out yet.
               Set FCtrl.Ptr = Ctrl
           End If
       Next
   Proc_End:
       On Error Resume Next
       If ErrFlag Then
           ClearEventVariables
       End If
       Set Ctrl = Nothing
       Set FCtrl = Nothing
       Exit Sub
   Proc_Err:
       ErrFlag = True
       Debug.Print "InitFormObject " & _
       "Error " & Err & ": " & Err.Description
       Resume Proc_End
   End Sub
   Private Function CtrlExists( _
       ByRef FCtrl As Database.FormControl, _
       ByRef CtrlName As String _
   ) As Boolean
       On Error Resume Next
       Set FCtrl = CtrlList(CtrlName)
       CtrlExists = Err = 0
   End Function
   Private Sub InitCtrlList()
       Set CtrlList = New VBA.Collection
       CtrlList.Add SetCtrlData(SForm, acSubform), "SForm"
   End Sub
   Private Function SetCtrlData( _
       ByRef Ctrl As Access.Control, _
       ByRef acType As Access.AcControlType _
   ) As Database.FormControl
       Set SetCtrlData = New Database.FormControl
       With SetCtrlData
           ' This assignment is where I need to keep a reference
           ' to the variable in the class.  However, it doesn't
           ' work.
           Set .Ptr = Ctrl
           .acType = acType
       End With
   End Function
   Private Sub ClearEventVariables()
       Dim FormCtrl As Database.FormControl
       Set Form = Nothing
       For Each FormCtrl In CtrlList
           ' Assuming I was able to retain a reference to the
           ' class variable, this would clear it.
           Set FormCtrl.Ptr = Nothing
       Next
   End Sub
   Private Sub Class_Terminate()
       ClearEventVariables
       Set CtrlList = Nothing
   End Sub
   

我在代码示例中仅使用1个控制，以简单起见。但是，这个想法是简化我需要修改多少代码，以便在形式的设计更改时添加/删除控件。或者，如果我必须在项目中添加更多表格。

Answer 1

如果您仅需要在单个模块中引用，请在模块标题中声明为公共。如果要在任何模块中引用，请在常规模块标头中声明为全局。甚至数组，记录集和连接对象也可以以这种方式声明。请注意，如果代码在运行时断开，这些变量将丢失其值。

或查看tempvar对象变量。如果代码断开，他们不会丢失价值。但是只能存储数字或文本值，而不是对象。