How to call function from another specific workbook in VBA?

Question

I would like to know if there is a way to call a VBA function or method from another specified workbook's module as it is possible for a specific worksheet without using the Application.Run

For the worksheet I can call for example :

ActiveSheet.MyTest()  

if MyTest is defined in the sheet module

But I would like to call a function which is defined in a module

I tried :

ActiveWorkbook.MyTestModule() ActiveWorkbook.VBProject.VBComponents("MyModule").MyTestModule(myArg) 

which don't work generating an error Object does not support this method

I could call

Application.Run(ActiveWorkbook.name & "!MyTestModule", myArg) 

But I am not sure of the error handling of the Application.Run and I would find cleaner to run directly the method

Answer 1

In the workbook you want to call from (I'll call this A), you could add a reference to the workbook that you want to call to (I'll call this B) as follows:

1. In workbook A, open the Microsoft Visual Basic for Applications window (for example, by pressing Alt+F11).
2. Select Tools, References.
3. In the References dialog that appears, choose Browse.
4. In the Add Reference dialog that appears, choose Microsoft Excel Files from the Files of type box, select the file that you want to call (B), and choose Open.
5. Choose OK to close the References dialog.

In file A, you should then be able to call public module-level functions in file B as if they were in file A. To resolve any naming conflicts, you can prefix calls by the "Project Name" for file B as specified in the General tab of the Project Properties dialog (accessible via the Properties command in the Microsoft Visual Basic for Applications Tools menu). For example, if the "Project Name" for file B was "VBAProjectB", you could call function F from file A using the syntax VBAProjectB.F.

Answer 2

By the way, this also works if you want to access a custom data type in another workbook:

' In workbook ABC, project name Library  Public Type Book_Data    Title As String    Pub_Date As Date    Pub_City As String End Type  ' In workbook DEF (after a ref to Library) Dim Book_Info As Library.Book_Data  Book_Info.Title = "War and Peace" Debug.Print Book_Info.Title