Copying a Module and Updating Links

[Edgar]

Could use some help on this one?

I have an Excel add-in (with various functions and procedure in multiple modules) that many people in my work group use. However, the add-in is not available to everyone within the company.

Clearly, if a workbook with custom functions located in the add-in is sent to someone else, the functions no longer work.

I originally thought I could solve this problem by coping the module with the custom functions from the add-in to the workbook. I got it to do this just fine.

The problem now is that the formulas in the workbooks, written before the module is copied to the workbook, refer to the add-in and show up as broke links when opened without the add-in, even though a copy of the functions are located in the workbook.

The same is true going the other way. Formulas that refer to the workbook version of the functions do not automatically find the add-in functions when the workbook module is removed.

I don't want to break the links because I want to make sure everything still calculates.

Finally, I thought about just adding the module to every work book as it is created or opened, but I don't want my co workers to have to accept the enable macro options for every workbook they open.

Any thoughts?

[Simon Lloyd]

Edgar, Welcome to Vbax!, one of the reasons that you haven't had any responses yet is because you havent attached your workbook or sample workbook - without these its hard to visualize what is happening or supply any kind of reasonable answer, it's true most of the people who help out with our questions on this site are very gifted but need a little more to go on!

Regards,
Simon

[Bob Phillips]

Steppiong back, why is the add-in not available to everyone? You could store it on a network server to make it available, and you could add code to your workbooks that tests if it is loaded and warn if not.

[Edgar]

Stepping back, why is the add-in not available to everyone? You could store it on a network server to make it available, and you could add code to your workbooks that tests if it is loaded and warn if not.

The function module of the add-in that contains all the custom functions for our group is less that 5% of the entire add-in. The rest of the add-in has other tools for importing and manipulating data from legacy reports and interfacing with other software such as Hyperion.

My group is the only group that really needs 95% of the add-in. I want to copy the one module that has the custom functions into the workbooks and reset the links only when we have to send the workbook to others outside our group or outside the company.

I can't get permission to automatically put the function module in everyone's workbooks.

I am beginning to think that I should just put the function module in every new workbook created where the add-in is installed.

However, this creates two additional problems:

1. We add new functions every couple of weeks. The add-in is easily updated through an update procedure, but I am not sure how I would update the function modules embedded in every workbook.

2. Adding modules to the workbooks creates the annoying problem of having to enable them everytime the workbook is open. I am not sure how to get the security to recognize the macros as friendly and just open them.

Thanks for any help you can provide.

[Bob Phillips]

Split the add-in into two separate add-ins, the decision becomes simpler then.

[Norie]

Edgar

When you send out the workbooks is the add-in actually required?

Could you not just send out the data?

[Simon Lloyd]

Hi Edgar, check out thsi thread from this page onwards http://vbaexpress.com/forum/showthre...?t=9798&page=4 Ken Puls did a lot of work for me in network workbook protection, however during the course of our discussions and work he created some code that checks for the existance of an add-in if not install it, so check out the attachments and gleam the articles of code you need from them!

Regards,
SImon