There's no such thing as a stupid question, but they're the easiest to answer.


Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Randomizing Sub Running Order


ersin's Avatar
ersin ersin is offline
Member with 2 posts.
Join Date: Jun 2012
Experience: Beginner
28-Jun-2012, 07:42 AM #1
Solved: Randomizing Sub Running Order
Is it possible randomizing subs so that it doesn't always run in the same order (currently it will run Macro1 then Macro2 then Macro3. I would like to run Macro2, Macro1, Macro3...or in any other random combination)?

Sub CallAll()
Call Macro1
Call Macro2
Call Macro3
End Sub

Sub Macro1()
MsgBox "Hello A"
End Sub

Sub Macro2()
MsgBox "Hello B"
End Sub

Sub Macro3()
MsgBox "Hello C"
End Sub

Thanks in advance,
gathrawnca's Avatar
gathrawnca gathrawnca is offline
Member with 26 posts.
Join Date: Jun 2012
Experience: Intermediate
28-Jun-2012, 08:16 AM #2
Hey Ersin,
The following code will run do what you ask. It is also expandable so if you want to go to 4 macros, just change MaxNumberOfMacros to 4 at the top of the code (also would need to change iterations to 4). Also it allows you to run less then all macro, by setting Iterations to however many macros you want to run (For example, you have 3 macros and you want to run 2 random ones). It prevents the same macro from being run twice. If you want to use more meaningful macro names, you have to replace the for loop at the beginning with a series of MacroList.Add statements and just add strings (and change the MacroToRun string to not add "Macro" at the beginning).

Sub Random()
Dim MacroList As Collection
Set MacroList = New Collection
Dim MaxNumberOfMacros As Integer
Dim Iterations As Integer
Dim i As Integer
MaxNumberOfMacros = 3
Iterations = 3
For i = 1 To MaxNumberOfMacros
MacroList.Add (i)
Next i
Call NextMacro(MacroList, Iterations)
End Sub
Sub NextMacro(MacroList As Collection, Iterations As Integer)
Dim MacroNumber As Integer
Dim MacroToRun As String
MacroNumber = Int(MacroList.Count * Rnd() + 1)
MacroToRun = "Macro" & MacroList.Item(MacroNumber)
Application.Run (MacroToRun)
MacroList.Remove (MacroNumber)
Iterations = Iterations - 1
If Iterations > 0 And MacroList.Count() > 0 Then
Call NextMacro(MacroList, Iterations)
End If
End Sub

Last edited by gathrawnca; 28-Jun-2012 at 08:39 AM..
ersin's Avatar
ersin ersin is offline
Member with 2 posts.
Join Date: Jun 2012
Experience: Beginner
28-Jun-2012, 08:36 AM #3
Thank you; this is great!
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

calling subs, ordering, random, randomizing, subs

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools

You Are Using: Server ID
Trusted Website Back to the Top ↑