Page 194 - Excel Progamming Weekend Crash Course

k540629 ch13.qxd 9/2/03 9:34 AM Page 169

Session 13 — Programming with Excel’s Built-In Functions 169

VBA Functions or Excel Functions?

There’s a lot of overlap between the functions provided by VBA and those pro-
vided by Excel. When a task could be performed either way, how do you decide
which to use? It is usually fairly easy to decide, based on the nature of your
project.

When the purpose of your code is to create all or part of a worksheet, which
the user will then use, you usually choose Excel functions. Because Excel
functions exist in worksheet cells and are evaluated whenever the worksheet
is recalculated, they can perform their job without the need to run VBA pro-
grams. You’ll also tend toward using Excel functions when the results need to
be displayed in the worksheet.
VBA functions are often more appropriate when your code plays a more active
role in an application, beyond that of simply creating a worksheet with in-
place labels and formulas. If the result of a function will be used internally,
and not displayed, a VBA function is also more appropriate

Another way to display a number in currency format is by applying the
“Currency” format to the cell, as you learn in Session 14. This is one of
Tip many situations where Excel and VBA provide you with more than one way
to obtain a given result.

The WorksheetFunction Object
Excel provides many worksheet functions that do not have VBA parallels. If you want to use
one of these functions in code, you can use the WorksheetFunction object. This object lets
you evaluate an Excel worksheet function in VBA code without having to place the function
in a worksheet cell, which is useful when your program needs to use a function result but
does not need to display it to the user. The syntax is:
WorksheetFunction.FunctionName(arguments)

FunctionName can be any of the Excel worksheet functions. The argument list must be
appropriate as defined for that function. For example, the following code sets the variable x
to the smallest value in the range A1:A10:
x = WorksheetFunction.Min(ActiveSheet.Range(“A1:A10”))

This technique is useful considering Excel provides many functions that are not part of
the VBA language.