Collection Object - ByRef - ByVal

Question

I am using VBA in Access 2013.

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

When RunProc is executed it calls PopulateCollection where the argument passed is an Collection instace named MyCol.

PopulateCollection adds 3 items and then RunProc continues by iterating the Collection.

My question / problem is this:

I want the argument MyCol in RunProc to NOT be populated by PopulateCollection. What is the proper way to accomplish this?

Why does PopulateCollection populate both the argument and parameter?

' --------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

'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?

What do you mean by *"both the argument and the parameter"*? Your question is a bit unclear, can you try to express more clearly what you expect to happen? (Also, if you do not want to `PopulateCollection()` to change your collection, why do you call that function in the first place?) — Tomalak, Feb 17 '14 at 15:27
Your question just makes no sense... if you do not want `myCol` to get populated then **do not pass** it to the function. — , Feb 17 '14 at 15:32
When RunProc is executed and at the end of the procedure it loops MyCol it should return nothing because MyCol(the argument) should not be populated. Only pMyCol should be populated in PopulateCollection not MyCol — Michael, Feb 17 '14 at 15:38
In other words, instantiate a collection then pass it to a procedure. This procedure takes the collection and does something with it. But I do not want the original collection: MyCol to be altered only the passed collection: pMyCol — Michael, Feb 17 '14 at 15:40
But the function is called `PopulateCollection`, you should not be surprised when it does what it says on the box. `pMyCol` and `MyCol` *are the same thing*, they are not separate. The question stands: If you don't want to populate the collection, why do you call the function `PopulateCollection`? — Tomalak, Feb 17 '14 at 15:44
So What is the point of passing the original `MyCol` to the function? Why don't you just convert your function to a `Sub` procedure and create a new collection within its body? Again, what is the relationship between `MyCol` and `pMyCol`? — , Feb 17 '14 at 15:59
Thanks for the help. Here is another way of asking my question: — Michael, Feb 17 '14 at 16:33

score 8 · Accepted Answer · answered Feb 18 '14 at 00:07

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.

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.

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

Collection Object - ByRef - ByVal

1 Answers1

Linked

Related