ByVal or ByRef Parameters

Procedure arguments, either for a subroutine or a function, are declared either as ByVal or ByRef. The use of ByVal means that the called procedure does not want to change the value of the argument passed to it whereas the use of ByRef means that the called procedure may change the value.
This note looks at how the called procedure and the calling routine are affected by three different kinds of arguments passed ByVal and passed ByRef.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1004%20ByVal%20ByRef.shtml

Tushar Mehta

Posted in Uncategorized

4 thoughts on “ByVal or ByRef Parameters

  1. Very good article. I want to add that if the called procedure wants an integer (or long) parameter ByVal and the calling procedure passes a double, the called procedure converts double into integer/long and rounds without notification! E.g. pass 123.456 to a procedure which expects an integer ByVal, the called procedure will use 124 instead of 123.456!

  2. Probably worth mentioning as well that when passing a value as part of a loop e.g. passing the value of “i” in a loop for i = 1 to 10, then this must be passed ByVal.

  3. I would be inclined to add that it is good coding practice to pass by value wherever possible. That will keep your variable scope smaller and make debugging easier. Variables passed by ref that get messed up need to be traced through all of the procedures that they are passed to making by val a lot easier to trace. Adding to Eric’s comment omitting the byval or byref keyword implies a pass by ref and now you are having to do more tracing to track down an issue with your variables.

    The one exception to this would be if you are passing variables in a loop. Since the variable is being copied there is more overhead to passing by value. Usually not a big issue but if you are passing a string you may want to consider passing by ref as the work to copy a string can be large.

Leave a Reply

Your email address will not be published. Required fields are marked *