Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    78

    Question Unanswered: Calling external macros

    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.Run wb.Name + "!MacroName" -- but I want to just call a Sub in that macro.

    Thanks.

    ~BS

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    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.Run 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.Run "'abc.xls'!SubName"
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Dec 2004
    Posts
    78

    Question

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •