Microsoft Office Tutorials and References
In Depth Information
Example 6-5. Passing an Argument by Value
On the other hand, suppose we change the AddOne procedure, replacing the keyword ByRef with
the keyword ByVal :
Sub AddOne(ByVal i As Integer)
i = i + 1
End Sub
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
 
Search JabSto ::




Custom Search