R
ryan
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 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 are: 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 altered, not within the scope of the
original workbook at least; is this true? If it is, it seems like a huge
detriment to the operability of using 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
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 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 are: 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 altered, not within the scope of the
original workbook at least; is this true? If it is, it seems like a huge
detriment to the operability of using 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