Call multiple Subs

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
A general question about Calls.

Calling a Sub from another Sub is quite common, how about a Sub that does nothing else but call all the subs to execute the entire job?

Will the subs run sequentially after one completes, or will there be the likley hood they will bump into each other causing all sorts of chaos...?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
It will execute one after another like this
Code:
Sub test()
   Call test1
   Call test2
   Call test3
End Sub

Sub test1()
     Range("a1").Value = "test1"
     MsgBox "test1"
     Range("a2").Value = "test1 finished"
End Sub

Sub test2()
     Range("b1").Value = "test2"
     MsgBox "test2"
     Range("b2").Value = "test2 finished"
End Sub

Sub test3()
     Range("c1").Value = "test3"
     MsgBox "test3"
     Range("c2").Value = "test3 finished"
End Sub
 

kmillen

Board Regular
Joined
Jun 8, 2007
Messages
115
I know of no documentation or reference that guarantees execution in the manner you described. Although it seems unlikely Excel would misbehave as in a poorly managed threaded environment, this situation can easily be overcome with functions. Use the return value of a function to indicate whether it is time or it is proper to call the next function.
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
Thanks J-
That is good news.
I added a visual test that helps me to see the results.... your test code was great to see this-
Code:
Sub Callsubs()
   Call test1
   Call test2
   Call test3
End Sub

Sub test1()
     Range("a1").Value = "test1"
     MsgBox "test1"
     Range("a2").Value = "test1 finished"
End Sub

Sub test2()
     Range("b1").Value = "test2"
     Range("a1:A14").Interior.Color = vbBlue
     MsgBox "test2"
     Range("b2").Value = "test2 finished"
End Sub

Sub test3()
     Range("c1").Value = "test3"
     Range("A6:A15").Interior.Color = vbYellow
     MsgBox "test3"
     Range("c2").Value = "test3 finished"

End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Good!

FYI

If you put "Exit Sub", then the code exit from the current Sub routine and goes straight to next sub routine.

If you put "End", it will completely ends at that line from entire sub routines.
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
Kmillien-
this situation can easily be overcome with functions. Use the return value of a function to indicate whether it is time or it is proper to call the next function.

I can have a general understanding of your approach- it would help me to see what an example of how you would handle it. I am still very green w/ the code world and especially UDFs.

Thanks,

Doug
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Doug

Function only returns value/object, can not change anything like Sub routine does.
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
Yes. If I understand what Kmillien is suggesting is creating a UDF that is a boolean and will pass the result to a sub.
But this is just me guessing.
I liked your solution- it has much promise. I have about 15 subs to run for my final results each day and after I trouble shoot them I want to combine them to run one after another. This keeps them separate in case there is a change the whole thing does not collapse.
One of the biggest challenges has been how much data I have to go through to process and find errors- I know it is not large compared to some big corp's, financial departments, but for me it is big and it takes a lot of computing power.
I am at about 20,000 rows and 28 columns of data.... so one thing that breaks, takes a few minutes to track down every time.....
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Doug,

Most of my codes, I used an array to speed up the process.
Process within a memory is much faster than accessing sheet/range objects frequently.

However if you have 20000 rows with 28 columns, a few minutes is not too bad to me. (could be speed up, but not sure)

If you find out the part that takes much time, we can see if we can make it faster or not.

BTW formatting Cells/Ranges takes time, that we can do nothing about it.
 

kmillen

Board Regular
Joined
Jun 8, 2007
Messages
115
What I'm suggesting is to return something, anything from function1 indicating it is safe or appropriate to call function2 or to call function2 in a certain manner.

1. Open a new Excel sheet
2. Tools | Macro | Visual Basic Editor Alt-F11.
3. Insert | Module (from the menu)
4. Enter this code.

Code:
Option Explicit

Function function1(s As String) As Boolean
    If Len(s) > 10 Then
        s = Mid(s, 1, 4)
    End If
    function1 = (Len(s) > 5)
End Function

Function function2(p As Integer) As Integer
    If p > 10 Then
        p = p * 23
    End If
    function2 = p
End Function

5. Go back to the Excel sheet and in cell A1 enter:
=function1("abcdefghijklmnop")
6. In cell A2 enter:
=IF(A1, function2(7), function2(27))

Using a method similar to this, it is guaranteed that function1 will run before function1 (they won't bump into each other).

These functions will run sequentially because function2 depends on the output of function1 even though no value is passed from function1 to function2.

This concept is what I suggested in my first post.
 

Forum statistics

Threads
1,181,102
Messages
5,928,063
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top