|
|
Menu
Content
Resources
To Do
Toolbox
|
←Older revision | Newer revision→ ByVal vs ByRef [edit] OverviewWhen 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. [edit] Example of DifferenceThis can be seen a bit more clearly through an example: CODE Sub TestByVal() Dim intVal1 As Integer, intVal2 As Integer intVal1 = 1 intVal2 = 1 Call CalcVals(intVal1, intVal2) Debug.Print intVal1 Debug.Print intVal2 End Sub Sub CalcVals(ByVal Val1, ByRef Val2) Val1 = Val1 + 1 Val2 = Val2 + 1 End Sub By running the above test, we get the following result in the immediate window CODE 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. [edit] Tips & TricksIt 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... CODE Option Compare Database Option Explicit Public Enum eListSQLs elsqlsVendorAccounts = 0 elsqlsCustomerCodes = 1 End Enum Public Function GetListSQL(lListID As eListSQLs) Dim Ret As String 'our return variable Select Case lListID Case elsqlsVendorAccounts: pfGetVendorAccounts Ret Case elsqlsCustomerCodes: pfGetCustomerCodes Ret End Select GetListSQL = Ret End Function Private Function pfGetVendorAccounts(ByRef Ret As String) Ret = "SELECT fldAccountNum, fldDesc FROM tblVendors ORDER BY fldAccountNum" End Function Private Function pfGetCustomerCodes(ByRef Ret As String) Ret = "SELECT fldCode FROM tblCustomers ORDER BY fldCode" End Function
|
ByVal vs ByRef
 |
| Disclaimers |