Running Multiple Macros 4

[WaterSprite]

Is there a way to run multiple macros without typing out each Macro line by line?

Something like Macro34:Macro57

Or maybe a version of the Application.run

 

[SkipVought]

Hi,

for i = 34 to 57
  run "Macro" & i
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MakeItSo]

Macro 34 through 57?
Damn! I always give my macros meaningful names. Am I quaint?


If your macros are numbered through like this, then a simple For loop should do the trick:

For i=34 to 57
   Application.run("Macro" & i)
Next i

I would however also strongly recommend re-organising your routines for the long run.
Perhaps compile them all in a dll might be useful? Depends on what you are actually doing though.

Cheers,
MiS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

Dang Skip!
Beat me to it!


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[fumei]

But more eloquent.

"Am I quaint?"

Indeed you are, although I completely agree. Numbered procedures is a very bad practice, IMO.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[SkipVought]

I too hate numbered procedures as one has absolutely no idea what each might be.

Like variables, procedures ought to be somewhat descriptive, in order to give some hint of purpose. It is part of documenting a process.

Let's hope that each of the 34 to 57 do something different, rather than each being a near reflection of the former or latter.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[strongm]

Just for fun, how about the following? It will run all the macros* in a nominated module in the order in which they appear in that module. Depending on your version of Office and/or your security settings you may need to grant permission for macros to access the VBA project object model:

[blue]Public Const vbext_pk_Proc = 0

[green]' Excel version[/green]
Private Sub RunAllMacrosInModule(strModuleName)
    Dim WorkMacro As String
    Dim TotalLinesRead As Long
    
    With ActiveWorkbook.VBProject.VBComponents(strModuleName).CodeModule
        TotalLinesRead = 1
        Do Until TotalLinesRead >= .CountOfLines
            WorkMacro = .ProcOfLine(TotalLinesRead, vbext_pk_Proc)
            If WorkMacro <> "" Then
                Application.Run .Name & "." & WorkMacro
                TotalLinesRead = TotalLinesRead + .ProcCountLines(WorkMacro, vbext_pk_Proc)
            Else
                TotalLinesRead = TotalLinesRead + 1
            End If
        Loop
    End With
    
End Sub[/blue]

* As written this won't run macros that expect arguments, although this could be fairly easily added

 

[fumei]

Cute.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[SkipVought]

Au contrair!

Ought to be...

Public Const p = 0

' Excel version
Private Sub [b]Macro178953[/b](m)
    Dim w As String
    Dim t As Long
    
    With ActiveWorkbook.VBProject.VBComponents(m).CodeModule
        t = 1
        Do Until t >= .CountOfLines
            w = .ProcOfLine(t, p)
            If w <> "" Then
                Application.Run .Name & "." & w
                t = t + .ProcCountLines(w, p)
            Else
                t = t + 1
            End If
        Loop
    End With
    
End Sub

in keeping with the OP.

Lets not clarify things so close to the weekend!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[strongm]

Heh!

 

[fumei]

You guys are so funny.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[DougAJ4]

How about:

Sub RunList()
Dim MacroList As Variant, i As Long, NumMacros As Long

MacroList = Range("macrolist").Value
NumMacros = UBound(MacroList)

For i = 1 To NumMacros
Application.Run MacroList(i, 1)
Next i
End Sub


Then make a list of the macros to run on a worksheet and call it macrolist.

Doug Jenkins

http://newtonexcelbach.wordpress.com/

 

[Gavona]

Hey Doug, that seems the most versatile approach!

I picked up/adapted a routine to list all the macros in my workbook (as part of my documentation routine). In order to make it work I had to first set reference to Microsoft Visual Basic for Applications Extensibility 5.3. I think this will be required or Strongm's solution (though I must admit I don't fully understand such things). Here is the code I use to do that.

Sub ListOfMacros()
    On Error Resume Next '< error = reference already set
     'set reference to Microsoft Visual Basic for Applications
     'Extensibility 5.3
    ThisWorkbook.VBProject.References.AddFromGuid _
    "{0002E157-0000-0000-C000-000000000046}", 5, 3
     'now get the list of macros
    Call GetTheList
End Sub

Gavin

 

[strongm]

>I think this will be required [f]or Strongm's

Nope, you don't need any references for my code to work.

 

[DougAJ4]

Gavona - I don't have a reference set to Microsoft Visual Basic for Applications Extensibility 5.3, I don't even have that on my list of available references. Also I can't see anything in the code that should require a special reference.

Are you working in Excel VBA?

Doug Jenkins

http://newtonexcelbach.wordpress.com/

 

[strongm]

Doug, Gavona was referring to my code, not to yours.