Microsoft KB Archive/106008

Knowledge Base

Open Subroutines Run Before Referenced Subroutines

Article ID: 106008

Article Last Modified on 10/11/2006

---

APPLIES TO

• Microsoft Excel 97 Standard Edition
• Microsoft Excel 98 for Macintosh

---

This article was previously published under Q106008

SUMMARY

In Microsoft Excel, if you call a subroutine (for example, TestSub) from another Visual Basic subroutine, Microsoft Excel will search for the subroutine in the current workbook and then it will search any libraries and/or workbooks to which the current workbook has references (these references will be searched in top-to-bottom order as they appear in the References dialog box).

Microsoft Excel will run the subroutine from the first location where it is found. This is by design.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Steps to Reproduce Behavior

1. In a new workbook, insert a new Visual Basic module (Module1).

2. In the new module, type the following subroutine:

          Sub TestSub()
              MsgBox "TestSub in ALPHA.XLS."
          End Sub
                           

3. If you are using Microsoft Excel 97 or Microsoft Excel 98, on the Tools menu, click VBAProject Properties. Change the project name to "VBAProjectAlpha". Click OK. Then, save the workbook as ALPHA.XLS.

   -or-

   If you are using an earlier version of Microsoft Excel, save the workbook as ALPHA.XLS.
4. Repeat steps 1-3 two more times, but use the messages and workbook names BRAVO.XLS and CHARLIE.XLS, respectively. When you finish, you should have three workbooks (ALPHA.XLS, BRAVO.XLS, and CHARLIE.XLS), each of which contains a Module1.
5. Quit and restart Microsoft Excel.
6. Open ONLY the ALPHA.XLS workbook.

7. In Module1, add the following subroutine:

          Sub Starter()
              TestSub
          End Sub
                           

8. On the Tools menu, click References.
9. In the References dialog box, click Browse.
10. In the Browse dialog box, select the BRAVO.XLS file in the File Name list and click OK.
11. In the References dialog box, click Browse.
12. In the Browse dialog box, select the CHARLIE.XLS file in the File Name list and click OK.
    
    The Available References list box should now contain BRAVO.XLS and CHARLIE.XLS at the bottom of the list.
13. Click OK to exit the References dialog box.
14. In Module1, run the Starter subroutine.
    
    A dialog box appears with the message "TestSub in ALPHA.XLS."
15. Delete the TestSub subroutine from Module1.
16. Run the Starter subroutine.
    
    A message box appears with the message "TestSub in BRAVO.XLS."

Microsoft Excel allows you to create references between workbooks and other workbooks or libraries. If these workbook references do not exist, a subroutine from a closed workbook cannot be called. To run a subroutine located in a closed workbook, you can create a reference between workbooks; this allows you to run the subroutine by calling the subroutine's name.

For example, if you follow the steps in the "Steps to Reproduce Behavior Section" above, you create references to both BRAVO.XLS and CHARLIE.XLS. The first time the Starter subroutine is run (step 14), Microsoft Excel finds the TestSub subroutine in ALPHA.XLS and runs it. The second time the Starter subroutine is run (step 16), Microsoft Excel does not find the TestSub subroutine in ALPHA.XLS (because it has been deleted). It then checks the referenced libraries and modules for the subroutine. The subroutine is first found in BRAVO.XLS, and so Microsoft Excel runs the TestSub subroutine located there.

For more information on creating references and running subroutines located in other workbooks, see the "Visual Basic User's Guide," Chapter 4.

REFERENCES

For additional information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

163435 VBA: Programming Resources for Visual Basic for Applications

Additional query words: 5.00a 5.00c 7.00a XL98 XL97 XL7 XL5 XL

Keywords: kbdtacode kbinfo kbprogramming KB106008

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.