Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

I created a VBA function for use in worksheet formulas. However, it always returns # NAME ?. What went wrong?

You probably put the function in the code module for a Sheet (for example, Sheet1 ) or in the ThisWorkbook module. Custom worksheet functions must reside in standard VBA modules.

I wrote a VBA function that works perfectly when I call it from another procedure, but it doesn't work when I use it in a worksheet formula. What's wrong?

VBA functions called from a worksheet formula have some limitations. In general, they must be strictly passive . That is, they can't change the active cell , apply formatting, open workbooks, or change the active sheet. If the function attempts to do any of these things, the formula will return an error.

Functions can only perform calculations and return a value. An exception to this rule is the VBA MsgBox function. A custom function can display a message box whenever it is recalculated. This is very handy for debugging a custom function.

When I access a custom worksheet function with the Insert Function dialog box, 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 View Macros 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 :

Open table as spreadsheet

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.

How can I force a recalculation of formulas that use my custom worksheet function?

Press Ctrl+Alt+F9.

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 SUM worksheet function with a statement such as the following:

Ans = Application.WorksheetFunction.Sum(Range("A1:A3"))

This example assigns the sum of the values in A1:A3 (on the active sheet) to the Ans variable.

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 can't use the SQRT worksheet function in your VBA code.

Is there any way to force a line break in the text of a message box?

Use a carriage return or a linefeed character to force a new line. The following statement displays the message box text on two lines. vbNewLine is a built-in constant that represents a carriage return.

MsgBox "Hello" & vbNewLine & Application.UserName