CODEWITHSUNDEEP
vbaexcel
Gutti
Gutti

Reputation: 657

How to call function from another specific workbook in VBA?

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

Answers (4)

Del
Del

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

Jack
Jack

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

RIck_R
RIck_R

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

Brian Camire
Brian Camire

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:

  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.

Upvotes: 38

Related Questions