#NAME? Error With User Defined Functions

[Thomas M.]

Excel 2007

I have a problem that has been driving me nuts. I created a code module and
have pasted a couple of user defined functions into the module. When I am
entering my formula into a cell and I start typing the name of the function,
I see that the function is listed in the drop-down list of functions. That
tells me that Excel recognizes the function. However, after entering the
formula I get a #NAME? error.

Here is a link to just one of the user defined functions that I've been
trying to use.

http://xldynamic.com/source/xld.ColourCounter.html#code

Other people have successfully used the function as-is without any problems
by doing exactly the same things that I've done. Yet, when I try to use the
function I just get the #NAME? error. Furthermore, I've tried a number of
different functions and with every one I get the #NAME? error.

Is there something that I need to do in order to use custom functions, like
maybe install an add-in or something, or is there a configuration setting
that enables the use of custom functions?

Thanks for any help that you can offer.

--Tom

 

[Niek Otten]

The function should be in a General module, not a Sheet module or a
Workbook module

But maybe one of the arguments is interpreted as a name that is not
recognized

What is your formula?

 

[Gary''s Student]

Hi Thomas:

The error means that Excel can't find the UDF. You must make it easy to find:

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
for specifics on UDFs

 

[Thomas M.]

Thanks for the response.

In the Project Explorer I right-clicked Modules and went to Insert > Module,
and then gave the module the name of CustomFunctions. I pasted the code
into that module. My formula is essentially the same used on the page to
which I linked in my original message, except that I modified it slightly
because my data range is only 7 rows instead of the 100 rows given in the
example. The formula I am using is:

=SUMPRODUCT(--(ColorIndex(A2:A8)=3),A2:A8)

According to the article, this should sum all cells in A2:A8 where the
background color is red.

--Tom

 

[Thomas M.]

Thanks for the reply.

What you suggest is basically what I did. See my reply to Niek for the
precise steps that I used and the formula that I am using.

--Tom

 

[Gord Dibben]

The four functions from Bob's site are all in a General module in the
workbook in which you have the formula?

And you saved the workbook as macro-enabled *.xlsm?

I have no problems in 2007 with these.

If the Functions are in a separate worlbook you must call like so

=SUMPRODUCT(--(otherbook.xlsm!ColorIndex(A2:A8)=3),A2:A8)


Gord Dibben MS Excel MVP

 

[Thomas M.]

Got it!

I had not saved the file as an .xlsm file. I'm new to Excel 2007 and my
employer has Excel configured to save as .xls files by default because not
everyone is switched over yet. I didn't realize that files with macros had
to be saved as .xlsm files.

Thanks for the information. It is a huge help to me to have this issue
resolved.

--Tom

 

[Gord Dibben]

Good to hear you're sorted.

Thanks for the feedback and good luck with the project.


Gord