The difference lies in whether you pass a copy of the variable/object, or you pass a link to the object. Sometimes its used to modify the variable or return more information then a simple function will allow, as a function returns just one value.
Imagine the 2 following subs:
- Public Sub DemoByVal(ByVal strInput as string)
-
strInput="OverRide"
-
End Sub
-
Public Sub DemoByRef(ByRef strInput as string)
-
strInput="OverRide"
-
End Sub
Now if we were to call these functions by the following code:
- Public Sub DisplayDemo()
-
Dim strTest as string
-
strTest="Start"
-
-
call DemobyVal(strTest)
-
Debug.print "DemoByVal: " & strTest
-
-
Call DemoByRef(strTest)
-
Debug.print "DemoByRef: " & strTest
-
End Sub
In your immediate pane it would then read:
- DemoByVal: Start
-
DemoByRef: OverRide
The default behavior is ByVal, so you don't actively put the ByRef then it will use ByVal.
In short you can say you should use ByVal (or nothing) when you want VBA to use the variables and do something or return something else, and you should use ByRef when you want VBA to work ON the variables, changing them for example.
Another example could be to pass error codes back and forth. Imagine a function call:
- Public Function Example(ByRef ErrCode as long) as String
-
On Error Goto ErrHandler
-
...Some code that does something
-
-
ExitSub:
-
Exit Sub
-
-
ErrHandler:
-
errCode=Err.number 'Or custom error code
-
Resume ExitSub
-
End Function
And you could use it in such a way:
- Dim strTest as string
-
Dim ErrCode as long
-
-
strTest=Example(ErrCode)
-
If ErrCode<>0 then
-
Msgbox "Error: " & errCode" & vbnewline _
-
"Could not generate magic string"
-
GoTo ExitSub
-
End if
In this way if a error occurs in the subprocedure, the calling main procedure becomes aware of it.
14
