Add-In UDF returning #NAME error

[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 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 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 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

 

[Tom Hutchins]

I can't answer your deeper question about the nature of Excel, but I have
encountered the same problem. My workaround was to reset the OnAction
property for every button on my custom toolbar using a Workbook_Activate
event subroutine, to force the toolbar buttons to call the macros in the
correct workbook (ThisWorkbook, in my case). For example,

For x = 1 To CommandBars("Gradebook").Controls.Count
Select Case LCase(CommandBars("Gradebook").Controls.Item(x).Caption)
Case "enter absent for this grade"
CommandBars("Gradebook").Controls.Item(x).OnAction = _
ThisWorkbook.FullName & "!MarkAbsent"
Case "mark this grade late"
CommandBars("Gradebook").Controls.Item(x).OnAction = _
ThisWorkbook.FullName & "!MarkAsLate"
etc.

Maybe there is a simple settings change which will solve the problem. If so,
I'd like to learn about it too.

Hope this helps,

Hutch

 

[ryan]

Thanks for the reply, Tom.

Unfortunately, for a UDF, I can't just explicitly set the association
between the call and the code. Unless I make a macro that just takes off any
path string that gets affixed to the front of my UDF call. I could probably
do that, but it just seems so clunky. In fact, off the top of my head, I
don't know which event should trigger such a patch-macro... but anyway, I
really appreciate your response and ideas. Your work-around definitely gets
me thinking about ways I could circumvent this issue. In fact, it's a big
relief just to hear that I'm not alone on this one.

If anyone else understands the problem that we're talking about, and can
shed more light on a solution or at least a better understanding of the
underlying issues, that would be excellent.

Thanks Again,
Ryan

 

[Tom Hutchins]

It wouldn't be hard to strip the path before every UDF function call in a
cell. If you did that via a macro when you open a workbook (Workbook _Open
maybe), existing formulas would be fixed and any new fornulas calling the UDF
should be okay anyway. Here is a macro which checks the formulas on every
sheet in the active workbook. If a formula contains the UDF name, everything
before the UDF name is replaced with just an equal sign.

Sub CleanRx(WhichUDF As String)
Dim c As Range, sht As Worksheet
On Error Resume Next
For Each sht In ActiveWorkbook.Sheets
sht.Activate
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Select
For Each c In Selection
If InStr(1, c.Formula, WhichUDF$) > 0 Then
c.Formula = "=" & _
Right(c.Formula, Len(c.Formula) - _
InStr(1, c.Formula, WhichUDF$) + 1)
End If
Next c
Next sht
End Sub

You call it like this:
Sub CleanAllRx()
Call CleanRx("ReturnVal")
Call CleanRx("OtherUDF")
End Sub

Hope this helps,

Hutch

 

[ytayta555]

I,m sure am so impolite , but ..., mr Tom Hutchins ,
can you look a little to my problem ,,Useing IF
to Copy a Range ,, , when you have time ?
thouzand of apologise for Rian .... I'm so
impatient to find the solution ....

Respectfully

my apologise

 

[ryan]

Thanks, that's good code. I may wind up using that, but for now I've decided
to store the add-in itself on the network. It's not my preferred solution,
but it will have to do for now.

I hope in the future MS addresses this issue by allowing Excel to associate
UDF calls with their code more dynamically. In the current state it seems so
stubbornly static. Once the UDF is typed in a worksheet, it's not explicitly
apparent, but evidently the full path of the add-in is implied in the UDF
call. And, it appears that once this specific path is assumed, it can't be
unassumed, even when the host-computer changes and the add-in directory is
not the same. To me, the UDF is just defined by its name, and wherever Excel
can match that name with its code in whatever add-ins are installed on that
particular machine, then do it... make that match and run the code.

It just seems that the usefulness of add-ins is greatly diminished otherwise.

Thanks again for your help.