Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Not Recognized
.... Not for the usual reason.
I have a UDF that lives in a standard module in an add-in. The add-in loads automatically. I have one workbook with two worksheets that call the UDF. For some time I had no problems, but recently I tweaked one of the worksheets. Now, that worksheet no longer recognizes the UDF (returns #NAME). The other, untweaked worksheet works fine. I made a copy of the UDF in the module where it resides, gave it a different name, and adjusted the worksheet to call the new copy. Works great. But surely I don't need to keep two identical copies of a UDF in the same module... What could be going on here? For what it's worth the add-in has dozens of UDFs (in other modules) that work just fine. This is Ex2003 by the way. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Not Recognized
This is just a guess at how to fix it -- not an explanation.
I'd get rid of the second UDF. Then on the troublesome worksheet, I'd change the function calls back to the original name. My bet is that will fix the problem. If it doesn't, try selecting all the cells. Edit|replace what: = (equal sign) with: = replace all I bet (er, I hope) that excel will see the change (even the function call in the formula change) and reevaluate the formula. If it doesn't, then the ='s to ='s may work. But without more info about that UDF and its code, I wouldn't venture a guess why it broke. On 08/12/2010 10:31, Smartin wrote: ... Not for the usual reason. I have a UDF that lives in a standard module in an add-in. The add-in loads automatically. I have one workbook with two worksheets that call the UDF. For some time I had no problems, but recently I tweaked one of the worksheets. Now, that worksheet no longer recognizes the UDF (returns #NAME). The other, untweaked worksheet works fine. I made a copy of the UDF in the module where it resides, gave it a different name, and adjusted the worksheet to call the new copy. Works great. But surely I don't need to keep two identical copies of a UDF in the same module... What could be going on here? For what it's worth the add-in has dozens of UDFs (in other modules) that work just fine. This is Ex2003 by the way. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Not Recognized
On Aug 12, 1:12*pm, Dave Peterson wrote:
This is just a guess at how to fix it -- not an explanation. I'd get rid of the second UDF. Then on the troublesome worksheet, I'd change the function calls back to the original name. My bet is that will fix the problem. If it doesn't, try selecting all the cells. Edit|replace what: *= *(equal sign) with: *= replace all I bet (er, I hope) that excel will see the change (even the function call in the formula change) and reevaluate the formula. If it doesn't, then the ='s to ='s may work. But without more info about that UDF and its code, I wouldn't venture a guess why it broke. On 08/12/2010 10:31, Smartin wrote: ... Not for the usual reason. I have a UDF that lives in a standard module in an add-in. The add-in loads automatically. I have one workbook with two worksheets that call the UDF. For some time I had no problems, but recently I tweaked one of the worksheets. Now, that worksheet no longer recognizes the UDF (returns #NAME). The other, untweaked worksheet works fine. I made a copy of the UDF in the module where it resides, gave it a different name, and adjusted the worksheet to call the new copy. Works great. But surely I don't need to keep two identical copies of a UDF in the same module... What could be going on here? For what it's worth the add-in has dozens of UDFs (in other modules) that work just fine. This is Ex2003 by the way. -- Dave Peterson Thanks for the suggestions, Dave. They are good ones for the books. I now recall using the replace = with = trick to fix UDF calls in the past. However, no luck with any of that today. I did make some headway though. In the process of deleting and re-typing UDF calls I noticed at one point the function I wrote as =SmoothY(...) turned into =MyAddIn!SmoothY(...) without my prompting. Then I remembered -- I originally developed the function in the workbook, then moved it later to the add-in. Near the same time I moved the location of the add-in from a network to which I am not always attached to the local C:. It seems the worksheet is confused about where the function is located. I searched the installed projects to ensure there is no duplication of the function name "SmoothY" -- it only exists in the add-in. But I was able, at least temporarily, to fix the worksheet by prepending "MyAddIn!" to the function call. I will see how it goes over the next few update cycles (which includes on and off the network). Thanks again for your thoughts! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Not Recognized
On Thu, 12 Aug 2010 18:12:06 -0700 (PDT), Smartin
wrote: Thanks for the suggestions, Dave. They are good ones for the books. I now recall using the replace = with = trick to fix UDF calls in the past. However, no luck with any of that today. I did make some headway though. In the process of deleting and re-typing UDF calls I noticed at one point the function I wrote as =SmoothY(...) turned into =MyAddIn!SmoothY(...) without my prompting. Then I remembered -- I originally developed the function in the workbook, then moved it later to the add-in. Near the same time I moved the location of the add-in from a network to which I am not always attached to the local C:. It seems the worksheet is confused about where the function is located. I searched the installed projects to ensure there is no duplication of the function name "SmoothY" -- it only exists in the add-in. But I was able, at least temporarily, to fix the worksheet by prepending "MyAddIn!" to the function call. I will see how it goes over the next few update cycles (which includes on and off the network). Thanks again for your thoughts! Somehow, between your add-in and your workbook, you have what I call "name confusion". You are probably using the same UDF name in two different locations. It could be the same function (name) in two different modules; or maybe even a module that has the same name as your UDF. Fix that and you should be OK. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UDF Not recognized | Excel Worksheet Functions | |||
value not recognized as a formula | Excel Worksheet Functions | |||
value not recognized as a formula | Excel Worksheet Functions | |||
One cell isn't recognized? | Excel Discussion (Misc queries) | |||
Add-In not Recognized | Excel Programming |