I recently came across a discussion about ByRef and ByVal, which I found a bit confusing. So I thought I’d summarise my (possibly mis-) understanding here.
ByRef and ByVal are keywords that influence the argument-passing mechanism of a call, which appear before the affected parameter in a procedure header: Function MyFunc (ByVal name As String, …).
If you don’t give either keyword, the default mechanism is ByRef. In effect, the parameter in the called procedure is a synonym for the variable passed as an argument by the calling procedure. Any changes made to the parameter variable in the called procedure are visible to the calling procedure.
The ByVal mechanism is equivalent to the declaration of a separate variable in the called procedure, and assignment of the argument value to that variable. Any changes made to the parameter variable in the called procedure are not visible to the calling procedure.
There’s a page in the VBA Help titled “Passing Arguments Efficiently”. This contains the following example of ByVal passing:
Function Factorial(ByVal MyVar As Integer)
MyVar = MyVar - 1
If MyVar = 0 Then
Factorial = 1
Exit Function
End If
Factorial = Factorial(MyVar) * (MyVar + 1)
End Function
Sub TestFactorial()
Dim var As Variant
var = 5
Debug.Print Factorial(var)
Debug.Print var
End Sub
The point is that the value of var in the calling procedure is unaffected by the decrementing in Factorial: it remains as 5.
However, to me, this seems a quite bizarre way of writing a factorial function! Why would you want to decrement MyVar, rather than just passing (MyVar – 1) to the recursive call? Also, what’s wrong with if-then-else?
Incidentally, ByVal is necessary here because we’re passing a Variant argument, which needs to be copied (assigned) to effect the type-conversion. You get an error without the ByVal (that is, if it’s ByRef).
One possible use for argument-modification is to pass back a status value to the caller:
Sub TestDodgy()
Dim error As Boolean
Dim result As Integer
Debug.Print error
result = Dodgy(42, error)
Debug.Print error
End Sub
Function Dodgy(n As Integer, ByRef err As Boolean) As Integer
If n > 100 Then
Dodgy = n
Else
err = True
End If
End Function
(The explicit ByRef is just for clarity; it’s the default). This prints out False, then True, illustrating that error (in the caller) and err (in the called) are the same run-time variable. I gather that this technique is used in function-call APIs, which I guess is okay in that context.
However, my feeling is that, in application code, we’d be better off with the error variable at the module-level. Better still, a Class module could make the error status visible as a property. This would allow callers in other modules to follow the pattern: Obj.AttemptX, If Obj.SucceededX Then …
Let’s look at the two mechanisms with a simple example:
Function ByvalFunc(ByVal str As String) As Boolean
str = "yyy" 'this change does not propagate
ByvalFunc = True
End Function
Function ByrefFunc(ByRef str As String) As Boolean
str = "zzz" 'this change propagates
ByrefFunc = True
End Function
Function ByrefFunc2(ByRef str As String) As Boolean
Dim str2 As String
str2 = str
str2 = "xxx" 'this change does not propagate
ByrefFunc2 = True
End Function
Sub Test()
Dim res As Boolean
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "foo"
s2 = "bar"
s3 = "baz"
s4 = s2
res = ByvalFunc(s1)
res = ByrefFunc(s2)
res = ByrefFunc2(s3)
Debug.Print s1
Debug.Print s2
Debug.Print s3
Debug.Print s4
End Sub
We get: foo, zzz, baz, bar. So the ByRef string s2 is the one that is modified in the called function. Assignment in either the called procedure (s3) or the caller (s4) breaks the link by giving us a new variable.
In the call to ByrefFunc, the caller can force ByVal passing of s2, by using extra parentheses: res = ByrefFunc((s2)). This is rather dodgy syntactically, since in any other context, superfluous parentheses are ignored.
Now comes a twist. Suppose that we have subs, rather than functions:
Sub ByvalSub(ByVal str As String)
str = "yyy"
End Sub
Sub ByrefSub(ByRef str As String)
str = "zzz"
End Sub
Sub Test()
...
s1 = "foo"
s2 = "bar"
ByvalSub (s1)
ByrefSub (s2)
Debug.Print s1
Debug.Print s2
In neither case does the change in the called sub propagate to the caller. This strikes me as weird. Assuming that you want argument-changeability at all, why would you not want it for subs? Any clarification welcome!
Fortunately, with Objects, things work as you would want. Arguments passed to Property Set or other methods of an object are typically assigned (directly or indirectly) to the private variables of the object. In this respect, they are like str2 in ByRefFunc2, above.
What happens when arguments are objects? Here, there is an extra level of indirection: the value of an object-typed variable is an object-reference, not the object structure itself. So if we pass an object-typed variable ByVal, the called procedure gets a copy of the object-reference; the object does not get copied. This is entirely reasonable, as the object could be large, complex, or a piece of our application. Here’s an example:
Sub TestObj1()
Dim li As List
Set li = New List
li.Add ((42))
TestObj2 li
Debug.Print "TestObj1", li.First
Set li = Nothing
End Sub
Sub TestObj2(ByVal li As List)
li.Add (66)
Debug.Print "TestObj2", li.GetNth(2)
Set li = Nothing
End Sub
This works, printing out 66, then 42. If TestObj2 had a ByRef parameter (explicitly or by default), then the call to li.first in TestObj1 fails with a ‘No Object’ error.
Why might you want to alter an object-typed parameter? Good question… One possibility is Memory Management. If the object (reference) is passed around ByRef, there is effectively a single shared reference. In this case, whichever procedure sets the reference to nothing deletes the object (I guess we’re thinking of something potentially large, like a Recordset). It might not be clear whose responsibility this is. On the other hand, if the object (reference) is passed around ByVal, then each procedure can set its own local reference to nothing, when it’s finished with it, without worrying about the wider usage – as in the example above. Any comments on this would be welcome.
There are some pages in the MSDN library on this: look at and around this one. This is Visual Basic, not VBA, and I noticed that it says: “The default in Visual Basic is to pass arguments by value”. Since the VBA default is ByRef, this looks like a potential gotcha, if you ever tried to migrate code between the two. Curious…
Roy MacLean's VBA Blog 
I think there must be some difference between your VBA and mine Roy, in mine byref arguments are always modified in place. I actually got a little worried and tried running the two subs up there, and sure enough s2 was changed to “zzz”, as I thought it should.
Maybe I’m just over-simplifying the issue, but in general I pass arguments by value without a compelling reason not to. When I pass something by reference to improve performance (as may be the case with strings), I don’t do so with the intent to modify it. I think this comes down to managing the scope of variables – as you point out above we’d be better off declaring one reference at a module level where necessary, than we would implicitly widening the scope of a variable by passing it off to be modified in another procedure.
Thanks, Geoff.
Regarding the Sub/Function difference, I was using Excel 2000, so it’s possible that it’s something that’s changed in more recent versions. I’ll try it in 2007 and report back.
Despite having investigated the Byval/Byref distinction, I can’t say that I find the need to worry about it often. Defaults are defaults for a reason, so I assume that Microsoft think that Byref is the typical case. I’m inclined to omit either keyword – thus getting Byref) – unless this causes a problem.
And I still think that one needs to think hard before modifying function parameters. Incidentally, another case where it is done is with the cancel-event parameters on some application events – e.g. Workbook.BeforeSave
Geoff,
Aha! Before, I was calling the sub like this:
ByrefSub (s2)
Notice anything? It should have been either
ByrefSub s2
or
Call ByrefSub (s2)
The original form is equivalent to
Call ByrefSub ((s2))
with the extra parentheses, which force Byval passing from the caller’s end.
Yeeeurggghhh!!! Definitely one of the dodgiest bits of syntax design I’ve come across. For goodness sake, couldn’t you write
Byrefsub ByVal s2
I wonder how often I’ve put in those parentheses…
Very dodgy indeed. This to me amounts to a back door being left open – if I specify in a procedure that its arguments are to be passed a certain way, I probably have a good reason for it. I can think of at least half a dozen applications I’ve written in the last year where core functionality was created by modifying a variable in place. Like you, I shudder to think of how often I could have sabotaged this by using something as innocuous-seeming as a pair of parentheses in the calling procedure.
Thanks for clearing that up though Roy, it’s definitely better to be aware of a trap like this than not…
Thank you Roy! I had concluded that ByRef passing was disabled in VBA (Excel 2000), despite what the documentation says. Being primarily a Pascal programmer, I’m used to enclosing the parameter list of a called procedure in parentheses, and I thought the preceding CALL was just a relic of BASIC’s past with no effect. Byref passing works for me now just as you described.
I had also tried using a FUNCTION rather than a SUB. Since I was not interested in the function result, I invoked it by itself rather than on the right side of an assignment statement. Byref did not work, but it does work when the function call is on the right side of an assignment statement.
Function Foo(a As Integer) As Integer
a = 15
Foo = 0
End Function
Sub Soo(a As Integer)
a = 15
End Sub
With these calls, x is passed ByVal:
Foo(x)
Soo(x)
With these calls, x is passed ByRef (is changed to 15 in the calling procedure):
Call Foo(x)
Foo x
y = Foo(x)
Call Soo(x)
Soo x
Roy, you lifesaver. I’m a VBA newbie and have been struggling with a function call that attempts to modify strings for 2 days….I’d tried everything. Damn those parentheses. š Thanks
This explained it pretty well I thought
From: http://www.cpearson.com/excel/byrefbyval.aspx
Passing Simple Variables ByRef And ByVal
Passing ByRef or ByVal indicates whether the actual value of an argument is passed to the CalledProcedure by the CallingProcedure, or whether a reference (called a pointer in some other languages) is passed to to the CalledProcedure. If an argument is passed ByRef, the memory address of the argument is passed to the CalledProcedure and any modification to that parameter by the CalledProcedure is made to the value in the CallingProcedure. If an argument is passed ByVal, the actual value, not a reference to the variable, is passed to the CalledProcedure.
A simple example will illustrate this clearly:
Sub CallingProcedure()
Dim A As Long
Dim B As Long
A = 123
B = 456
Debug.Print “BEFORE CALL = A: ” & CStr(A), “B: ” & CStr(B)
CalledProcedure X:=A, Y:=B
Debug.Print “AFTER CALL = A: ” & CStr(A), “B: ” & CStr(B)
End Sub
Sub CalledProcedure(ByRef X As Long, ByVal Y As Long)
X = 321
Y = 654
End Sub
In the CallingProcedure the variables A and B are assigned the values 123 and 456, respectively. These values are confirmed with the first Debug.Print statement. Then, the CalledProcedure is called passing arguments A and B. Within CalledProcedure the parameters X and Y as assigned the values 321 and 654 respectively, and control is returned back to the procedure CallingProcedure. Since the parameter X was declared with ByRef, a reference or pointer to A was passed to CalledProcedure and any modification to the X parameter in CalledProcedure affects the variable A in CallingProcedure. The parameter Y was declared with ByVal, so only the actual value of B was passed to CalledProcedure. Changes made to the parameter Y are not made to the varialbe B. This is illustrated by the second Debug.Print statement. This shows that A as modified by CalledProcedure but that B was not changed.
Very helpful discussion. Thanks all. One more point that may catch out amateurs (like me) trying to pass strings by reference to subs: The ‘…’ in one of the examples represents declarations (without which you’ll get a “byref argument type mismatch”). Here’s some simple code to illustrate that, together with the dodgy syntax design:
Sub ByvalSub(ByVal str As String)
str = “yyy”
End Sub
Sub ByrefSub(ByRef str As String)
str = “zzz”
End Sub
Sub Test()
Dim s1 As String, s2 As String
s1 = “foo”
s2 = “bar”
Debug.Print Chr(10) & “Init values:… … … … “; s1 & “, ” & s2
ByvalSub (s1)
ByrefSub (s2) ‘(this is the one that doesn’t work as you’d expect)
Debug.Print “After “”ByxxSub (sx)””… … “; s1 & “, ” & s2
s1 = “foo”
s2 = “bar”
ByvalSub s1
ByrefSub s2
Debug.Print “After “”ByxxSub sx”” … … “; s1 & “, ” & s2
s1 = “foo”
s2 = “bar”
Call ByvalSub(s1)
Call ByrefSub(s2)
Debug.Print “After “”Call ByxxSub(sx)””… “; s1 & “, ” & s2
s1 = “foo”
s2 = “bar”
Call ByvalSub((s1))
Call ByrefSub((s2)) ‘the extra parentheses force Byval passing
Debug.Print “After “”Call ByxxSub((sx))”” “; s1 & “, ” & s2
End Sub
So as opposed to strolling at a continuous speed for say an hour,
you’d probably modify matters up by alternating short bursts of intense walking for say a minute followed by a recovery period with slower walking soon after every single intense period. Just keep alternating for twenty minutes to begin and add time when you develop into much more fit. It is possible to use interval coaching with all phases of theprocera avh to boost your final results.
Also if you do any kind of exercising it is strongly recommended you drink plenty of water before during and right after to help keep your entire body appropriately hydrated.