Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
hbj hbj is offline
external usenet poster
 
Posts: 35
Default Why full path in the formula to an UDF function?

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default Why full path in the formula to an UDF function?

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

  #3   Report Post  
Posted to microsoft.public.excel.programming
hbj hbj is offline
external usenet poster
 
Posts: 35
Default Why full path in the formula to an UDF function?

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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default Why full path in the formula to an UDF function?

"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



  #5   Report Post  
Posted to microsoft.public.excel.programming
hbj hbj is offline
external usenet poster
 
Posts: 35
Default Why full path in the formula to an UDF function?

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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default Why full path in the formula to an UDF function?


"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



  #7   Report Post  
Posted to microsoft.public.excel.programming
hbj hbj is offline
external usenet poster
 
Posts: 35
Default Why full path in the formula to an UDF function?

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using 'indirect' function with full network path [email protected] Excel Worksheet Functions 0 July 18th 12 03:37 AM
Using 'indirect' function with full network path [email protected] Excel Discussion (Misc queries) 0 July 17th 12 03:55 PM
Full path possible to be seen? Octavio New Users to Excel 10 July 2nd 06 08:51 PM
Full path in title bar ntoze Excel Discussion (Misc queries) 1 April 18th 06 04:18 PM
Syntax for using the full path to get a value Tommy Flynn[_2_] Excel Programming 4 November 17th 03 08:15 PM


All times are GMT +1. The time now is 08:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"