Passing Variables in VBA Using ByRef and ByVal

In many cases it is inefficient to run one long sub procedure in VBA. Rather, it is easier to break them up into smaller processes. I’m going to show an example of how to do this using a simple square root function, and having a variable passed from another sub procedure.

My initial subprocedure will just assign a test value, use the square root function to determine the square root, and then return a message box to summarize the results.

————————————————————————————

Sub test()

Dim testvalue as Integer, root as Integer

testvalue = 25

root = sqrt(testvalue)

Msgbox “the square root of ” & testvalue & ” is ” & root

End sub

————————————————————————————

The root variable comes from the sqrt function which is below:

————————————————————————————

Function sqrt(cl as Integer) as Integer

sqrt = cl ^ 0.5

End Function

————————————————————————————

The sqrt function takes the cl to the power of 1/2; its square root.  The sqrt function requires a variable for it to work, which is indicated by the parentheses, indicating that it requires the cl variable to be an integer. That is the key requirement, that the variable passed to the sqrt function is the same data type.

The sub procedure at the top assigns 25 to the testvalue variable.  The next line calls the sqrt function and uses the testvalue variable as the argument for the function. Effectively it is going to calculate the square root of 25. This result will be assigned to the root variable. The last line before the end of the sub procedure simply pops up a message box to say what the square root of the testvalue is.

If I run the first sub procedure this is what the message box results in:

As you see the square root of 25 is indeed 5, so the calculation worked correctly.

However, let’s pretend my function has an additional line where it sets the cl variable to 9:

————————————————————————————

Function sqrt(cl as Integer) as Integer

sqrt = cl ^ 0.5

cl = 9

End Function

————————————————————————————

 Now when I run the sub procedure the message box I get is this:

Obviously this is not correct. And you’ll notice that now my variable in my sub procedure has been changed to 9. The reason for this is because the variable has been passed to the function by Reference, or ByRef. As a result, when the sub procedure sends the testvalue variable to the sqrt function, the testvalue variable effectively assumes the cl variable, and when the cl variable is changed to 9, when the variable is now sent back to the sub procedure it is with the value of 9. Sending a variable by reference allows for the variable to be changed by the function. 

The other way to pass a variable is to pass it by Value, or ByVal. Becuase ByRef is the default in VBA, it does  not have to be stated. But to pass it ByVal that needs to be specified. Below is the adjusted function now taking only the value rather than the reference:

————————————————————————————

Function sqrt(ByVal cl as Integer) as Integer

sqrt = cl ^ 0.5

cl = 9

End Function

 ————————————————————————————

 Now when I run the sub procedure I get the following message box:

The calculation reverts back to the correct message I had initially before I added that line of code to change the cl variable. But even with the change in code, by setting the variable to ByVal, it ensures my original variable cannot be changed in the sqrt function.