Excel 2010 Bible

Chapter 40: Creating Custom Worksheet Functions

829

l (^) Some functions have a fixed number of required arguments (from 1 to 60).
l Some functions have a combination of required and optional arguments.
The following sections present a series of examples that demonstrate how to use arguments
effectively with functions. Coverage of optional arguments is beyond the scope of this book.
On the CD
The examples in this chapter are available on the companion CD-ROM. The file is named VBA
functions.xlsm.

A function with no argument

Functions don’t necessarily use arguments. Excel, for example, has a few built-in worksheet func-

tions that don’t use arguments, such as RAND, TODAY, and NOW.

The following is a simple example of a function that has no arguments. This function returns the

UserName property of the Application object, which is the name that appears in the

Personalize section of the Excel Options dialog box. This function is very simple, but it can be use-

ful because no other way is available to get the user’s name to appear in a worksheet formula.

Function User()

‘ Returns the name of the current user

User = Application.UserName

End Function

When you enter the following formula into a worksheet cell, the cell displays the name of the cur-

rent user:

=User()

Like with Excel’s built-in functions, when you use a function with no arguments, you must include

a set of empty parentheses.

A function with one argument

The function that follows takes a single argument and uses the Excel text-to-speech generator to

“speak” the argument.

Function SayIt(txt)

Application.Speech.Speak (txt)

End Function

Note
To hear the synthesized voice, your system must be set up to play sound. n