Saving Custom Functions in an Excel Add-in
The only limitation to user-defined functions is that when you enter them directly into a cell (without the use of the Insert Function dialog box), you must preface their function names with their filenames. For example, if you want to type in the custom Commission function that's saved in the Personal Macro Workbook and you enter the following formula:
=Commission(C9,C10)
(assuming that cell C9 contains the total sales and cell C10 contains the number of items sold), Excel returns the #NAME? error value to the cell. If you then edit the function to include the Personal Macro Workbook's filename as follows:
=Personal.xls!Commission(C9,C10)
Excel then calculates the sales commission based on the TotalSales in C9 and the ItemsSold in C10, returning this calculated value to the cell containing this user-defined function.
To be able to omit the filenames from custom functions when you enter them directly into a cell, you need to save the workbook file that contains them as a special add-in file. Then, after saving the workbook with your user-defined functions as an add-in file, you can start entering the functions into any worksheet sans their filename qualifiers by activating the new add-in file in the Add-Ins dialog box (Tools →Add-Ins).
To convert a workbook containing the user-defined functions that you want to be able to enter into worksheets without their filenames, follow these steps:
- Open the workbook in which you've saved your user-defined functions in Excel.
Make sure that each custom function works properly. - Press Alt+F11 or choose Tools → Macro → Visual Basic Editor to open Visual Basic Editor.
- Choose Tools → VBAProject Properties from Visual Basic Editor's menu bar to open the VBAProject - Project Properties dialog box.
- Select the Protection tab and then select the Lock Project for Viewing check box.
Selecting this check box prevents other users from viewing the custom functions so that they can't make any changes to them. - Select the Password text box, enter the password there, and then select the Confirm Password text box and re-enter the password
exactly as you entered it in the text box above. Then click OK.
This password prevents users from removing the view-protection status. - Click the View Microsoft Excel button at the beginning of the Standard toolbar in the Visual Basic Editor window to return to the Excel worksheet.
- Choose File → Properties to open the Properties dialog box.
- Enter a descriptive title for the add-in in the Title text box, enter a description of its contents in the Comments text box, and then click OK.
Before saving the workbook as an add-in, you should add a title and description of the userdefined functions that it contains. (This information then appears in the Add-Ins dialog box whenever you select the add-in file.)
After you click OK, you're ready to save the workbook file as a special add-in file. - Choose File → Save As to open the Save As dialog box.
- From the Save as Type drop-down list, select Microsoft Excel Add-In (*.xla).
This action selects the Add-Ins folder in the Save In drop-down list box showing the names of any add-in files that you've saved there. - Select the File Name drop-down list box and replace the current filename with one of your own (without changing the .xla filename
extension) and then click the Save button.
After saving your workbook as an add-in file, you need to follow the remaining steps to activate it so that you can enter its user-defined functions in any worksheet. - Choose Tools → Add-Ins to open the Add-Ins dialog box.
- Click the Browse button in the Add-Ins dialog box to open the Browse dialog box.
- Select the name of your new add-in file in the Browse list box and then click OK.
The Browse dialog box closes, and you return to the Add-Ins dialog box. - Select the check box in front of the name of the new add-in and then click OK to close the Add- Ins dialog box.
This action activates the add-in so that you can enter its user-defined functions in any worksheet.
After you close the Add-Ins dialog box, you can start entering the custom functions that this add-in file contains directly into the cells of any spreadsheet without having to open the Insert Function dialog box.
