Microsoft Office Tutorials and References

In Depth Information

Sometimes, however, when I enter a formula that uses a custom function, Excel will not match the case that I

used in the VBA code. This was a mystery until I figured out the trick. Assume you have a function named

MYFUNC, and its function declaration uses uppercase for the name. When you type the function into a formula,

though, Excel does
not
display it in uppercase. Here's how to fix it:

In Excel, choose Formulas⇒Defined Names⇒Define Name and create a name called MYFUNC (in uppercase let-

ters). It doesn't matter what the name refers to.

Creating that name causes all formulas that use the MYFUNC function to display an error. That's to be expected.

But, you'll notice that the formula now displays MYFUNC in uppercase characters.

The final step: Choose Formuals⇒Defined Names⇒Name Manager and
delete
the MYFUNC name. The formulas

will no longer display an error — and they will retain the uppercase letters for the function name.

I can't tell you
why
this trick works, but it does seem to work every time.

Using functions in formulas

Using a custom VBA function in a worksheet formula is like using a built-in worksheet function. However, you

must ensure that Excel can locate the Function procedure. If the Function procedure is in the same workbook as

the formula, you don't have to do anything special. If it's in a different workbook, you may have to tell Excel

where to find it. You can do so in three ways:

•
Precede the function's name with a file reference
. For example, if you want to use a function called Coun-

tNames that's defined in a workbook named Myfuncs.xlsm, you can use a formula like the following:

=MyFunctions.xlsm!CountNames(A1:A1000)

If you insert the function with the Insert Function dialog box, the workbook reference is inserted automatic-

ally.

•
Set up a reference to the workbook
. You do this with the VB Editor's Tools
⇒
References command (see

Figure 24-2). If the function is defined in a referenced workbook, you don't need to use the worksheet name.

Even when the dependent workbook is assigned as a reference, the Insert Function dialog box continues to

insert the workbook reference (even though it's not necessary). Note that the referenced workbook must be

open in order to use the functions defined in it.

Function names in a referenced workbook do not appear in the Formula AutoComplete drop-down list. For-

mula AutoComplete works only when the formula is entered into the workbook that contains the custom

function or when it is contained in an installed add-in.