Excel path problem with UDFs stored at different locations

[hege]

Hi all,
I have a problem. We have created some common UDF Excel functions and
put them into an XLA Adds-in (eg COMMON.XLA) file and distributed them
among several users.
Users then store these COMMON.XLA files wherever they want on their
hard drives.

Problem:

If a user uses one of these UDFs in their own Excel sheet, everything
works fine. - Eg: A1 cell contains: "=MYFUNC()"
However when the Excel sheet is opened by an other user, where the
physical location of this COMMON.XLA is different, then Excel will not
find MYFUNC() function, COMMON.XLA is open (and hidden), because what
the user sees is something like this:
"=+'C:\Program files\My functions\[COMMON.XLA]'!MYFUNC()"
and A1 cell contains a #REF error message as the (otherwise open)
COMMON.XLA is in a different folder.

How can I tell Excel to ignore the path for UDFs?

Thanks,
Hege

 

[PBezucha]

Hi Hege,
I hope there are several smart ways how to tackle the problem. One, perhaps
clumsy, is as follows. As the snag is in fact in the work books that are
calling xla, you may delete generally inappropriate paths in all the formulas
by a macro, which you distribute and run at users, together with proper
add-in linking. The following example finds at first the whole wrong text
containing "common.xla" and uses it for deleting in all further cells in all
worksheets. Not tested for .xla, only .xls, but you can obviously adapt.

Option Explicit
Sub DirReplace()
Dim KeyWrd As String, FormulaText As String, BegEqPos As Integer, _
EndExclamPos As Integer, ToBeReplaced As String, Sh As Worksheet
KeyWrd = "common.xla"
On Error GoTo ErrExit
FormulaText = Cells.Find(What:=KeyWrd, After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Formula
BegEqPos = InStr(1, FormulaText, "=", vbTextCompare)
EndExclamPos = InStr(BegEqPos, FormulaText, "!", vbTextCompare)
ToBeReplaced = Mid$(FormulaText, BegEqPos + 1, EndExclamPos - BegEqPos)
For Each Sh In ActiveWorkbook.Sheets
Sh.Cells.Replace What:=ToBeReplaced, Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next
ErrExit:
End Sub

Good luck