A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Why full path in the formula to an UDF function?



 
 
Thread Tools Display Modes
  #1  
Old June 14th 16, 07:13 PM posted to microsoft.public.excel.programming
hbj
external usenet poster
 
Posts: 22
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
Ads
  #2  
Old June 15th 16, 03:28 AM posted to microsoft.public.excel.programming
isabelle
external usenet poster
 
Posts: 92
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  
Old June 15th 16, 11:58 AM posted to microsoft.public.excel.programming
hbj
external usenet poster
 
Posts: 22
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  
Old June 15th 16, 02:36 PM posted to microsoft.public.excel.programming
Peter T[_7_]
external usenet poster
 
Posts: 124
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.
Hkan

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

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  
Old June 16th 16, 01:20 PM posted to microsoft.public.excel.programming
hbj
external usenet poster
 
Posts: 22
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  
Old June 16th 16, 04:26 PM posted to microsoft.public.excel.programming
Peter T[_7_]
external usenet poster
 
Posts: 124
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  
Old December 30th 16, 02:33 PM posted to microsoft.public.excel.programming
hbj
external usenet poster
 
Posts: 22
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 02:09 PM.


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