UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> ByVal vs ByRef    
ByVal vs ByRef

Overview

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:

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.

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...

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


The above example of using ByRef to set the value of a variable is often used in API calls as well.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 10,841 times.  This page was last modified 17:00, 11 February 2012 by Jack Leach.   Disclaimers