You should have noticed the ByVal keyword in the parameter list of the MouseDown() event procedure shown earlier in the chapter. The ByVal keyword tells VBA to make a copy of the value stored in the accompanying variable. Thus, any manipulation of the copied value within the procedure does not affect the original variable.
The alternative to passing a variable by value is to pass a variable to another procedure by reference; the ByRef keyword is used to do so. When you pass by reference you are essentially passing the original variable to the procedure. Any manipulation of the variable in the new procedure is permanent, so the variable does not retain its original value when program execution proceeds back to the calling procedure. This is true even if you use a new variable name in the procedure that accepts the variable passed by reference. Passing by reference is the default behavior, so you can omit the ByRef keyword if you wish.
The following short program will make the behavior of ByVal and ByRef clear. I suggest inserting a new module into a project, adding the code below, and running the program from the procedure Main().
Private Sub Main()
Dim numl As Integer Dim num2 As Integer numl = 10 num2 = 15
Call PassByRef(numl) Call PassByVal(num2) MsgBox (numl & " " & num2) End Sub
Private Sub PassByRef(ByRef num3 As Integer)
num3 = 20 End Sub
Private Sub PassByVal(ByVal num2 As Integer)
num2 = 20 End Sub
Figure 3.6 shows the message box output by this program.
Message Box output from sub procedure
First two integer variables are declared and initialized to the values 10 and 15. The first variable, numl, is passed by reference to the procedure PassByRef() in a variable called num3. The value 20 is assigned to the num3 variable inside the PassByRef() procedure. Next the variable num2 is passed by value to the PassByVal() procedure, where it is copied to another variable called num2. The num2 variable in the PassByVal() procedure is then assigned the value 20. The program ends with the output of the original num1 and num2 variables in a message box.
Now ask yourself: "What values output in the message box?" The answer is 20 for the num1 variable, and 15 for the num2 variable. The variable num1 holds the value 20 at the end of the Main() procedure because it was changed in the PassByRef() procedure. Even though a different variable name was used in the PassByRef() procedure, the num3 variable still refers to the same memory location holding the value assigned to the num1 variable. Essentially, we have one variable with two names, each with its own scope. The num2 variable retains its value of 15 at the end of Main() procedure because it had been passed by value to the Pass-ByVal() procedure. Passing by value makes a copy of the variable's value to a new variable, even if the variable in the accepting procedure (PassByVal) has the same name. In this case, there are two variables with the same name.
You pass a variable by reference to a procedure in order to change the value of the original variable; or when the variable is needed in the procedure, but its value does not have to be changed. If the variable needs to be altered for another purpose but must retain its original value; then pass the variable by value using the ByVal keyword.
Was this article helpful?