custom function in excel using vba that works in any matchine

Question

I have created a custom function via vba in excel. If I use it in my computer, it works ok, but if I change the file to another computer (where this computer also has the created function), it does not work. I must change the path of the created function. Is there any way to not change the path everytime I copy the file into another computer?

='C:\Users\Usuario1\Documents\Complementos\BondsTIRMDuration.xlam'!TIrbonds($A2;F2;'C:\Users\Usuario1\Documents\Complementos\AsBusinessDay.xlam'!asbusinessday('C:\Users\Usuario1\Documents\Complementos\AsBusinessDay.xlam'!PrevBusinessDay(HOY())))*100

Answer 1

1. Solution 1: You could use a common paths in both computers
   (for example: C:\work , C:\Work2)

2. Solution 2: You could put all files in the same path (C:\work), then you only need the to put the file name

   ='BondsTIRMDuration.xlam'!TIrbonds($A2;F2;'AsBusinessDay.xlam'!asbusinessday('AsBusinessDay.xlam'!PrevBusinessDay(HOY())))*100

Answer 2

Just save your add-in in the correct path on every computer.

It should be something like:

C:\Users\YOURNAME\AppData\Roaming\Microsoft\AddIns\

See Install and Use Excel Add-ins to determine the correct path.

If your add-in is installed correctly you should be able to run your user defined function without a path.

Answer 3

You can call special folder with application.

MsgBox Application.DefaultFilePath

This example will be: C:\Users\Usuario1\Documents

'Here are a few VBA path functions
    MsgBox Application.Path
    MsgBox Application.DefaultFilePath
    MsgBox Application.TemplatesPath
    MsgBox Application.StartupPath
    MsgBox Application.UserLibraryPath
    MsgBox Application.LibraryPath

You can too create wscrit object to call another paths, for example:

 MsgBox CreateObject("Wscript.Shell").SpecialFolders("Desktop")

Example folders for Wscript.shell object:

AllUsersDesktop
AllUsersStartMenu
AllUsersPrograms
AllUsersStartup
Desktop
Favorites
Fonts
MyDocuments
NetHood
PrintHood
Programs
Recent
SendTo
StartMenu
Startup
Templates

And execute a macro like this,(allways have to use same directory):

Sub Macro()
    AddIns.Add Filename:=Application.DefaultFilePath & "\Complement.xlam"
    AddIns("Complement").Installed = True
End Sub