Which call method is the fastest?

Which call method is fastest?

Method 1

Sub Macro1
pseudo code
Call Macro2
Exit Sub

Method 2

Sub Macro1
pseudo code
Macro2
Exit Sub

Method 3

Sub Macro1
pseudo code
Application.Run "Macro2"
Exit Sub

(If there is a speed difference but the difference is negligible then, just for the sake of the argument, imagine the code is running on a very slow computer).

Also apart from code appearance & speed difference (if any) are there any pros/cons to any of these methods?

Hi,

I prefer to see Method 1 of Call Macro2.

If you have some crazy name macro like DoThisNow and you don't use the "Call" then it isn't obvious if it is a Macro you are running or some built in VBA thing.

I believe if you use Method 3 it will need to parse the string and find it in the list of macros and therefore take a little bit longer.

If I try to read my code after a few months or read others code I find the Call Macro syntax much more readable.

Thanks Marvin for the quick reply.

Originally Posted by MarvinP

I prefer to see Method 1 of Call Macro2. If you have some crazy name macro like DoThisNow and you don't use the "Call" then it isn't obvious if it is a Macro you are running or some built in VBA thing.

Agree on both points. But I am willing to switch methods if Method 2 or 3 is superior.

Originally Posted by MarvinP

I believe if you use Method 3 it will need to parse the string and find it in the list of macros and therefore take a little bit longer.

I have that theory too but I am looking for something to back that up... Which is why I started this thread.

Can anyone confirm/deny our theory re the speed issue with Method 3?

Hey,

My signature line is "One test is worth a thousand opinions".

It seems to me you should create two macros. The first uses method 1 and you should call your macro2 that is a simple return. Put a for.. next loop in Macro 1 and a timer and run the call to Macro 2 1000 times. Then change it to method 2 and run it again. Then for method 3.

PLEASE let us know the results.....

Originally Posted by MarvinP

"One test is worth a thousand opinions".

Too True

Originally Posted by MarvinP

PLEASE let us know the results.....

See test code at the bottom of this post. (I decided to use 50,000 loops. 1000 wasn't enough to show up any real difference).

My results were:
method 1 = .172 seconds
method 2 = .109 seconds
method 3 = 8.219 seconds

Results will vary by computer performance of course.

My conclusion:

• Method 1 is superior as it is fast & it is is the easiest to read .
• The speed difference between 1 & 2 really is negligible. (Remember I am talking about 50,000 loops here. If you set it to say 1000 then there is no recordable difference between 1 & 2)
• Method 3 is clearly the worst in terms of speed (And the result shown here is an optimal result - I tested it in an new XLSM. My theory is that the more macros you have in a project, the more procedures VBA will need to search in order to find the macro to run and the slower method 3 will become).

Option Explicit

Public Sub SpeedTest_Methods123()
''Code based on "Len vs blank strings vs vbNullString"
''http://www.jpsoftwaretech.com/category/vba/
    Dim i As Long
    Dim starttimeMethod1 As Single
    Dim endtimeMethod1 As Single
    Dim starttimeMethod2 As Single
    Dim endtimeMethod2 As Single
    Dim starttimeMethod3 As Single
    Dim endtimeMethod3 As Single
    Dim Msg As String

    Const numberOfLoops As Long = 500000

    ' use method1
    starttimeMethod1 = Timer
    For i = 1 To numberOfLoops
      Call Macro2
    Next i
    endtimeMethod1 = Timer

    ' use method2
    starttimeMethod2 = Timer
    For i = 1 To numberOfLoops
      Macro2
    Next i
    endtimeMethod2 = Timer

    ' use method3
    starttimeMethod3 = Timer
    For i = 1 To numberOfLoops
      Application.Run "Macro2"
    Next i
    endtimeMethod3 = Timer

    Msg = "Number of iterations: " & numberOfLoops & vbCrLf
    Msg = Msg & "Using method 1: " & _
      Format(endtimeMethod1 - starttimeMethod1, "#.###") & " seconds" & vbCrLf
    Msg = Msg & "Using method 2: " & _
      Format(endtimeMethod2 - starttimeMethod2, "#.###") & " seconds" & vbCrLf
    Msg = Msg & "Using method 3: " & _
      Format(endtimeMethod3 - starttimeMethod3, "#.###") & " seconds"

    MsgBox Msg

End Sub

Private Sub Macro2()
'enter some dummy calculations so that macro 2 has something to do
    Dim Low As Double
    Dim High As Double
    Low = 20
    High = 50
    High = High * Low
End Sub

P.S. I remember now why I used to use Application.Run. Unlike Call, Application.Run has the ability to call a module level sub in a different module. (But then again - either make the sub you are calling project level or move the sub to where you really need it )

Something like?

Sub Method2()
Dim Timer As Double
Dim Ctr As Double
Timer = Time
For Ctr = 1 To 10000000
    Call ReturnFromCall
Next Ctr
Debug.Print Format(Time - Timer, "0.0000000s")
End Sub

Sub ReturnFromCall()

End Sub