Reputation: 657
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
Upvotes: 32
Views: 95552
Reputation: 31
Here's code to run a procedure that's not in the current workbook:
Application.Run "'ManageBanks.xlsm'!UpdateBankData"
This runs the procedure "UpdateBankData" in the workbook "Managebanks.xlsm"
Upvotes: 0
Reputation: 951
Note that if you want to call a method in a module of a different workbook without having the annoying behavior of a DLL-style link as mentioned in the other answers, you can use code like the below:
Dim otherWorkBook As Workbook
Set otherWorkBook = Workbooks.Open("myWorkbook.xlsm")
Call otherWorkBook.Sheets("SomeSheet").someMethod(arg1, arg2...)
Where someMethod() is a method which calls the actual method you're interested in within the module of the other sheet
Upvotes: 2
Reputation: 121
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
Upvotes: 2
Reputation: 4845
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:
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.
Upvotes: 38