vba 集合对象 - ByRef - ByVal

Collection Object - ByRef - ByVal

提问by Michael

I am using VBA in Access 2013.

我在 Access 2013 中使用 VBA。

In a regular module there are 2 procedures, RunProc()and PopulateCollection()

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

When RunProcis executed it calls PopulateCollectionwhere the argument passed is an Collection instace named MyCol.

当RunProc被执行时，它调用PopulateCollection传递的参数是一个名为 的集合实例MyCol。

PopulateCollectionadds 3 items and then RunProccontinues by iterating the Collection.

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

My question / problem is this:

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

I want the argument MyColin RunProcto NOT be populated by PopulateCollection. What is the proper way to accomplish this?

我希望MyColin 中的参数RunProc不被PopulateCollection. 完成此操作的正确方法是什么？

Why does PopulateCollectionpopulate both the argument and parameter?

为什么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

---

---

Here is another way of asking my question:

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

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

'考虑3个程序：Proc1、Proc2、Proc3

'Proc1 calls Proc2 and Proc3

'Proc1 调用 Proc2 和 Proc3

'The only difference between Proc2 and Proc3 is that 'the parameter pmyInt is called differently: ByVal vs ByRef

'Proc2 和 Proc3 之间的唯一区别是 ' 参数 pmyInt 的调用方式不同：ByVal 与 ByRef

'Proc2 does not change the argument myInt 'Proc3 does change the argument myInt

'Proc2 不改变参数 myInt 'Proc3 改变参数 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 procede?

'我的问题的根源是为什么'没有用对象（VBA.Collection）表现出相同的行为'假设我不想改变原始集合'我将如何进行？

回答by Dick Kusleika

In VBA, objects (such as Collections) are always passed by reference. When you pass an object ByRef, the address of the object is passed and PopulateCollection can change the reference.

在 VBA 中，对象（例如集合）总是通过引用传递。当你传递一个对象 ByRef 时，传递对象的地址并且 PopulateCollection 可以改变引用。

When you pass it ByVal, a copy of the reference is passed. The copy of the reference still points to the original Collection, but if you change the copy, you don't change the reference in RunProc.

当您通过 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