Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function not recognized in formula
Excel 2003, XP SP3:
I have written VBA code to define a private Boolean function for my current workbook and inserted it in a new module belonging to that workbook. VBE clearly recognizes it and does not flag any errors. But when I reference the function in a formula (spelled correctly with upper-case letters where appropriate) the spelling gets altered and the cell shows #VALUE! When I try to debug the code, there is no evidence that it has been entered at all. I have previously tried creating the function under a different name and placed it in other modules in other workbooks (in my XLSTART folder), but I had no more success. In some of the tests, the cell value showed as #NAME? instead. What am I doing or not doing? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function not recognized in formula
Well, first of all, what is the name of your function? It is quite
likely that Excel's Autocorrect feature recognizes it as a word that is on the list of words to correct. On the Tools menu, choose AutoCorrect Options, and then see if your original function name is in the list. If so, delete that entry from the list or rename the function. Renaming is probably better if other users are going to use your function -- otherwise, they'd have to fix their own AutoCorrect list. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Sun, 14 Feb 2010 12:16:01 -0800, Hershmab wrote: Excel 2003, XP SP3: I have written VBA code to define a private Boolean function for my current workbook and inserted it in a new module belonging to that workbook. VBE clearly recognizes it and does not flag any errors. But when I reference the function in a formula (spelled correctly with upper-case letters where appropriate) the spelling gets altered and the cell shows #VALUE! When I try to debug the code, there is no evidence that it has been entered at all. I have previously tried creating the function under a different name and placed it in other modules in other workbooks (in my XLSTART folder), but I had no more success. In some of the tests, the cell value showed as #NAME? instead. What am I doing or not doing? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function not recognized in formula
Hi,
Post your function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Hershmab" wrote: Excel 2003, XP SP3: I have written VBA code to define a private Boolean function for my current workbook and inserted it in a new module belonging to that workbook. VBE clearly recognizes it and does not flag any errors. But when I reference the function in a formula (spelled correctly with upper-case letters where appropriate) the spelling gets altered and the cell shows #VALUE! When I try to debug the code, there is no evidence that it has been entered at all. I have previously tried creating the function under a different name and placed it in other modules in other workbooks (in my XLSTART folder), but I had no more success. In some of the tests, the cell value showed as #NAME? instead. What am I doing or not doing? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function not recognized in formula
<Post your function
And the formula with which you call it, together with the values and types of the arguments. You did enter the function in a standard module, didn't you? It should not be in a Sheet module or Workbook module. In general, if you get #Value and do not even hit a debug point in he code, the number of arguments is wrong or they are of the wrong type. -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Hi, Post your function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Hershmab" wrote: Excel 2003, XP SP3: I have written VBA code to define a private Boolean function for my current workbook and inserted it in a new module belonging to that workbook. VBE clearly recognizes it and does not flag any errors. But when I reference the function in a formula (spelled correctly with upper-case letters where appropriate) the spelling gets altered and the cell shows #VALUE! When I try to debug the code, there is no evidence that it has been entered at all. I have previously tried creating the function under a different name and placed it in other modules in other workbooks (in my XLSTART folder), but I had no more success. In some of the tests, the cell value showed as #NAME? instead. What am I doing or not doing? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function not recognized in formula
<"debug point"
should be "Breakpoint" Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... <Post your function And the formula with which you call it, together with the values and types of the arguments. You did enter the function in a standard module, didn't you? It should not be in a Sheet module or Workbook module. In general, if you get #Value and do not even hit a debug point in he code, the number of arguments is wrong or they are of the wrong type. -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Hi, Post your function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Hershmab" wrote: Excel 2003, XP SP3: I have written VBA code to define a private Boolean function for my current workbook and inserted it in a new module belonging to that workbook. VBE clearly recognizes it and does not flag any errors. But when I reference the function in a formula (spelled correctly with upper-case letters where appropriate) the spelling gets altered and the cell shows #VALUE! When I try to debug the code, there is no evidence that it has been entered at all. I have previously tried creating the function under a different name and placed it in other modules in other workbooks (in my XLSTART folder), but I had no more success. In some of the tests, the cell value showed as #NAME? instead. What am I doing or not doing? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function not recognized in formula
And you created a regular/normal/general module and put the code in that module?
What's the name of the function? What's the name of the module? Can you use the function in a simpler formula? What did that formula look like? Did you enable macros to run when you opened the workbook? But that would cause a Name error--not a Value error? Maybe it's working fine and your input isn't correct???? ====== If the function is in a different workbook, you would need to use something like: ='otherworkbookname.xls'!myFunc(a1) If the function is in a different workbook that's been saved as an addin, then you don't need to qualify it. =myfunc(a1) would work ok. ============= The uppercase/lowercase isn't the problem. Excel seems to use the case of whatever you chose to type the first time you used it in the first formula. Hershmab wrote: Excel 2003, XP SP3: I have written VBA code to define a private Boolean function for my current workbook and inserted it in a new module belonging to that workbook. VBE clearly recognizes it and does not flag any errors. But when I reference the function in a formula (spelled correctly with upper-case letters where appropriate) the spelling gets altered and the cell shows #VALUE! When I try to debug the code, there is no evidence that it has been entered at all. I have previously tried creating the function under a different name and placed it in other modules in other workbooks (in my XLSTART folder), but I had no more success. In some of the tests, the cell value showed as #NAME? instead. What am I doing or not doing? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Excel "Insert Formula" dialog always call my user defined function | Excel Programming | |||
How to write a formula with a user defined function | Excel Programming | |||
Formula as User Defined Function | Excel Programming | |||
User defined function not recognized | Excel Programming |