Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Without further ado, I'll show you a simple VBA Function procedure. This function, named USER, does not accept any arguments. When used in a worksheet formula, this function simply displays the user's name in uppercase characters. To create this function, follow these steps:

  1. Start with a new workbook. (This is not really necessary, but keep it simple for right now.)

  2. Press Alt+F11 to activate the VB Editor.

  3. Click your workbook's name in the Project window. If the Project window is not visible, press Ctrl+R to display it.

  4. Choose Insert Module to add a VBA module to the project.

  5. Type the following code in the code window:

Function USER() ' Returns the user's name USER = Application.UserName USER = UCase(USER) End Function

Figure 23-1 shows how the function looks in a code window.

Figure 23-1: A simple VBA function displayed in a code window.

To try out the USER function, activate Excel (press Alt+F11) and enter the following formula into any cell in the workbook:

=USER()

If you entered the VBA code correctly, the Function procedure executes, and your name displays (in uppercase characters) in the cell.

Note 

If your formula returns an error, make sure that the VBA code for the USER function is in a VBA module (and not a module for a Sheet or ThisWorkbook object). Also, make sure that the module is in the project associated with the workbook into which you enter the formula.

When Excel calculates your worksheet, it encounters the USER custom function. Each instruction in the function is evaluated, and the result is returned to your worksheet. You can use this function any number of times in any number of cells.

You'll find that this custom function works just like any other worksheet function. You can insert it into a formula by using the Insert Function dialog box, and it will also appear in the Formula AutoComplete drop-down as you type it in a cell. In the Insert Function dialog box, custom functions appear in the User Defined category. As with any other function, you can use it in a more complex formula. For example, try this.

What Custom Worksheet Functions Can't Do

As you develop custom worksheet functions, you should understand a key point. A Function procedure used in a worksheet formula must be passive: In other words, it can't change things in the worksheet.

You may be tempted to try to write a custom worksheet function that changes the formatting of a cell. For example, you may want to edit the USER function (presented in this section) so that the name displays in a different color. Try as you might, a function such as this is impossible to write-everybody tries this, and no one succeeds. No matter what you do, the function always returns an error because the code attempts to change something on the worksheet. Remember that a function can return only a value. It can't perform actions with objects.

="Hello "&USER()

Or use this formula to display the number of characters in your name:

=LEN(USER())

If you don't like the fact that your name is in uppercase, edit the procedure as follows:

Function USER() ' Returns the user's name USER = Application.UserName End Function

After editing the function, reactivate Excel and press F9 to recalculate. Any cell that uses the USER function displays a different result.

Related

Категории