Microsoft Office Tutorials and References
In Depth Information
Passing Arguments to Procedures
You can also, of course, pass literals (that is, not variables) to a procedure. For example:
Sub Main()
Call ProcessFile(“budget.xlsx”)
End Sub
You can pass an argument to a procedure in two ways:
h By reference: Passing an argument by reference simply passes the memory address of
the variable. Changes to the argument within the procedure are made to the original
variable. This is the default method of passing an argument.
h By value: Passing an argument by value passes a copy of the original variable. Consequently,
changes to the argument within the procedure are not reflected in the original variable.
The following example demonstrates this concept. The argument for the Process procedure is
passed by reference (the default method). After the Main procedure assigns a value of 10 to
MyValue , it calls the Process procedure and passes MyValue as the argument. The Process
procedure multiplies the value of its argument (named YourValue ) by 10. When Process ends
and program control passes back to Main , the MsgBox function displays MyValue: 100 .
Sub Main()
Dim MyValue As Integer
MyValue = 10
Call Process(MyValue)
MsgBox MyValue
End Sub
Sub Process(YourValue)
YourValue = YourValue * 10
End Sub
If you don’t want the called procedure to modify any variables passed as arguments, you can
modify the called procedure’s argument list so that arguments are passed to it by value rather than by
reference. To do so, precede the argument with the ByVal keyword. This technique causes the
called routine to work with a copy of the passed variable’s data — not the data itself. In the
following procedure, for example, the changes made to YourValue in the Process procedure do not
affect the MyValue variable in Main . As a result, the MsgBox function displays 10 and not 100 .
Sub Process(ByVal YourValue)
YourValue = YourValue * 10
End Sub
In most cases, you’ll be content to use the default reference method of passing arguments.
However, if your procedure needs to use data passed to it in an argument — and you must keep
the original data intact — you’ll want to pass the data by value.
Search JabSto ::

Custom Search