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

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." wrote in message
...
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

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" wrote in message
...
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." wrote in message
...
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

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

On Mon, 4 Jan 2010 16:50:27 -0700, "Thomas M."
wrote:

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" wrote in message
...
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." wrote in message
...
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

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" <gorddibbATshawDOTca wrote in message
...
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

On Mon, 4 Jan 2010 16:50:27 -0700, "Thomas M."
wrote:

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" wrote in message
...
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." wrote in message
...
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

Good to hear you're sorted.

Thanks for the feedback and good luck with the project.


Gord

On Mon, 4 Jan 2010 17:58:49 -0700, "Thomas M."
wrote:

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" <gorddibbATshawDOTca wrote in message
.. .
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

On Mon, 4 Jan 2010 16:50:27 -0700, "Thomas M."
wrote:

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" wrote in message
...
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." wrote in message
...
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

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/Writin...ionsInVBA.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


.

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" wrote in message
...
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/Writin...ionsInVBA.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


.