How to call subs multiple times

cobber1418 · Oct 20th 2019

Hi All
I need help to call a sub multiple times. I know how to call a sub a second time but I don't know how to jump the next subs to get back to where I was when I called the sub.

Thanks in advance, Cobber

**Kenneth Hobson** · Oct 20th 2019

Here are 2 loop methods.

Code

Sub Main()
  Dim i As Integer
  For i = 1 To 3
    'Application.Run "m"
    m
  Next i
  
  i = 0
  Do
    m
    i = i + 1
  Loop Until i = 3
End Sub


Sub m()
  MsgBox "m"
End Sub

Display More

cobber1418 · Oct 20th 2019

Hi Kenneth
Thanks for your quick reply.......looks a bit complicated to a beginner, but I'll have a crack at it, cheers.

cobber1418 · Oct 20th 2019

Hi Kenneth
Sorry, I can't figure it out.....see below what I'm trying to do.
When I get to sub C, I want to go back to sub A and when it finishes I want to jump forward to sub D without doing sub B again.

Sub A
code etc
call B
End sub

Sub B
code etc
call C
End sub

Sub C
call A 'this is where I want to repeat sub A and go to sub D without repeating sub B & C
End sub

Sub D
code etc
End sub

Hope this helps, cheers Cobber

cobber1418 · Oct 20th 2019

Hi Kenneth
This is how I tried to use your code.....but without success, it just keeps looping, where am I going wrong, cheers.

Code

Sub A()
'create extra sheets
Sheets.Add After:=Sheets(1)
Sheets(ActiveSheet.Name).Name = "Two" 'sheet 2
Sheets.Add After:=Sheets(2)
Sheets(ActiveSheet.Name).Name = "Three" 'sheet 3
Sheets(1).Select
Call B
End Sub


Sub B()
Range("A1").Value = "text"
Call C
End Sub


Sub C()
Sheets(2).Select
Range("A1").Value = "word"
Call D
End Sub


Sub D()                [COLOR=#FF0000]  'changed your main to D[/COLOR]
Sheets(3).Select


  Dim i As Integer
  For i = 1 To 3
    'Application.Run "m"
    'm
    B        [COLOR=#FF0000] 'changed your m to B[/COLOR]
  Next i
  
  i = 0
  Do
    'm
    B         [COLOR=#FF0000]'changed your m to B[/COLOR]
    i = i + 1
  Loop Until i = 3
Call E
End Sub


Sub E()
Sheets(1).Select
Range("A2").Value = "lastsub"


End Sub

Display More

**pike** · Oct 20th 2019

Hi,
The code returns to where is called the sub routine , so maybe a master to run the subs

Code

Option Explicit
Sub master_Order()
     AAA "SheetTwo", "SheetThree"
     BBB
     CCC
     AAA "SheetFour", "SheetFive"
     DDD
     EEE
End Sub
Sub AAA(strNaneOne As String, strNameTwo As String)
    Sheets.Add After:=Sheets(1)
    Sheets(ActiveSheet.Name).Name = strNaneOne    'sheet 2
    Sheets.Add After:=Sheets(2)
    Sheets(ActiveSheet.Name).Name = strNameTwo    'sheet 3
    Sheets(1).Select
End Sub


Sub BBB()
    Range("A1").Value = "text"
End Sub


Sub CCC()
    Sheets(2).Select
    Range("A1").Value = "word"
End Sub


Sub DDD()
    Sheets(3).Select
End Sub
Sub EEE()
    Sheets(1).Select
    Range("A2").Value = "lastsub"
End Sub

Display More

cobber1418 · Oct 20th 2019

Hi Pike
So the Master sub tells all the subs when to run...I just need to organise the order.

Thanks very much.

How to call subs multiple times

Participate now!

Share

Users Viewing This Thread