Page 185 - Excel Data Analysis

10 537547 Ch09.qxd 3/4/03 12:13 PM Page 171

AUTOMATING ACTIONS WITH MACROS 9

You may have situations in which you want to Example:
pass parameters to subroutines. A parameter is
Sub TotalValues
essentially a variable that receives an
Dim Value1, Value2 As Integer
argument from the statement that you use to
Value1 = 5
call the subroutine. Just like standard
Value2 = 7
variables, you want to specify the data type for
Call AddValues (Value1, Value2)
the subroutine parameters to avoid their being
End Sub
converted to variants.
Sub AddValues (Val1 As Integer, Val2 As Integer)
In the following example, the Call statement Dim Total As Integer
in the TotalValues subroutine calls the Total = Val1 + Val2
AddValues subroutine and passes in the MsgBox (Total)
End Sub
values contained in the Value1 and Value2
variables. A subroutine that has parameters
cannot be called from the Macro dialog box.
Other procedures typically call subroutines
with arguments. Therefore, you would run the
macro TotalValues and it would call the
subroutine AddValues by passing the
required parameter values.

Find_Average

■ The Visual Basic Editor ° Switch to Excel and open ■ The new subroutine
inserts the End Sub the Macro dialog box. appears as a macro along
command when you with the other available
press Enter. Note: See the section "Run a Macro" macros.
to open the Macro dialog box.
‡ Type the macro code.

171