ByRef Versus ByVal Parameters

Parameters come in two flavors:ByRef and ByVal. Many programmers do not have a clear understanding of these concepts, but they are very important and not that difficult to understand.

To explain the difference, consider the two procedures in Example 6-4. ProcedureA simply sets the value of the module-level variable x to 5, displays that value, calls the procedure AddOne with the argument x , and then displays the value of x again.

Example 6-4. Testing the ByVal and ByRef Keywords

Sub ProcedureA()

MsgBox x ' Display x

Call AddOne(x) ' Call AddOne MsgBox x ' Display x again

End Sub

Note the presence of the ByRef keyword in the AddOne procedure declaration. This keyword tells VBA to pass a reference to the variable x to the AddOne procedure. Therefore, the AddOne procedure, in effect, replaces its parameter i by the variable x. As a result, the line:

effectively becomes:

So, after AddOne is called, the variable x has the value 6.

On the other hand, suppose we change the AddOne procedure, replacing the keyword ByRef with the keyword ByVal:

In this case, VBA does not pass a reference to the variable x, but rather it passes its value. Hence, the variable i in AddOne simply takes on the value 5. Adding 1 to that value gives 6. Thus, i equals 6, but the value of the argument x is not affected! Hence, both message boxes will display the value 5 for x.

ByRef and ByVal both have their uses. When we want to change the value of an argument, we must declare the corresponding parameter as ByRef, so that the called procedure has access to the actual argument itself. This is the case in the previous example. Otherwise, the AddOne procedure does absolutely nothing, since the local variable i is incremented, and it is destroyed immediately afterwards, when the procedure ends.

On the other hand, when we pass an argument for informational purposes only, and we do not want the argument to be altered, it should be passed by value, using the ByVal keyword. In this way, the called procedure gets only the value of the argument.

To illustrate further, ProcedureA in Example 6-5 gets the text of the first cell and feeds it to the CountCharacters function. The returned value (the number of characters in the active document) is then displayed in a message box.

Example 6-5. Passing an Argument by Value

Sub ProcedureA()

Dim sText As String sText = ActiveSheet.Cells(1,1).Text MsgBox CountCharacters(sText) End Sub

Function CountCharacters(ByVal sTxt As String)

CountCharacters = Len(sTxt) End Function

Now, CountCharacters does not need to, and indeed should not, change the text. It only counts the number of characters in the text. This is why we pass the argument by value. In this way, the variable sTxt gets the value of the text in sText, that is, it gets a copy of the text.

To appreciate the importance of this, imagine for a moment that CountCharacters is replaced by a procedure that contains hundreds or thousands of lines of code, written by someone else, perhaps not as reliable as we are. Naturally, we do not want this procedure to change our text. Rather than having to check the code for errors, all we need to do is notice that the sTxt parameter is called by value, which tells us that the procedure does not even have access to our text. Instead, it gets only a copy of the text.

There is one downside to passing arguments by value: it can take a lot of memory (and time). For instance, in the previous example, VBA needs to make a copy of the text to pass to the parameter sTxt.

Thus, we can summarize by saying that if we want the procedure to modify an argument, the argument must be passed by reference. If not, the argument should be passed by value unless this will produce an unacceptable decrease in performance or unless we are very sure that it will not get changed by accident.

It is important to note that VBA defaults to ByRef if we do not specify otherwise. This means that the values of arguments are subject to change by the called procedure, unless we explicitly include the keyword ByVal. Caveat scriptor !

0 0

Post a comment