10 users need to be able to run the same macro in excel

Question

i have a server where resides an excel macro

there will be about 10 users on 10 different workstations that will need to run that macro on their own worksheets.

how do i run the macro from a centralized location by multiple users who want to run it on their own worksheets?

the reason i need it centralized is because the macro will need to be updated constantly, and we cannot have it reside locally because then there would be 10 different instances of it

as one of the answers suggests below, perhaps an add-in is the way to go? if i do create an add-in, how would it know that there is a macro update? would i have to distribute the add-in every single time i update the macro?

Answer 1

I would consider just having each user's workbook contain the macro you need them to run, then each user can call it locally on their machine.

Otherwise just create one shared workbook, and have all the users use that one workbook.

It really depends on the situation the workbook will be used in, and what your trying to accomplish. If you could provide more detail, I could help you more specifically.

Answer 2

If the users have their Workbook open and the Server Workbook open, then the Macro would be available to them in the View->Macros dialog after selecting "All Open Workbooks" in the "Open Macros In" drop-down.

However, I'm thinking that there are three potential issues:

1- If the two workbooks are open in two different instances of Excel, then each workbook won't show the others macros.

2- The macro in the Server Workbook is going to need to be smart enough to operate on the other workbook. Most likely it would need to prompt the user for the correct workbook and or worksheets.

3- You might get a security warning on the remote macro execution. (not sure about this one)

I would probably create an Add-In that encapsulated my macro and install it on the remote user's machines so that they can click a button to execute it locally.

Update based on edit
Your Add-In should be able to pull new code remotely to update the macro it executes. Something like this, for example. This may be an overkill solution though.

Answer 3

If your problem is to make sure that people use an up-to-date macro, you may want to look into VSTO. One of the benefits is that you can use ClickOnce for deployment, which among others allows you to publish the code in one place, and have the user's code automatically download and update in case of changes.