Difference between ByRef ByVal in Access Excel VBA

This tutorial explains difference between ByRef ByVal in Access Excel VBA.

You may also want to read:

Difference between Dim and Set

Excel VBA Option Explicit to force declaration of variables

Function, Sub, Method, Property Differences

Difference between ByRef ByVal in Access Excel VBA

ByRef means to pass a variable or Object by Reference (memory address). ByVal means to pass a variable or Object by Value.

When you define a Function with arguments, you can define the variable or Object as ByRef or ByVal. For example

Public Function functionByVal(ByVal x)
    x = x + 1
    functionByVal = x
End Function

or

Public Function functionByRef(ByRef x)
    x = x + 1
    functionByRef = x
End Function

or simply

Public Function functionByDefault(x)
    x = x + 1
    functionByDefault= x
End Function

By default, argument without ByRef or ByRef keyword is default to ByRef.

When we call all these Functions, both Functions have no difference at this point.

Public Sub test()
    y = 10
    MsgBox (functionByVal(10))  'return 11
    MsgBox (functionByRef(10))  'return 11
    MsgBox (y)  'return 10
End Sub

Pass Sub variable to Function argument

What happens if we pass the variable y into Function?

Lets see what happens in default behavior (ByRef)

Public Sub test()
    y = 10
    MsgBox (functionByRef(y))  'return 11
    MsgBox (y)  'return 11
End Sub

The original x in Sub becomes 10, which means passing y to Function affects the original y in Sub.

Lets see what happens in default behavior (ByVal)

Public Sub test2()
    y = 10
    MsgBox (functionByVal(y))  'return 11
    MsgBox (y)  'return 10
End Sub

Unlike ByRef, y is not affected by Function.

Explanation for difference between ByRef ByVal

ByRef is to pass memory address of variable from Calling Procedure to a Called Procedure.

Value of y in our case is stored in memory address, every time we call y, we point to the memory address to get the value.

As the memory address of y is passed to functionByRef() , any modification to the argument in the Function is made directly in the memory address, therefore when y is point to that memory again in test(), y changes.

When y is passed by value (ByVal), the value of y is passed to functionByVal(), not the actual memory address, so changes made to y in Sub or Function do not affect one another.

In summary, if you just want to make use of another Function to return a value, use ByVal in all cases.

Outbound References

http://www.vbdotnetforums.com/vb-net-general-discussion/32352-byref-vs-byval.html

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *