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

score 0 · Answer 1 · answered Jan 07 '19 at 18:08

0

Solution 1: You could use a common paths in both computers
(for example: C:\work , C:\Work2)
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

answered Jan 07 '19 at 18:08

ruirodrigues1971

302
1
6

Ehhm there already exists a default path for add-ins: `C:\Users\YOURNAME\AppData\Roaming\Microsoft\AddIns\ ` – Pᴇʜ Jan 08 '19 at 07:33

score 0 · Answer 2 · answered Jan 08 '19 at 07:31

0

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.

answered Jan 08 '19 at 07:31

Pᴇʜ

45,553
9
41
62

*"does not work"* is a completely useless error description. We cannot help you if you don't give us information. Follow the steps in the link I gave this is the way to go and *must* work! – Pᴇʜ Jan 10 '19 at 07:44
i create the function, i install add-in correctly in both computer (A and B). In both computer work the function if i use it from a file created in each computer. However, when i create a file in computer "A" that uses the created function and try to open the file in computer "B", it does not work, i must to change the path of the function in the file... – Mouse Jan 10 '19 at 13:24
Make sure that the Excel-Addin is loaded when Excel starts (check in RibbonMenu › File › Options › Add-Ins, it must be active). Then Remove the path completely from the formula. And it should work. – Pᴇʜ Jan 10 '19 at 14:29
Yes, I'm very sure of it. – Mouse Jan 11 '19 at 15:36
@Mouse still you must be doing it wrong. Follow this guide step by step: [Create an Excel Add-In for User Defined Functions (UDF’s)](https://www.myonlinetraininghub.com/create-an-excel-add-in-for-user-defined-functions-udfs) – Pᴇʜ Jan 14 '19 at 07:19

Aaron Pan Vega · Answer 3 · 2019-01-08T09:27:56.510

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

custom function in excel using vba that works in any matchine

3 Answers3