none
Updating the same macros in several workbooks? RRS feed

  • Question

  • Hi All

    I have several workbooks that have the same macros in them. When I want to make an improvement to a macro , I find that I need to apply the change to all the workbooks individually - which can be quite time consuming as I have about 20 or so workbooks..

    Is there any way that macros can be held centrally and then updated to the all workbooks - without having to manually update each workbook individually?

    Any advice would be much appreciated.

    thank you.  

    Peter

    Monday, February 15, 2016 11:48 AM

Answers

  • I have never written an Add-In so if you give some general information or advice on how to start with this.

    As simple as possible:

    - Make a new file
    - Add a regular module
    - Copy this sub into that module

    Sub Hello_World()
      MsgBox "Hello_World"
    End Sub

    - Right click the VBAProject in the Project Explorer and choose "Properties of"
    - Change the project name to "MyFirstAddIn"
    - Save the file as MyFirstAddIn.XLAM in a folder that is accessible by any user
    - Close and reopen Excel (Just to "cleanup" the VBA Project Explorer and make the following easier to understand)
    - Open the MyFirstAddIn.XLAM
    - Make a new file (or open an existing one)
    - Open the VBA editor
    - Set a reference to your AddIn (2 possible ways):

    a) Within the Project Explorer left click the MyFirstAddIn project and drag & drop it into your project
    or
    b) Click Tools\References and check MyFirstAddIn in the list box

    - Add a regular module
    - Copy this sub into that module

    Sub Main()
      Hello_World
    End Sub


    - Save the file as Test.xlsm in a folder that is accessible by any user
    - Close and reopen Excel (Just to "cleanup" the VBA Project Explorer and make the following easier to understand)
    - Open the Test.xlsm and execute sub Main

    That's it. Anytime when Test.xlsm is opened the AddIn is loaded automatically.

    Hint: When you publish the MyFirstAddIn.XLAM, set the ReadOnly attribute within the Windows Explorer.
    Then you can publish a new AddIn (copy a newer AddIn file) even if a user has the file in use.

    Andreas.


    • Edited by Andreas Killer Tuesday, February 16, 2016 11:06 AM
    • Marked as answer by py1 Tuesday, February 16, 2016 1:54 PM
    Tuesday, February 16, 2016 11:05 AM
  • Hi Peter,

    here's are the sample files, created from the description above.

    https://dl.dropboxusercontent.com/u/35239054/Samples/42beec78-316f-441d-a9c9-da9c7e4e22f4.zip

    Forgot to note: The main files (Test.xlsm) saves the path to the AddIn.

    Means you can move the main files to a different folder, no problem.

    But when you move the AddIn file, you have to change the reference in all main files!

    Except when the AddIn file moves with the main files, means you can have them in the same folder and move both files into a different folder, or move the folder itself.

    It's the same behavior as if you refer to an other file with a formula.

    You can get around this if you install the AddIn in Excel, then it doesn't matter where you place the AddIn.

    Andreas.



    • Edited by Andreas Killer Tuesday, February 16, 2016 2:50 PM
    • Marked as answer by py1 Tuesday, February 16, 2016 3:19 PM
    • Unmarked as answer by py1 Tuesday, February 16, 2016 3:21 PM
    • Marked as answer by py1 Tuesday, February 16, 2016 3:26 PM
    Tuesday, February 16, 2016 2:41 PM
  • Andreas

    As always - than you very much for providing such  a complete answer.

    I really appreciate you taking time to provide such a detailed response.

    many thanks!

    kind regards,

    Peter

    • Marked as answer by py1 Tuesday, February 16, 2016 3:26 PM
    Tuesday, February 16, 2016 3:26 PM
  • Reading other VBA literature they all say that you should 'link' the add-in to the test file using the Add-in Manager (File-->Options-->Add-Ins)

    There is no need for that in your case.

    When you install an AddIn via File\Options\AddIns the AddIn is loaded by Excel at startup. This is the usual way for AddIns that support an UI (e.g. buttons in the ribbon).

    Let us assume you do that only. Scenario: Excel is closed, you double click your xlsm File, this happens:

    Excel starts up, Excel loads the AddIn, Excel loads your file.
    But when you try to call the Sub Hello_World (that is located in the AddIn) from your file, you'll get a RTE "Sub or Function not definied".

    That is the reason why you must set a reference within the VBA editor to the AddIn.
    You can call Sub/Functions within the AddIn from VBA only when you set a reference to the AddIn.

    (Technical Note:
      There are "dirty" ways to call Sub/Functions without a references, but that is bad programming behavior and can lead to many problems.
      A reference is clear and a safe way to make sure that the correct Sub/Functions are called.
    )

    Andreas.

    • Marked as answer by py1 Thursday, March 3, 2016 1:29 PM
    Wednesday, March 2, 2016 2:08 PM
  • I have created a simple add in called (PYAddin1) which just sends a message via the message box. What confuses me know is - if I do not want to call this via VBA code how can I get to run.

    Then your AddIn must create a user interface in the ribbon (tab, group, button) that calls the sub.

    Have a look here:
    http://www.rondebruin.nl/win/section2.htm

    Andreas.

    • Marked as answer by py1 Thursday, March 10, 2016 8:36 AM
    • Unmarked as answer by py1 Thursday, March 10, 2016 8:36 AM
    • Marked as answer by py1 Thursday, March 10, 2016 8:37 AM
    Thursday, March 10, 2016 8:03 AM

