Writing a User-Defined Function

When you record a macro, VBA always puts the code inside a command macro. Unfortunately, there is no way to record a user-defined function; you have to write them out by hand. Fortunately, the process is very similar to creating a command macro from scratch. Here are the general steps to follow to write a user-defined function:

1. Open the module you want to use for the function.
2. Place the insertion point where you want to start the function. (Make sure the insertion point isn't inside an existing macro.)
3. Choose Insert, Procedure to open the Add Procedure dialog box.
4. Use the Name text box to type the function's name. The guidelines you must follow are the same as those for a command macro: The name must be 255 characters or fewer; the first character must be a letter or an underscore (_); and you can't use spaces or periods.
5. In the Type group, click the Function option.
6. Click OK. VBA adds the following code to the module (where ProcedureName is the name you typed in step 3):
   

   Public Function ProcedureName()
   End Function

7. Between the Public Function and End Function lines, type the VBA statements you want to include in the function. Press Enter after each statement to start a new line.
8. Be sure to include a statement that defines the return value. That statement should consist of the function name, followed by an equals sign (=), followed by the return value.

Figure shows an example user-defined function that calculates and returns a result, using a single VBA statement:

Here, Sales and Expenses are the arguments that get passed to the function. The function subtracts the Expenses value from the Sales value, and then divides by Sales to return the gross margin.