Page 206 - Excel Data Analysis

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

EXCEL DATA ANALYSIS

USING AN EXCEL FUNCTION IN A MACRO

ou can add almost all of the Excel worksheet the function with the statement:
functions to your VBA code, but they provide a very Application.WorksheetFunction. The function
Y limited number of built-in functions and may not follows with any arguments required by the function
cover your specific data analysis situation. However, by enclosed in parentheses. For example, the code
using the available Excel functions, you can add Application.WorksheetFunction.Max(Num1,
functionality to your macros that is not available with the Num2, Num3, Num4) uses the Max Excel worksheet
VBA functions. For example, Excel provides several different function to compare the values in four different variables to
financial functions that you can add to a macro to perform determine which variable contains the largest value.
financial analysis.
You cannot call Excel worksheet functions that have
equivalent VBA functions. For example, both VBA and Excel
One of the properties available for the Application
object, the WorksheetFunction property is part of the have functions called Cos that return a numeric value that
Excel Object Model that VBA uses to access features of represents the cosine of an angle. If you try to use the Cos
Excel. The Application object refers to the actual Excel Excel worksheet function in your macro code, you receive
program. The WorksheetFunction object stores all of the an error message stating "Object doesn't support this
Excel Worksheet functions. To access one of the functions in property or method." See Appendix B for a list of available
the WorksheetFunction object, you use the Excel functions and Appendix C for available VBA functions.
WorksheetFunction property and precede the name of

USING AN EXCEL FUNCTION IN A MACRO

⁄ Create a new subroutine ¤ Type Dim WSVar as › Initialize values of the ˇ Type WSVar=Application.
for the macro. DataType, replacing WSVar variables. WorksheetFunction, replacing
with a variable to contain the WSVar with the name of the
Note: See the section "Create a results of the function call variable declared in step 2.
Macro Using the Visual Basic and Datatype with the data
Editor" for more information.
type.
‹ Declare any additional
variables for the subroutine.
192