All replies

  • Is there any way that macros can be held centrally and then updated to the all workbooks - without having to manually update each workbook individually?

    Yes, write an AddIn and set the reference in the workbook to that AddIn.

    (That is the same when you want to use the Solver-AddIn in VBA).

    Andreas.

    Monday, February 15, 2016 2:56 PM
  • Andreas

    Thanks for this.

    Can you give me some more information or point me in the right direction.

    I have never written an Add-In so if you give some general information or advice on how to start with this.

    many thanks,

    Monday, February 15, 2016 4:00 PM
  • I would try to store the Macro on a main workbook and have 2 workbooks open (The main one and the one I need to run the macro on). I also have not made an addin before so your reply will be of intrest to me also.
    Monday, February 15, 2016 4:19 PM
  • As already suggested an addin is certainly one way. Another way, but only if the workbooks are for your use only, would be to put the macros in your 'Personal' workbook. Like an 'installed' addin it will automatically load when Excel starts.

    If your worbooks are for distribution, whether or not  also distributing and maintaining an addin would be the easiest approach would depend on other factors.

    Monday, February 15, 2016 4:37 PM
    Moderator
  • I have never written an Add-In so if you give some general information or advice on how to start with this.

    As simple as possible:

    - Make a new file
    - Add a regular module
    - Copy this sub into that module

    Sub Hello_World()
      MsgBox "Hello_World"
    End Sub

    - Right click the VBAProject in the Project Explorer and choose "Properties of"
    - Change the project name to "MyFirstAddIn"
    - Save the file as MyFirstAddIn.XLAM in a folder that is accessible by any user
    - Close and reopen Excel (Just to "cleanup" the VBA Project Explorer and make the following easier to understand)
    - Open the MyFirstAddIn.XLAM
    - Make a new file (or open an existing one)
    - Open the VBA editor
    - Set a reference to your AddIn (2 possible ways):

    a) Within the Project Explorer left click the MyFirstAddIn project and drag & drop it into your project
    or
    b) Click Tools\References and check MyFirstAddIn in the list box

    - Add a regular module
    - Copy this sub into that module

    Sub Main()
      Hello_World
    End Sub


    - Save the file as Test.xlsm in a folder that is accessible by any user
    - Close and reopen Excel (Just to "cleanup" the VBA Project Explorer and make the following easier to understand)
    - Open the Test.xlsm and execute sub Main

    That's it. Anytime when Test.xlsm is opened the AddIn is loaded automatically.

    Hint: When you publish the MyFirstAddIn.XLAM, set the ReadOnly attribute within the Windows Explorer.
    Then you can publish a new AddIn (copy a newer AddIn file) even if a user has the file in use.

    Andreas.


    • Edited by Andreas Killer Tuesday, February 16, 2016 11:06 AM
    • Marked as answer by py1 Tuesday, February 16, 2016 1:54 PM
    Tuesday, February 16, 2016 11:05 AM
  • Andreas

    Thanks for this - it look promising.

    I have tried it quickly and could not get it to work - but I suspect it was me doing something stupid!!

    Will spend more time later working trhough it.

    I appreciate your help.

    kind regards,

    Peter

    Tuesday, February 16, 2016 1:56 PM
  • Hi Peter,

    here's are the sample files, created from the description above.

    https://dl.dropboxusercontent.com/u/35239054/Samples/42beec78-316f-441d-a9c9-da9c7e4e22f4.zip

    Forgot to note: The main files (Test.xlsm) saves the path to the AddIn.

    Means you can move the main files to a different folder, no problem.

    But when you move the AddIn file, you have to change the reference in all main files!

    Except when the AddIn file moves with the main files, means you can have them in the same folder and move both files into a different folder, or move the folder itself.

    It's the same behavior as if you refer to an other file with a formula.

    You can get around this if you install the AddIn in Excel, then it doesn't matter where you place the AddIn.

    Andreas.



    • Edited by Andreas Killer Tuesday, February 16, 2016 2:50 PM
    • Marked as answer by py1 Tuesday, February 16, 2016 3:19 PM
    • Unmarked as answer by py1 Tuesday, February 16, 2016 3:21 PM
    • Marked as answer by py1 Tuesday, February 16, 2016 3:26 PM
    Tuesday, February 16, 2016 2:41 PM
  • Andreas

    As always - than you very much for providing such  a complete answer.

    I really appreciate you taking time to provide such a detailed response.

    many thanks!

    kind regards,

    Peter

    • Marked as answer by py1 Tuesday, February 16, 2016 3:26 PM
    Tuesday, February 16, 2016 3:26 PM
  • Hi Andreas

    This might be (another!) stupid question from me.

    I followed this and managed to create an add in.

    However, I do not seem to be able to 'execute' the add in from my spreadsheet. It looks like the add in is installed ok - but not sure how to kick it off.

    What am I missing?

    Thansk

    Peter

    Friday, February 26, 2016 4:18 PM
  • What am I missing?

    Hi Peter,

    I don't know.

    Look into my profile for my mail address and send me the main file and the AddIn, I'll take a look.

    Andreas.

    Sunday, February 28, 2016 1:58 PM
  • Andreas

    Sorry for my delay in responding.

    I think I am getting  closer to getting this to work. Leave it with me and if I get stuck I will contact you again - if that is ok!

    many thanks

    Peter

    Tuesday, March 1, 2016 1:11 PM
  • Hi Andreas

    It's me again!

    I have take the files from the DropBox and I can see that the add in is 'linked' to the test file via the reference command.

    Reading other VBA literature they all say that you should 'link' the add-in to the test file using the Add-in Manager (File-->Options-->Add-Ins) and when you get to Add In Dialogue box check the box which has the add in name.

    In your test file you do not this? Is there a reason why?

    When I set up my own test files - and use the Add in Manager , I can tick the add in ok; however when I go back to may test file and try to run the macro it does not seem to find it!!

    totally confused now!

    Shall I send you my files?

    thanks

    Peter

    Tuesday, March 1, 2016 4:34 PM
  • Reading other VBA literature they all say that you should 'link' the add-in to the test file using the Add-in Manager (File-->Options-->Add-Ins)

    There is no need for that in your case.

    When you install an AddIn via File\Options\AddIns the AddIn is loaded by Excel at startup. This is the usual way for AddIns that support an UI (e.g. buttons in the ribbon).

    Let us assume you do that only. Scenario: Excel is closed, you double click your xlsm File, this happens:

    Excel starts up, Excel loads the AddIn, Excel loads your file.
    But when you try to call the Sub Hello_World (that is located in the AddIn) from your file, you'll get a RTE "Sub or Function not definied".

    That is the reason why you must set a reference within the VBA editor to the AddIn.
    You can call Sub/Functions within the AddIn from VBA only when you set a reference to the AddIn.

    (Technical Note:
      There are "dirty" ways to call Sub/Functions without a references, but that is bad programming behavior and can lead to many problems.
      A reference is clear and a safe way to make sure that the correct Sub/Functions are called.
    )

    Andreas.

    • Marked as answer by py1 Thursday, March 3, 2016 1:29 PM
    Wednesday, March 2, 2016 2:08 PM
  • Andreas

    Once again - thank you.

    I will try again and see how I progress.

    many thanks for your continued support.

    Peter

    Thursday, March 3, 2016 1:28 PM
  • Andreas

    Can I ask you a further question regarding your solution.

    You had the Test.xlsm and MyFirstAddin.xlam  a folder 'accessible' by the user. I am a bit confused on how this process would work. For example if I develop the solution on my PC and have the files saved like this: 

    Test file in C:\Peter\Mydocumnet\FolderA

    MyFirstAddin in C:\Peter\Mydocument\FolderB

    I can set the reference ok. that is fine.

    I want to be able to send the test file to 5 users and have them amend this as they require. the Addin file would be common to them all and would hold most of the macros.

    The only folder that they have in common is a SharePoint one - but if I place the add in in the SP folder I found that the performance in calling the SP is very slow.

    If I then email the 2 files to 5 different users what file structure should they use so that the references are maintained. I think if they place both the files in one folder on their PC the links would not be maintained?

     I also am not clear how future updates to the Add in could be distributed.

    Apologies if this outside your scope of interest.

    thank you.

    Monday, March 7, 2016 9:55 AM
  • For example if I develop the solution on my PC and have the files saved like this: 

    Test file in C:\Peter\Mydocumnet\FolderA

    MyFirstAddin in C:\Peter\Mydocument\FolderB

    I can set the reference ok. that is fine.

    I want to be able to send the test file to 5 users and have them amend this as they require. the Addin file would be common to them all and would hold most of the macros.


    I that case the users have to

    a) place the AddIn and the file in the same folder or

    b) install the AddIn as usual

    Andreas.

    Monday, March 7, 2016 3:18 PM
  • Hi Andreas,

    Going back to your answer above as to why I need to use the Tools/Reference command above in order for  VBA to execute the add in - I think I  understand that. however , I now have a different problem!

    I have created a simple add in called (PYAddin1) which just sends a message via the message box. What confuses me know is - if I do not want to call this via VBA code how can I get to run.

     I have a test file and have used  the Options-->Addin-->Excel Go  to ensure that the addin is loaded.

    I have not done the Tools /Reference command.

    I then close excel and re-opened it. My add in was loaded ok.

    But how do I execute it now?

    Again I feel I am missing something basic !

    many thanks

    Peter

    Wednesday, March 9, 2016 11:14 AM
  • I have created a simple add in called (PYAddin1) which just sends a message via the message box. What confuses me know is - if I do not want to call this via VBA code how can I get to run.

    Then your AddIn must create a user interface in the ribbon (tab, group, button) that calls the sub.

    Have a look here:
    http://www.rondebruin.nl/win/section2.htm

    Andreas.

    • Marked as answer by py1 Thursday, March 10, 2016 8:36 AM
    • Unmarked as answer by py1 Thursday, March 10, 2016 8:36 AM
    • Marked as answer by py1 Thursday, March 10, 2016 8:37 AM
    Thursday, March 10, 2016 8:03 AM
  • thanks again Andreas - another useful source of information.

    kind regards,

    Peter

    Thursday, March 10, 2016 8:37 AM