vba - 集合对象 - ByRef - ByVal

Question

我在 Access 2013 中使用 VBA。

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

当执行RunProc时，它会调用PopulateCollection，其中传递的参数是一个名为 MyCol 的 Collection 实例。

PopulateCollection 添加 3 个项目，然后 RunProc 继续迭代 Collection。

我的问题是这样的:

我希望 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 时，会传递引用的副本。引用的副本仍然指向原始 Collection，但如果更改副本，则不会更改 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