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:
If you insert the function with the Insert Function dialog box, the workbook reference is inserted automatic-
• 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.