excel - Is it possible to prevent code available in personal.xlsb?

Question

I've been self-teaching myself VBA for a couple of years now (working on it on and off to make my work life easier). I'm usually able to find answers to my questions on this forum, but not this one.

I have a lot of 'example' code in my own personal.xlsb. For instance I have a public sub with a modified messagebox with standard caption etc I always like to make visible, referred to as PublicSub_MyMsgBox

Often, the automation I create, will be used by my colleagues as well. I would create a specific workbook with specific buttons for a specific goal. I would copy the required code into a module in this shared workbook. The workbook would therefore have a module with the sub PublicSub_MyMsgBox as well. Another sub in this shared workbook will have a line to call PublicSub_MyMsgBox.

How can I be sure that when I test a code created in this shared workbook, it does not use the PublicSub_MyMsgBox from my own personal.xlsb, but actually only uses the code from the workbook? (to be able to verify that I have indeed copied all relevant code into the workbook for my colleagues to use as well).

Thank you very much for your help! Linda

question from:https://stackoverflow.com/questions/65859771/is-it-possible-to-prevent-code-available-in-personal-xlsb

Answer 1

No code from personal.xlsb will run even if a reference to pers. workbook exists! You can be sure that no code from personal.xlsb will be run, instead of the one inside the workbook where the call is made.

Even if you added a reference to your personal.xlsb, for identic macro names VBA will choose the one in the workbook where the code runs. To check that, please, try the next scenario:

1. Create in your personal workbook the next macro:

Public Sub MyMsgBox()
   MsgBox "Hello from Personal workbook!"
End Sub

2. Then create the next macro in a standard module of your new workbook:

Sub MyMsgBox()
   MsgBox "Hello from This workbook!"
End Sub

3. Create a simple checking Sub, of course, in the new workbook, add a reference to your personal.xlsb workbook and run it:

Sub testMyMsgBox()
   MyMsgBox
End Sub

4. It will run the Sub in your workbook. Now, comment the Sub from your workbook and run testMyMsgBox again. It will ran the procedure from personal.xlsb...