←Older revision | Newer revision→
ByVal vs ByRef
When passing arguements to functions, the beginner or immediate programmers may have noticed the ByVal and ByRef argment types and wondered what they were. In actuality, these tell VBA how to utilize memory pointers for either type of argument, though for purposes here we can keep things in layman terms and say that: ByVal passes the value of the argument itself, and ByRef passes the argument itself.
Example of Difference
This can be seen a bit more clearly through an example:
Dim intVal1 As Integer, intVal2 As Integer
intVal1 = 1
intVal2 = 1
Call CalcVals(intVal1, intVal2)
Sub CalcVals(ByVal Val1, ByRef Val2)
Val1 = Val1 + 1
Val2 = Val2 + 1
By running the above test, we get the following result in the immediate window
intVal1 = 1
intVal2 = 2
intVal1, having been passed ByVal to the CalcVals sub, has no changes made to it outside the CalcVals sub. As it was passed ByVal, only a "copy" of the value is sent to the sub to be processed.
However, we can clearly see that intVal2, having been passed ByRef, is calculated and the effect is seen outside the CalcVals sub. We we pass a value by reference, it is as if we send the actual variable itself into the procedure that has been called, and any changes made to it in that procedure are spit out the other side.
Tips & Tricks
It is wise to be aware of the fact that VBA by default passes it's arguments as ByRef. Occasionally, you might do some work to an object argument that you may not want to be reflected in the calling procedure. It is good practice at that point to call the argument as ByVal to avoid any unintended consequences.
On the other hand, using ByRef to pass arguments can in some cases be a convenient way of getting values without having to use a return value from a function. Consider the following example of a Global SQL List module...
Option Compare Database
Public Enum eListSQLs
elsqlsVendorAccounts = 0
elsqlsCustomerCodes = 1
Public Function GetListSQL(lListID As eListSQLs)
Dim Ret As String 'our return variable
Select Case lListID
Case elsqlsVendorAccounts: pfGetVendorAccounts Ret
Case elsqlsCustomerCodes: pfGetCustomerCodes Ret
GetListSQL = Ret
Private Function pfGetVendorAccounts(ByRef Ret As String)
Ret = "SELECT fldAccountNum, fldDesc FROM tblVendors ORDER BY fldAccountNum"
Private Function pfGetCustomerCodes(ByRef Ret As String)
Ret = "SELECT fldCode FROM tblCustomers ORDER BY fldCode"