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

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

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