Page 759 - Excel 2013 All-in-One For Dummies

Creating Custom Excel Functions 741 Saving custom functions in add-in files
The only limitation to the user-defined functions that you save as part of
a regular workbook file or the Personal Macro Workbook file 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, 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 file- name as follows
=PERSONAL.XLSB!Commission(C9,C10)
Excel 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 filename from the custom functions that you create when you enter them directly into a cell, you need to save the workbook file that contains them as a special add-in file. (For details on using add-ins in Excel, see Book I, Chapter 2.) Then, after you’ve saved the workbook with your user-defined functions as an add-in file, you can start entering them into any worksheet sans their filename qualifier by activating the add-in in the Add-Ins dialog box. (Press Alt+FTAA and then click the Go command button when Excel Add-Ins is displayed on the Manage drop-down list button.)
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:
1. Unhide the PERSONAL workbook in which you’ve saved your user- defined functions in Excel by clicking the Unhide button on the View tab and then selecting PERSONAL followed by OK.
2. Press Alt+F11 or click the Visual Basic command button on the Developer tab or press Alt+LV.
This action opens the Visual Basic Editor window with the workbook file containing the user-defined functions selected in the Project Explorer window. Now you want to set up protection for this workbook so that no one but you can modify its contents.
Book VIII Chapter 2
VBA Programming