Let's talk a bit about
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?
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.
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