This may be relevant to you if:
1 You have written (or acquired) a custom add-in
2 You use functions from the add-in in workbooks which you give to other people.
3 The users have a copy of the add-in but it is not necessarily in the same folder as the one you use.
In the above scenario, when the user opens the workbook you have given to him, the cells which refer to the add-in functions will show the #NAME? error - even if he has the add-in installed. This is because when Excel saves a workbook containing cells with an add-in function, it saves an explicit reference to the whole of the path to the add-in. So if the user cannot see the add-in at exactly the same path location as you, he will get the above error. It IS possible to overcome this by searching for and deleting the path reference in all the cells where the add-in functions are called, but this is a bit of a pain and some users might well get it wrong, or be put off by the need to do this.
However, there is a way to avoid the problem. It has taken me a while to find it, and I can't say I fully understand it as yet, but it does seem to work, so if the above scenario ever applies to you, read on...
Before you distribute your workbook do the following:
1 In the VBA editor window, click on the workbook in the project explorer.
2 Select the menu item "Tools" then "References".
3 You will see a list of available references, which should include the add-in. Click the add-in's checkbox and hit OK.
4 If you have any code in the workbook open event skip to step 7.
5 In the project explorer window, double click the "Thisworkbook" item to open the workbook code pane.
6 Add code to the workbook open event - this can be as trivial as declaring a variable.
7 Save the workbook and close it.
8 If you already had code in the workbook open event and wish to keep it, then the workbook is now ready to distribute. If not, procede to step 9.
9 Open the workbook, go to the VBA editor, open the Thisworkbook code pane and delete the code in the workbook open event.
10 Save the workbook.
Now when you give the workbook to anyone who already has the add-in installed it will automatically find and reference THEIR version of the addin when the workbook opens - so they do not need to do anything else.
If I get the time, I plan to write some code to automatically go through the necessary steps, so that I can attach it to a button on my add-in to ensure that whenever I want to distribute a workbook with add-in functions I can just hit the button before doing so and it will be ready to go. If I manage to get this done, I'll post the code here.
This problem has been a bit of a pain round here because I work in a networked environment and not everybody has access to the same areas, so it is not possible to ensure that all of the people who use my add-in can refer to a common version.
I hope somebody else benefits from this, so if you have found it helpful, please let me know.
Tony
1 You have written (or acquired) a custom add-in
2 You use functions from the add-in in workbooks which you give to other people.
3 The users have a copy of the add-in but it is not necessarily in the same folder as the one you use.
In the above scenario, when the user opens the workbook you have given to him, the cells which refer to the add-in functions will show the #NAME? error - even if he has the add-in installed. This is because when Excel saves a workbook containing cells with an add-in function, it saves an explicit reference to the whole of the path to the add-in. So if the user cannot see the add-in at exactly the same path location as you, he will get the above error. It IS possible to overcome this by searching for and deleting the path reference in all the cells where the add-in functions are called, but this is a bit of a pain and some users might well get it wrong, or be put off by the need to do this.
However, there is a way to avoid the problem. It has taken me a while to find it, and I can't say I fully understand it as yet, but it does seem to work, so if the above scenario ever applies to you, read on...
Before you distribute your workbook do the following:
1 In the VBA editor window, click on the workbook in the project explorer.
2 Select the menu item "Tools" then "References".
3 You will see a list of available references, which should include the add-in. Click the add-in's checkbox and hit OK.
4 If you have any code in the workbook open event skip to step 7.
5 In the project explorer window, double click the "Thisworkbook" item to open the workbook code pane.
6 Add code to the workbook open event - this can be as trivial as declaring a variable.
7 Save the workbook and close it.
8 If you already had code in the workbook open event and wish to keep it, then the workbook is now ready to distribute. If not, procede to step 9.
9 Open the workbook, go to the VBA editor, open the Thisworkbook code pane and delete the code in the workbook open event.
10 Save the workbook.
Now when you give the workbook to anyone who already has the add-in installed it will automatically find and reference THEIR version of the addin when the workbook opens - so they do not need to do anything else.
If I get the time, I plan to write some code to automatically go through the necessary steps, so that I can attach it to a button on my add-in to ensure that whenever I want to distribute a workbook with add-in functions I can just hit the button before doing so and it will be ready to go. If I manage to get this done, I'll post the code here.
This problem has been a bit of a pain round here because I work in a networked environment and not everybody has access to the same areas, so it is not possible to ensure that all of the people who use my add-in can refer to a common version.
I hope somebody else benefits from this, so if you have found it helpful, please let me know.
Tony
