Which call method is the fastest?

Which call method is fastest?

Method 1

Please Login or Register  to view this content.

Method 2

Please Login or Register  to view this content.

Method 3

Please Login or Register  to view this content.

(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.....

Something like?

Please Login or Register  to view this content.

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).

Please Login or Register  to view this content.

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 )