Using ByVal in VBA

In this article, we’ll explore what ByVal means in VBA, its significance, and how to use it effectively.

What is ByVal in VBA

ByVal is a keyword in VBA used to specify how arguments are passed to procedures and functions. It stands for “by value,” and its primary purpose is to determine whether the original variable’s value is affected when it’s used as an argument in a procedure or function.

ByVal vs. ByRef

In VBA, there are two ways to pass arguments: ByVal and ByRef.

ByVal: When an argument is passed ByVal, a copy of the variable’s value is sent to the procedure or function. Changes made to the argument within the procedure or function do not affect the original variable.

ByRef: When an argument is passed ByRef (which is the default if you don’t specify ByVal or ByRef), the procedure or function receives a reference to the original variable. Any changes made to the argument within the procedure or function directly impact the original variable.

How to Use ByVal

Using ByVal in VBA is straightforward. When declaring a procedure or function, specify ByVal before the argument name:
Sub MyProcedure(ByVal arg As Integer)
' My awesome Code
End Sub

By including ByVal, you ensure that any modifications made to arg within MyProcedure won’t affect the original value of the variable passed as an argument.

To illustrate the concept of ByVal, let’s consider a simple VBA function that squares a number without altering the original value:
Function Square(ByVal num As Double) As Double
Square = num * num
End Function

In this example, the ByVal keyword ensures that the original num variable remains unaffected, allowing you to calculate the square of a number without side effects.