#NAME? Error With User Defined Functions
in [Functions]
|
From: Thomas M. on 31 Dec 2009 20:19 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 1 Jan 2010 08:14 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 1 Jan 2010 08:20 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 4 Jan 2010 18:50 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 4 Jan 2010 18:51
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 >> >> >> . >> |