Developer FAQ - Functions
Note: This document was written for Excel 97 - 2000.
I created a custom worksheet function. When I access this function using the Insert Function dialog, it says Choose the Help button for help on this function and its arguments. How can I get Insert Function dialog box to display a description of my function?
As you discovered the message displayed in the Insert Function dialog box is erroneous and and misleading. To add a description for your custom function, select Tool - Macro - Macros to display the Macro dialog box. Your function won't be listed, so you must type it manually 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 Paste Function dialog box?
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 do this using VBA. The statement below assigns the function named MyFunc to category 1 (Finanacial)
Application.MacroOptions Macro:="MyFunc", Category:=1
The table below lists the valid function category numbers.
- No category (appears only in All)
- Date & Time
- Math & Trig
- Lookup & Reference
- Commands (this category is normally hidden)
- Customizing (this category is normally hidden)
- Macro Control (this category is normally hidden)
- DDE/External (this category is normally hidden)
- User Defined (default)
- Engineering (this category is valid only if the Analysis Toolpak add-in is installed)
How can I create a new function category?
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 statement 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 online help.
Can I use Excel's built-in worksheet functions in my VBA code?
In most cases, yes. Excel's worksheet functions are accessed via the WorksheetFunction method of the Application object. For example, you could access the POWER worksheet functions with a statement such as the following:
Ans = Application.WorksheetFunction.Power(5, 3)
This example raises 5 to the third power.
Generally, if VBA includes an equivalent function, you cannot use Excel's worksheet version. For example, because VBA has a function to compute square roots (Sqr) you cannot use the SQRT worksheet function in your VBA code.
Excel 95 doesn't support the WorksheetFunction method. Does that mean I can't make my Excel 2000 application compatible with Excel 95?
No. Actually, using the WorksheetFunction method is superfluous. The following statements have exactly the same result:
Ans = Application.WorksheetFunction.Power(5, 3) Ans = Application.Power(5, 3)
Is there any way to force a line break in the text of a message box?
Use a carriage return or a line feed character to force a new line. The following statement displays the message box text on two lines. vbCr is a built-in constant that represents a carriage return.
MsgBox "Hello" & vbCr & Application.UserName
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks