Any Extra Execution Time?

[Cyberdude]

Is there any significant cost in execution time (or anything else) to Call a small procedure (say, 10 lines) rather than imbed the called code in-line in the main procedure?
Does the compilation pre-find the location of the called procedure, or does that have to be done at runtime?
Is it any more expensive to Call a procedure in Personal.xls than it is to call the same procedure located in the same workbook? I've wondered about that.

[johnske]

Hi Cyber,

Offhand - dunno. Why don't you time it? (you'll need to run a few times and average out the results)

Option Explicit
 
Sub TimeIt()
Dim StartTime#, FinishTime#, N&
StartTime = Timer
'put your own code below (example given)
For N = 1 To 100
[A1] = N
Next
FinishTime = Timer
MsgBox "Total Time = " & FinishTime - StartTime
End Sub

Pre-finding depends on whether you have Background Compile checked or not (if it's checked it may only be 'found' during run-time)...For a bit more about this, check this out

HTH,
John

[Cyberdude]

Thanx for the suggestion, John. The only reason I haven't done a test is that I'm not sure I could do a valid one that excludes other factors (unknown to me) that might skew the results. Maybe I'll try it anyway. I was hoping that the answers were widely known by the experts. I know that John Walkenbach has run some timing tests on other things and reported on them in his books, but this topic wasn't covered. Just thought I'd ask. Thanx again.

[Bob Phillips]

The only reason I haven't done a test is that I'm not sure I could do a valid one that excludes other factors (unknown to me) that might skew the results.

That is why you would repeat the test, in both situations, a set number of times, and average the results out. It doesn't remove the other factors, but it does smooth it somewhat.

If you want a hi-res timer, Karl Peterson does a beaut at http://vb.mvps.org/tools/ccrpTmr/

[Ken Puls]

FYI,

One thing to watch out for when you're timing code is messageboxes and inputboxes. Anything that ask the user for an input CAN significantly skew the results.

Whenever I time a proc, I always comment msgbox and inputbox areas, and put in some code to set any required variables to eliminate this problem.

HTH,