Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In UDF returning #NAME error
I've got a tough one for all you Excel/MS gurus. I don't think this is your
run-of-the-mill #NAME question. In fact, it's more of a general "how, when, and with what permanence does Excel match a UDF call with where it's code is stored?" question. Please bare with me... I have a pretty large and complex add-in, the nature of which precludes me from simply including the code in a Module with each spreadsheet that uses my UDF. The add-in works fine, I've been using it for years, but the problem arises when other people want to look at these spreadsheets from their computers (the spreadsheets are stored on the network). Naturally, I just installed the very same add-in on my colleagues' computers and figured that would allow the UDF to work for them as it did for me. So, the problem this created was that if User1 (who uses PC1) created a spreadsheet using my UDF, then User2 wanted to open that spreadsheet and take a look from PC2, all the UDF calls now had the full path of the add-in as a prefix, but this path referred to the local copy stored on PC1 (User1's computer), i.e. '=C:\Documents And Settings\User1\Application Data\Microsoft\AddIns\MyAddIn.xla!MyUDF(...)'. Of course this would return the #NAME error, and for obvious reasons. Simply going into each cell where the UDF was called and deleting the path, leaving only '=MyUDF(...)', would then fix it, but who wants to do that everytime? So, the questions this leaves a is there a way to make sure that the full path of the add-in is never affixed to the UDF calls? Why does Excel add the path prefix in the first place? Why can't Excel just realize that there's a local copy of the add-in on each computer that needs it? Is there some way to solve this? These are important questions (for me anyway), but the story doesn't end here. So, another approach that I tried was that I created a simpler version of the UDF that could be stored in a Module within the actual working spreadsheet. This created a problem of a similar nature... let me explain. As background information, let me tell you that I used the exact same name for the simplified version of the UDF, e.g. MyUDF, as I used for the add-in version (I realize that could cause complications, and it did, but that's not the point, please bare with me). When a spreadsheet was created on a single machine that had both the add-in version (installed) of the UDF and the simpler Module version of the UDF, this created the #VALUE error (this is not the point, it is strange, but perhaps the topic for a separate discussion). To get rid of the #VALUE error, I deleted the code from the Module, and then was faced with the #NAME error again. This new #NAME error is the curious part; the part that is of a similar nature to the original #NAME error (it's not exactly the same because now we're talking in the context of a single machine, not trying to share the spreadsheet between PCs). It's curious because when I delete the Module code I get the #NAME error even though there's still a perfectly good function definition in VBA code stored in the installed add-in, and furthermore, if I copy the entire worksheet (or just the range for that matter) where the UDF is being called to a new workbook, the UDF then returns the proper result as calculated by the code stored in the installed add-in. This experience, again, conjures questions like: at what point does an Excel workbook draw a connection between a UDF call and the location of the code that defines the function? And, it seems like once that connection is drawn, it can't be re-drawn, not within the scope of the original workbook at least; is this true? If it is, it seems like a huge detriment to the usefulness of add-ins. If anyone can suggest a solution, or at least shed some light on how Excel matches UDFs with their definitions, that would be enormously appreciated. A thousand apologies for the long-winded message, but this gives you some measure of the frustration that has pushed me to such lengths to get some answers! Many Thanks & Best Regards |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula returning an error | Excel Worksheet Functions | |||
Formula returning #N/A Error---why??? | Excel Worksheet Functions | |||
UDF Returning an Error Value: How to? | Excel Programming | |||
Vlookup in VB not returning anything error | Excel Programming | |||
Returning Error from Functions | Excel Programming |