+ Reply to Thread
Results 1 to 5 of 5

How to call on other macros

Hybrid View

  1. #1
    Registered User
    Join Date
    03-29-2007
    Posts
    2

    How to call on other macros

    I'm a freshman in college. We were assigned and excel project. We're recording macros and using VBA to edit them. All I need to know is how to make a sort of master macro to call on all the ones I have made at once. Help is much appreciated.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259
    Hello Mac3188,

    Create a Sub procedure in a Standard VBA Module and list the macro you have already created in it. Calling this one macro will then execute all the macro inside of it one by one.

    Example:
    Sub MasterMacro()
      Call Macro1
      Call Macro2
    
    End Sub
    Be sure to include any arguments your macros use as well.

    Sincerely,
    Leith Ross

  3. #3
    Registered User
    Join Date
    03-29-2007
    Posts
    2
    Thank you very much.

  4. #4
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    There are several options, with no "best answer".

    One option is simply to list the names of each subroutine.
    Sub masterMacro()
    
        Macro1
        Macro2
        Macro3
    
    End Sub
    If any of the macros require that you pass arguments, simply list the arguments:
    Sub masterMacro()
    
        Macro1 arg1, arg2, arg3
        Macro2
        Macro3
    
    End Sub
    Another variation on this uses the VBA key word "Call":
    Sub masterMacro()
    
        Call Macro1(arg1, arg2, arg3)
        Call Macro2
        Call Macro3
    
    End Sub
    All of that is fine as long as all macros (plus the "master") are in the same workbook, and the names of the "sub" macros never change. If either of these rules are violated, then you probably want to use the Excel "Run" method instead of the VB Call key word.

    Sub masterMacro()
    
        Run "Book1.xls!Macro1", arg1, arg2, arg3
        Run "Book2.xls!Macro2"
        Run "Book3.xls!Macro3"
    
    End Sub
    Note that since you are passing the names of the workbooks and the names of the macros as strings, you can replace the "fixed" strings with variables.

    Sub masterMacro()
    Dim wb1 As String, wb2 As String, wb3 As String
    Dim mc1 As String, mc2 As String, mc3 As String
    
        wb1 = "Book1.xls"
        mc1 = "Macro1"
        
        Run wb1 & mc1, arg1, arg2, arg3
        Run "Book2.xls!Macro2"
        Run "Book3.xls!Macro3"
    
    End Sub

  5. #5
    Registered User
    Join Date
    05-02-2006
    Posts
    95
    Is it possible to call a Private Sub also? How?

    Thanks,
    Ricky

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1