Page 116 - Excel Progamming Weekend Crash Course

h540629 ch07.qxd 9/2/03 9:33 AM Page 91

Session 7 — Procedures and Modules 91

Sub MySub(s As String)
s = “changed”
End Sub

Dim str As String
str = “original
MySub str

After this code executes, the string str contains “changed.”
You can also pass an argument by value using the ByVal keyword in the procedure
definition:
Sub MySub(ByVal s As String)

When a procedure is called, ByVal arguments are passed by making a copy of the
argument variable and then passing that copy to the procedure. Code in the procedure has
access to the argument value, but it does not have access to the original variable and there-
fore cannot modify it. This is shown here:

Sub MySub(ByVal s As String)
s = “changed”
End Sub

Dim str As String
str = “original
MySub str

After this code executes, the variable str still contains “original.”
VBA’s default is to pass variable arguments by reference because it is more efficient than
passing by value. The ability to change the variable inside the procedure is a side effect of
the way reference arguments work and should not regularly be employed as a programming
technique. When a procedure needs to return data to the calling program, it is better to use
a function procedure (covered later in this session). Modifying argument values within the
procedure should be used only when really necessary, and then with great care.

Calling Procedures
You can call, or execute, a procedure from Excel’s Macros dialog box (Alt+F8), but more
often you will call procedures from code. In most cases, each Excel program you write will
have one starting procedure that is the entry point to the program. From there, execution
passes to the program’s other procedures by means of procedure calls.
There are two equivalent forms for calling a procedure. The first uses the Call keyword:

Call procname(arglist)
If the procedure takes no arguments, omit the parentheses:

Call procname