More about Using Worksheet Functions

Newcomers to VBA often confuse VBA's built-in functions and Excel's workbook functions. A good rule to remember is that VBA doesn't try to reinvent the wheel. For the most part, VBA doesn't duplicate Excel worksheet functions.

Bottom line? If you need to use a function, first determine whether VBA has something that meets your needs. If not, check out the worksheet functions. If all else fails, you may be able to write a custom function by using VBA.

The WorksheetFunction object contains the worksheet functions available to VBA procedures. To see a list of these functions, you can use the Object Browser as shown in Figure 9-3. Follow these steps to display a complete list of worksheet functions available in VBA:

The Object Browser appears.

2. In the Project/Library drop-down list (the one in the upper-left corner of the Object Browser), select Excel.

3. In the list labeled Classes, select WorksheetFunction.

The Members of list shows all the worksheet functions you can use in your code.

For most worksheet functions that are unavailable as methods of the Application object, you can use an equivalent VBA built-in operator or function. For example, the MOD worksheet function is unavailable in the WorksheetFunction object because VBA has an equivalent, built-in Mod operator. This is by design — a VBA operator works faster than an Excel function in a VBA module.

Figure 9-3:

Use the Object Browser to show the worksheet function available in VBA.

Classes

Members of WorksheetFunction"

© Windows

-

Acos

£

© Workbook

Acosh

r;

® Workbooks

And

S3 Worksheet

li? Application

: Wo rks h eetF u ri ctio n

Asc

® Worksheets

& As in

¿p XIAppiicationinterna

Asinh

XIAppiyNamesOrde

Atan2

XIArabieModes

Atanh

XIArrangeStyie

& AveDev

XIArrowHeadLength

Average

tsf XIArrowHeadStyle

V

^ BahtText

6

CI as s Worksheet Function

Member of Excel

CI as s Worksheet Function

Member of Excel

0 0

Post a comment