One if ByRef, and Two if ByVal

Let's talk a bit about ByVal and ByRef.  Arguments in VBA are passed by reference by default.  What does that mean?  And what are the differences between passing by reference and by value?

Helpful background

To understand the difference between the two modes of passing variables, it helps to have a bit of background knowledge about what's actually happening with our data at the lowest level.  When we assign a value to a variable, say x = 5, that value (5) has to be physically stored somewhere.  It's not just floating around in thin air.  The values are stored in binary form in memory.

To simplify things, we'll use an Excel worksheet to represent memory.  Each cell is a single bit (0 or 1).  

Now let's imagine we have the following code:

Dim x As Byte
x = 5

The VBA type of Byte is an 8-bit data type that supports values from 0 to 255.  In bit form, those values are 0000 0000 through 1111 1111.  When we declare x As Byte, VBA sets aside 8 bits in memory to hold the value of x.  Since the actual value is stored in memory, VBA assigns x a reference to the location in memory where the value is stored.  In other words:

• x's reference: C2
• x's value: 5

Passing by reference

When we pass the variable x to a procedure by reference (the default), what we're actually passing is a pointer to the location of x's value in memory (i.e., C2).  Thus, any changes we make to the argument inside the procedure will change the original value.

Passing by value

If instead, we pass the variable x to a procedure by value, there is an extra step that happens under the hood.  The 8 bits located in C2:J2 are copied into a different location in memory, say G5:N5.  We still end up passing a pointer to our value, but in this case the pointer is to G5, where we've stored a copy of our original value.

Since we're passing a copy of the original value, we can make whatever changes we want to the argument in our procedure without affecting the original value of the variable in the calling function.  

Sample Code

Let's see how this works with some sample code.  My comments refer back to the simplistic concept of memory as an Excel spreadsheet that I referenced above.

Sub TestTriple()
    Dim x As Byte   'x points to C2
    x = 5           '0000 0101 stored in C2
    
    TripleByVal x   '0000 0101 copied to G5 and passed to TripleByVal
    Debug.Print x   'no change to C2's value
    
    TripleByRef x   'pointer to C2 passed to TripleByRef
    Debug.Print x   'C2's value gets tripled
    
    Triple x        'pointer to C2 passed to Triple
    Debug.Print x   'C2's value gets tripled a second time
End Sub

Sub TripleByVal(ByVal Foo As Byte)
    'G5: 0000 0101 -> 0000 1111  (5 -> 15)
    Foo = Foo * 3
End Sub

Sub TripleByRef(ByRef Foo As Byte)
    'C2: 0000 0101 -> 0000 1111  (5 -> 15)
    Foo = Foo * 3
End Sub

Sub Triple(Foo As Byte)  'Passed by ref by default
    'C2: 0000 1111 -> 0010 1101  (15 -> 45)
    Foo = Foo * 3
End Sub

Image by LUM3N from Pixabay

UPDATE (4/3/2021): I changed the title from "One if ByVal, and Two if ByRef" to "One if ByRef, and Two if ByVal" to reflect the fact that when passing by reference there is only a single copy of the value stored in memory, while passing by value results in a second copy being created and passed to the called routine.