#NAME? Error With User Defined Functions
in [Functions]
|
From: Gord Dibben on 4 Jan 2010 19:51 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." <NoEmailReplies(a)Please.com> 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" <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 19:58 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 news:8n25k51ldvjj94tolhd4bpegspvfdjvich(a)4ax.com... > 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." <NoEmailReplies(a)Please.com> > 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" <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: Gord Dibben on 4 Jan 2010 20:39 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." <NoEmailReplies(a)Please.com> 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 >news:8n25k51ldvjj94tolhd4bpegspvfdjvich(a)4ax.com... >> 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." <NoEmailReplies(a)Please.com> >> 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" <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 >>>>> >>>> >>> >> >
First
|
Prev
|
Pages: 1 2 Prev: Countif Formula Next: cursor changes to arrow dash arrow in worksheet |