集合对象 - ByRef - ByVal

Question

>我在Access 2013中使用VBA。

在常规模块中，有 2 个程序，RunProc() 和 PopulateCollection()

当执行RunProc时，它会调用PopulateCollection其中传递的参数是名为 MyCol 的集合 instace。

PopulateCollection添加 3 个项目，然后通过迭代集合继续RunProc。

我的问题/问题是这样的：

我希望RunProc中的参数MyCol不要由PopulateCollection填充。实现此目的的正确方法是什么？

为什么PopulateCollection同时填充参数和参数？

' --------Module1------------------
Option Compare Database
Option Explicit
Dim i As Integer
Dim MyCol As VBA.Collection
Sub RunProc()
    Set MyCol = New VBA.Collection
    
    PopulateCollection MyCol
    
    For i = 1 To MyCol.Count
        Debug.Print MyCol.Item(i)
    Next i
End Sub
Function PopulateCollection(ByRef pMyCol As VBA.Collection)
    For i = 1 To 3
       pMyCol.Add "Item" & i
    Next i            
End Function

<小时 />

这是问我问题的另一种方式：

Option Compare Database
Option Explicit
Sub Proc1()
    Dim myInt As Integer
    myInt = 1
    
    Proc2 myInt
    
    Debug.Print myInt
    
    myInt = 1
    
    Proc3 myInt
    
    Debug.Print myInt
        
End Sub
Sub Proc2(ByVal pmyInt)
    pmyInt = pmyInt + 1
    Debug.Print pmyInt
    
End Sub
Sub Proc3(ByRef pmyInt)
    pmyInt = pmyInt + 1
    Debug.Print pmyInt
    
End Sub
'Consider the 3 procedures: Proc1, Proc2, Proc3
'Proc1 calls Proc2 and Proc3
'The only difference between Proc2 and Proc3 is that
'the parameter pmyInt is called differently: ByVal vs ByRef
'Proc2 does not change the argument myInt
'Proc3 does change the argument myInt
'The root of my question is why the same behavior is
'not exhibited with an Object (VBA.Collection)
'Assuming I wanted to not have the original Collection altered
'how would I proceed?

Answer 1

在 VBA 中，对象（如集合）始终通过引用传递。传递对象 ByRef 时，将传递对象的地址，并且 PopulateCollection 可以更改引用。

当您传递它 ByVal 时，将传递引用的副本。引用的副本仍指向原始集合，但如果更改副本，则不会更改 RunProc 中的引用。

Sub RunProc()
    Dim MyCol As Collection
    Dim i As Long
    Set MyCol = New Collection
    PopCollByVal MyCol
    'I changed what pMyCol points to but *after*
    'I populated it when it still pointed to MyCol
    'so this returns 3
    Debug.Print "ByVal: " & MyCol.Count
    PopCollByRef MyCol
    'When I changed the reference pMyCol it changed
    'MyCol so both became a new Collection. This
    'return 0
    Debug.Print "ByRef: " & MyCol.Count
End Sub
Function PopCollByVal(ByVal pMyCol As Collection)
    Dim i As Long
    'The pointer pMyCol is a copy of the reference
    'to MyCol, but that copy still points to MyCol
    For i = 1 To 3
        'I'm changing the object that is pointed to
        'by both MyCol and pMyCol
        pMyCol.Add "Item" & i
    Next i
    'I can change what pMyCol points to, but I've
    'already populated MyCol because that's what
    'pMyCol pointed to when I populated it.
    Set pMyCol = New Collection
End Function
Function PopCollByRef(ByRef pMyCol As Collection)
    Dim i As Long
    'The pointer pMyCol is the actual reference
    'to MyCol
    For i = 1 To 3
        pMyCol.Add "Item" & i
    Next i
    'When I change what pMyCol points to, I also
    'change what MyCol points to because I passed
    'the pointer ByRef. Now MyCol is a new collection
    Set pMyCol = New Collection
End Function