XL: Open Subroutines Run Before Referenced Subroutines

ID: Q106008

The information in this article applies to:

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/support/supportnet/refguide/

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: 

   ARTICLE-ID: Q163435
   TITLE     : VBA: Programming Resources for Visual Basic for
               Applications
Additional query words: 5.00 5.00a 5.00c 7.00 7.00a XL98 XL97 XL7 XL5 
Keywords          : kbprg kbdta kbdtacode PgmOthr KbVBA 
Version           : WINDOWS:5.0,7.0,97; MACINTOSH:5.0,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbinfo

Last Reviewed: May 17, 1999