Calling external macros

bhavesh78 · #1 (**permalink**) 03-01-05, 12:23

I have 2 files open-- abc.xls and xyz.xls.
How can I call a Sub (say Sub Test in module1) from abc.xls to a macro in xyz.xls

I can call macro using-- Application****n wb.Name + "!MacroName" -- but I want to just call a Sub in that macro.

Thanks.

~BS

MikeTheBike · #2 (**permalink**) 03-02-05, 08:37

Hi

You can do this if you set a reference (Tools->Referances) to xyz.xls form abc.xls (you my have to change the project name Tools->VBAProject Properties. Don't forget to save this).

However I think this will open xyz.xls every time you open abc.xls whether you want to run the code or not !!

Alternatively you could put the code into a separate Workbook and save this as an addin (.xla) file and set a reference to this form both abc.xls and xyz.xls. This will allow both workbook to run the code. Again you will need to change the project name for the xla file so you can select it in the references list. Once this is done the xla file will load every time you open any workbook that has a reference to it (but not if you move the xla file - you will need to reconnect the reference - unless it is in the same directory as the as the workbook).

This is all based on Excel 97 but it all seem to work on 2K.

Hope this is of some use

MTB

shades · #3 (**permalink**) 03-02-05, 13:29

Quote:

Originally Posted by bhavesh78

I have 2 files open-- abc.xls and xyz.xls.
How can I call a Sub (say Sub Test in module1) from abc.xls to a macro in xyz.xls

I can call macro using-- Application****n wb.Name + "!MacroName" -- but I want to just call a Sub in that macro.

You are almost there, if you don't want to establish a reference (as explained in post above).

Code:

Application****n "'abc.xls'!SubName"

bhavesh78 · #4 (**permalink**) 03-02-05, 14:42

Quote:

Originally Posted by MikeTheBike

Alternatively you could put the code into a separate Workbook and save this as an addin (.xla) file and set a reference to this form both abc.xls and xyz.xls.
MTB

I cannot do the above since in this case I cannot use an addin. Currently I am using the following for xyz.xls

Code:

Private Sub Workbook_Activate()
    Application.OnDoubleClick = ""   'Our templates run a macro on doubleclick by default- so am disabling double click for this file only
End Sub

Private Sub Workbook_Deactivate()
    Application.OnDoubleClick = "DblClickInABC"     'Macro from other file/Enabling double click again on deactivate
End Sub

I have added the file abc.xls that has the macro "DblClickInABC" in References of xyz.xls for above code to work (calling macro from file abc.xls to xyz.xls)
Here problem is that, if I close xyz.xls and double click on abc.xls, it opens the file xyz.xls even if it was not called anywhere.. is it because of References or am I missing something here?

~BS

Internet Services

Web Development

Digital Marketing

Consumer Tech