Functions

I created a custom worksheet function. When I access this function with the Insert Function dialog, it reads "No help available." How can I get the Insert Function dialog box to display a description of my function?

To add a description for your custom function, activate the workbook that contains the Function procedure. Then choose Tools ^ Macro ^ Macros to display the Macro dialog box. Your function won't be listed, so you must type it into the Macro name box. After typing the function's name, click Options to display the Macro Options dialog box. Enter the descriptive text in the Description box.

Can I also display help for the arguments for my custom function in the Insert Function dialog box?

Unfortunately, no.

My custom worksheet function appears in the User Defined category in the Insert Function dialog box. How can I make my function appear in a different function category?

You need to use VBA to do this. The following instruction assigns the function named MyFunc to Category 1 (Financial):

Application.MacroOptions Macro:="MyFunc", Category:=1 The following table lists the valid function category numbers.

Number

Category

No category (appears only in All) Financial

Number

Category

2

Date & Time

3

Math & Trig

4

Statistical

5

Lookup & Reference

6

Database

7

Text

8

Logical

9

Information

10

Commands (normally hidden)

11

Customizing (normally hidden)

12

Macro Control (normally hidden)

13

DDE/External (normally hidden)

14

User Defined (default)

15

Engineering (valid only if the Analysis ToolPak add-in is installed)

How can I create a new function category?

You can create a new function category by using an XLM macro. However, this method is not reliable and is not recommended.

I have a custom function that will be used in a worksheet formula. If the user enters arguments that are not appropriate, how can I make the function return a true error value (#VALUE!)?

If your function is named MyFunction, you can use the following instruction to return an error value to the cell that contains the function:

MyFunction = CVErr(xlErrValue)

In this example, xlErrValue is a predefined constant. Constants for the other error values are listed in the Help system.

0 0

Post a comment