Microsoft Office Tutorials and References
In Depth Information
Figure 24-4: Provide a function description in the Macro Options dialog box.
6. Click OK and then click Cancel.
Specifying a function category
Oddly, Excel does not provide a direct way to assign a custom function to a particular function category. If you
want your custom function to appear in a function category other than User Defined, you need to execute some
VBA code. Assigning a function to a category also causes it to appear in the drop-down controls in the Formu-
las Function Library group.
For example, assume that you created a custom function named REMOVESPACES, and you'd like this function
to appear in the Text category (that is, Category 7) in the Insert Function dialog box. To accomplish this, you
need to execute the following VBA statement:
Application.MacroOptions Macro:=”REMOVESPACES”, Category:=7
One way to execute this statement is to use the Immediate window in the VB Editor. If the Immediate window
is not visible, choose View Immediate Window (or press Ctrl+G). Figure 24-5 shows an example. Just type the
statement and press Enter. Then save the workbook, and the category assignment is also stored in the workbook.
Therefore, this statement needs to be executed only one time. In other words, it is not necessary to assign the
function to a new category every time the workbook is opened.
Alternatively, you can create a Sub procedure and then execute the procedure.
Sub AssignToFunctionCategory()
Application.MacroOptions Macro:=”REMOVESPACES”, Category:=7
End Sub
Search JabSto ::




Custom Search