none
Excel keeps remapping macro calls from local modules to Add-in RRS feed

  • Question

  • Hi!

    We have serious problems as Excels keeps changing macro calls from buttons in the worksheets.

    The situation (in simplified manner) is the following:

    There is company A which uses an Add-In with a module modAddIn containing

    • public sub MySub

    There is company B which uses workbooks containing the module modLocal with

    • public sub MySub

    The workbooks need to be exchanged between both departments. The problem is: As soon as one of these workbooks is opened in A, Excel automatically remaps all calls to MySub from modLocal to modAddIn, so that the workbooks no longer work without the AddIn.

    This seems to be a bug in Excel or at least a design which urgently needs to be changed:

    1. The modules are named differently. Excel ignores the scope of the function call as stored in the "OnAction" property of the form control button.
    2. Everywhere else, even in VBA, local definitions always overwrite global ones and it makes no sense in doing it vice-versa.
    3. Excel even seems to ignore an enabled worksheet protection.

    On VBA level, I suggested to either

    • rename one of the two MySub or
    • change the macro calls in all the workbooks

    However, due to several reasons, this is not possible. So I'm a bit stuck. Any idea what I could do else? Maybe there is an option in Excel, VBE or the registry we missed where one can disable this behaviour?


    • Edited by Flipes Monday, November 9, 2015 8:07 AM typo in subject
    Monday, November 9, 2015 8:06 AM

Answers

  • The rabbit hole is much deeper.

    When you have an AddInA with a ribbon-button assigned to "MySub" in a module in the AddInA and

    you have an AddInB with a ribbon-button assigned to "MySub" in a module in the AddInB and

    you load all this files, any button calls the same MySub in the last (or was it the first?) loaded AddIn!

    The only way out of that is to rename the subs inside the AddIns with GUIDs to be 100% sure the name is unique.

    Here is a sample AddIn:
    https://dl.dropboxusercontent.com/u/35239054/SendALink.zip

    So when it is not possible to rename the subs inside the AddIns, you're lost.

    Andreas.

    Monday, November 9, 2015 11:30 AM