Which call method is the fastest?

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 )