Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



How to call subroutine from 1 module from another module

Posted by Yu-Kuan on August 01, 2000 8:59 PM
Hi,


How do I call VB subroutines and functions of 1 module from another module in the same worksheet/workbook? Is it possible to call subroutines and functions from different workbooks?

also, is there a way to name the modules so that they're easier to keep track of?

Thanks a lot.

Yu-Kuan


Check out our Excel VBA Resources

Re: How to call subroutine from 1 module from another module

Posted by Ivan Moala on August 03, 0100 1:17 AM

Just to add to Celias advice the other workbook
doesn't neccessarily have to be open BUT you need
to explicitly name where the macro file resides
eg "D:\MyTest\Filename.xls!macroname"
The unfortunate drawback to this is it WILL open
the file.


Ivan


Re: How to call subroutine from 1 module from another module

Posted by Celia on August 02, 0100 6:55 AM


Yu-Kuan
1.
To call a macro or function that is in the same workbook (it need not be in the same module) just type the name of the macro/function and any arguments it requires on one line of the calling macro.
Another way is to prefix the macro/function called with the word Call. This is not necessary but helps to make the code easier to read.

2.
To call a macro that is in a different workbook (the other workbook has to be open I think):-
Run macro:="filename.xls!macroname"
Or
Application.Run "filename.xls!macroname"
If the file name contains spaces, the file name must be enclosed in single quotes :-
Application.Run "'file name.xls'!macroname"

To run a function in another workbook :-
ReturnValue = Application.Run("filename.xls!functionname")
If it needs arguments :-
ReturnValue = Application.Run("filename.xls!functionname",Arg1value,Arg2value)

3.
To re-name a module, select the module name in the Projects window, open the Properties window and change the name property to your new name.

Celia





This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.