From: Thomas M. on
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


From: Niek Otten on
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?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Thomas M." <NoEmailReplies(a)Please.com> wrote in message
news:OBlGDCoiKHA.1420(a)TK2MSFTNGP05.phx.gbl...
> 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
>

From: Gary''s Student on
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

--
Gary''s Student - gsnu200909


"Thomas M." wrote:

> 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
>
>
> .
>
From: Thomas M. on
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

"Niek Otten" <nicolaus(a)xs4all.nl> wrote in message
news:O1x5cRuiKHA.5248(a)TK2MSFTNGP04.phx.gbl...
> 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?
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
>
> "Thomas M." <NoEmailReplies(a)Please.com> wrote in message
> news:OBlGDCoiKHA.1420(a)TK2MSFTNGP05.phx.gbl...
>> 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
>>
>


From: Thomas M. on
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

"Gary''s Student" <GarysStudent(a)discussions.microsoft.com> wrote in message
news:12FA0567-8D1C-4C0A-9310-2C558C176312(a)microsoft.com...
> 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
>
> --
> Gary''s Student - gsnu200909
>
>
> "Thomas M." wrote:
>
>> 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
>>
>>
>> .
>>