Using Excel Worksheet Functions

You can add almost all of the Excel worksheet functions to your VBA code. VBA provides a very limited number of built-in functions. By using the various functions available within Excel, you can add functionality that is not available with the existing VBA functions. For example, Excel provides several different financial functions that you can use within your macros.

To place an Excel worksheet function in your VBA subroutine or function, you use the WorksheetFunction property along with the name of the function.

One of the properties available for the Application object, the WorksheetFunction property is part of the Excel Object Model that VBA uses to access features of Excel. The Application object refers to the actual Excel program. The WorksheetFunction object stores all of the Excel Worksheet functions. To access one of the functions in the WorksheetFunction object, you use the

WorksheetFunction property and precede the name of the function with the statement: Application. WorksheetFunction. The function follows with any arguments required by the function enclosed in parentheses. For example, the code Application. WorksheetFunction.Max(Num1, Num2, Num3, Num4), uses the Max Excel worksheet function to compare the values in four different variables to determine which variable contains the largest value. See Chapter 4 for more information on the Excel Object Model.

You cannot call Excel worksheet functions that have equivalent VBA functions. For example, both VBA and Excel have functions called Cos that return a numeric value that represents the cosine of an angle. If you try to use the Excel worksheet function Cos in your VBA procedure you receive an error message stating "Object doesn't support this property or method." This message displays indicating that Excel does not recognize the function call.

USING EXCEL WORKSHEET FUNCTIONS

USING EXCEL WORKSHEET FUNCTIONS

-D Create a new subroutine.

□ Initialize values of variables.

0 Type WSVar = Application.WorksheetFunction replacing WSVar with the name of the variable.

-D Create a new subroutine.

Note: See Chapter 3 for information on creating subroutines.

0 Type Dim WSVar As Datatype, replacing WSVar with a variable to contain results of the function call and Datatype with the data type.

Declare any additional variables for the subroutine.

□ Initialize values of variables.

0 Type WSVar = Application.WorksheetFunction replacing WSVar with the name of the variable.

You can use the built-in Excel functions to add functionality to your Excel macros. VBA provides a limited number of built-in functions for use within the subroutines and functions you create. Because of this, it is not unusual to use the Excel worksheet functions within your VBA procedures. On the other hand, Excel provides an enormous number of functions for doing everything from performing statistical calculations to manipulating text. Even if you are an avid Excel user, you may find the sheer number of functions that Excel provides somewhat intimidating.

The Object Browser lists the functions that are part of the WorksheetFunction object. You can use these Excel worksheet functions within your VBA function or subroutine. You can view this list using WorksheetFunction as the search criteria. See Chapter 4 for more information on the Object Browser.

If you do not know the purpose of a particular Excel function, you can view the Insert Function dialog box in Excel, which displays when you click InsertOFunction. The Insert Function dialog box lists all of the available Excel functions. When you click a function, a short description of the function appears under the function list.

-0 Type .FunctionName(arguments), replacing FunctionName with the Excel function and corresponding arguments within the parentheses.

■ As you type the argument list, Microsoft IntelliType displays a list of required arguments for the function.

_Q Type additional code required to display the results of the Excel function.

m

File Edit

View Insert Format

Tools Data Windol"

Help

Type a question For help

- . S

X

í □

q I m a V # s* I « -

% s

il m®

» II Arial

"II10 H B

/ 0 1 s

A7

-

&

A

B

C

D

E

F

G

H

i

j

K

L

1

145

2

65

3

9G3

4

345.89

5

45

G

f

8

9

10

11

I

HSEfflllJ

m

|x|

12

963

13

14

I-

15

16

17

18

19

20

21

22

23

24

25

26

27

-

M

Y H \sheetl / Sheet2 / sheets

Sheet4 /

ieet3 /

Q Switch to Excel and run the macro.

0 0

Post a comment