Chapter 43: Creating Custom Worksheet Functions
43
Create an add-in When you create an add-in from a workbook that has function proce-
dures, you don’t need to use the file reference when you use one of the functions in a for-
mula; the add-in must be installed, however.Chapter 48, “Creating Custom Excel Add-Ins,” discusses add-ins.Function procedures don’t appear in the Macro dialog box because you can’t execute a function directly. As a result,
you need to do extra, up-front work to test your functions while you’re developing them. One approach is to set up a
simple Sub procedure that calls the function. If the function is designed to be used in worksheet formulas, you can
enter a simple formula that uses the function to test it while you’re developing the function.
Using Function Procedure Arguments
Keep in mind the following about function procedure arguments:■ Arguments can be variables (including arrays), constants, literals, or expressions.■ (^) Some functions do not have arguments.
■ Some functions have a fixed number of required arguments (from 1 to 60).
■ (^) 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.
The examples in this chapter are available on this book’s website at http://www.wiley.com/go/
excel2019bible. The file is named vba functions.xlsm.
Creating a function with no arguments
Most functions use arguments, but that’s not a requirement. Excel, for example, has a few
built-in worksheet functions 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 simple,
but it can be useful because there’s no built-in function that returns the user’s name:
Function User()
' Returns the name of the current user
User = Application.UserName
End Function