I have a project, which uses UDFs, running in several computers. When I open the file in computer 1, after I saved it in computer 2, the call to a formula in a cell is proceded by the full path to the UDF, where the full path points to the path in computer 2, where it was saved (Extremely clearly described;)).
E.g. ='C:\Users\HBJ\AppData\Roaming\Microsoft\AddIns\HB JUDF.xlam'!MyFunc(xxx,yyy)

HBJUDF.xlam is located in a common directory (Dropbox) accessible from all computers.

How should I reference the UDF to prevent it to load into the local AddIns directory?

How can I prevent Excel from adding full path name to the function call?

Thank you in advance!
HÃ¥kan

hi HÃ¥kan,

try:
x = Application.UserName
MyVar = "'C:\Users\" & x &
"\AppData\Roaming\Microsoft\AddIns\HBJUDF.xlam'!My Func(xxx,yyy)"

look here, to differentiate the Excel version:
http://www.rondebruin.nl/win/s9/win012.htm

isabelle

Le 2016-06-14 Ã* 14:13, hbj a écrit :
I have a project, which uses UDFs, running in several computers. When I open
the file in computer 1, after I saved it in computer 2, the call to a formula
in a cell is proceded by the full path to the UDF, where the full path points
to the path in computer 2, where it was saved (Extremely clearly
described;)). E.g.
='C:\Users\HBJ\AppData\Roaming\Microsoft\AddIns\HB JUDF.xlam'!MyFunc(xxx,yyy)

HBJUDF.xlam is located in a common directory (Dropbox) accessible from all
computers.

How should I reference the UDF to prevent it to load into the local AddIns
directory?

How can I prevent Excel from adding full path name to the function call?

Thank you in advance! HÃ¥kan

Sorry Isabelle I could not express myself clearly enough. The point is: I want to completely get rid of the full path in front of the function. Because I have loaded the UDF, Excel should know that the function is in the loaded UDF w/o explicitly insert the path. As far as I understand the problem is how should I load the UDF in a way that Excel knows that its instance is loaded and the full path name is not required.
HÃ¥kan

"hbj" wrote in message
Sorry Isabelle I could not express myself clearly enough. The point is: I
want to completely get rid of the full path in front of the function.
Because I have loaded the UDF, Excel should know that the function is in the
loaded UDF w/o explicitly insert the path. As far as I understand the
problem is how should I load the UDF in a way that Excel knows that its
instance is loaded and the full path name is not required.
Håkan

=============================

I don't think you've given us the complete picture. Is the UDF actually
working in formulas that contain an address to the addin, and not a case of
needing to update links?

Peter T

On Wednesday, June 15, 2016 at 4:34:52 PM UTC+3, Peter T wrote:
"hbj" wrote in message
Sorry Isabelle I could not express myself clearly enough. The point is: I
want to completely get rid of the full path in front of the function.
Because I have loaded the UDF, Excel should know that the function is in the
loaded UDF w/o explicitly insert the path. As far as I understand the
problem is how should I load the UDF in a way that Excel knows that its
instance is loaded and the full path name is not required.
HÃ¥kan

=============================

I don't think you've given us the complete picture. Is the UDF actually
working in formulas that contain an address to the addin, and not a case of
needing to update links?

Peter T

Hello Isabelle and Peter,

I think Isabelle is quite right, though explained the solution simply. After studying the case a while I understood that an UDF behaves differently from Excel internal functions in that way, that Excel needs the path to the XLAM. This file should point to the same folder in every machine. If not, Excel shows up the full path to the XLAM. The location of this file is different in each computer. Here Isabelles suggestion is the solution: Load the xlam programmatically on worksheet open. The file resides in C:\<USER\Dropbox\common, where <USER is different in each computer. So, so far solved. Thank you folks.
But, I found when retrieving user name, functions "Application.UserName" does not return same value as Environ$("username"). What is the difference?

I also try to load the XLAM to references with command
Application.VBE.ActiveVBProject.References.AddFrom File <FullPathToXLAM.
Is returns error code 32813 "Application-defined or object-defined error".

HÃ¥kan

"hbj" wrote in message
But, I found when retrieving user name, functions "Application.UserName"
does not return same value as Environ$("username"). What is the difference?

Application.UserName is an Excel property that you can configure in
Excel/Options
Environ$("username") returns the Windows login name

I also try to load the XLAM to references with command
Application.VBE.ActiveVBProject.References.AddFro mFile <FullPathToXLAM.
Is returns error code 32813 "Application-defined or object-defined error".

That should work providing 'trust access to VB Project' is enabled.
Ensure the addin has a unique project name and not VBAProject

Although the code is OK probably better to add references to a specified
workbook, depending on what's selected in project explorer the activeproject
might not be the one that's currently showing a module, eg

myBook.VBProject.References.AddFromFile()

Peter T

On Tuesday, June 14, 2016 at 9:14:05 PM UTC+3, hbj wrote:
I have a project, which uses UDFs, running in several computers. When I open the file in computer 1, after I saved it in computer 2, the call to a formula in a cell is proceded by the full path to the UDF, where the full path points to the path in computer 2, where it was saved (Extremely clearly described;)).
E.g. ='C:\Users\HBJ\AppData\Roaming\Microsoft\AddIns\HB JUDF.xlam'!MyFunc(xxx,yyy)

HBJUDF.xlam is located in a common directory (Dropbox) accessible from all computers.

How should I reference the UDF to prevent it to load into the local AddIns directory?

How can I prevent Excel from adding full path name to the function call?

Thank you in advance!
HÃ¥kan

I'm sorry to admit, I'm still struggling with the same problem. The full path name to the add-in in front of the function is only a side effect of the main problem. I have three computers running Excel projects, all using a common Add-in "myUDF.xlam" located i Dropbox. When I in PC1 edit a cell content referring to a function in the Add-in, Excel places the full path to the function in front of the function. When I open the same file in PC2, the same cell function refers to the path located in the first PC. I suppose that I can get rid of this by in "Workbook_Open()" copying" the Add-In to the local Office macro folder - and also let Excel refer to the copied local Add-in. But how???

HÃ¥kan