Microsoft Office Tutorials and References
In Depth Information
If your formula uses a custom worksheet function and it returns #VALUE! , your function
has an error. The error may be caused by logical errors in your code or by passing
incorrect arguments to the function. See “Debugging Functions,” later in this chapter.
In this section, I present a series of examples that demonstrate how to use arguments effectively
with functions. By the way, this discussion also applies to Sub procedures.
Functions with no argument
Like Sub procedures, Function procedures need not have arguments. Excel, for example, has a
few built-in functions that don’t use arguments, including RAND, TODAY, and NOW. You can
create similar functions.
This section contains examples of functions that don’t use an argument.
A workbook that contains these functions is available on the companion CD-ROM. The
file is named no argument.xlsm .
Here’s a simple example of a function that doesn’t use an argument. The following function
returns the UserName property of the Application object. This name appears in the Excel
Options dialog box (General tab) and is stored in the Windows Registry.
‘ Returns the name of the current user
User = Application.UserName
When you enter the following formula, the cell returns the name of the current user (assuming
that it’s listed properly in the Registry):
When you use a function with no arguments in a worksheet formula, you must include a
set of empty parentheses. This requirement isn’t necessary if you call the function in a
VBA procedure, although including the empty parentheses does make it clear that
you’re calling a function.
To use this function in another procedure, you can assign it to a variable, use it in an expression,
or use it as an argument for another function.