Count number of lines of code that execute in a macro?

[mad.scientist21]

Is there any way to count the number of lines of code that execute in a macro? I do not mean simply counting the number of lines of code in a macro, but the total number of lines executed. For example, a loop may only contain three lines of code, but those lines may be executed 1,00 times each for a total of 3,000.

(Of course, if I had all day I could step through the macro and count the number of times I hit F8.)

Thanks in advance.

 

[VoG]

Hello and welcome to MrExcel.

Why?

 

[mad.scientist21]

The first reason is sheer curiosity. I think is is a more interesting thing to know how many lines of code were run than simply knowing the number of lines of code that were programmed.

Second, I would like to use this as a way to measure efficiency differences between two macros that do the same thing.

 

[VoG]

Well, from one mad scientist to another I don't think that this is possible without inserting timing code after every line.

You can, of course, time the entire procedure very easily.

 

[pgc01]

Hi
Welcome to the board

I remember using a profiling tool (Lint), and it was a great help to improve the performance of the solutions. If you know that a part of the code is responsible for 40% of the execution time and another part for 1%, you could not care less about this last part, even if you know it's poorly written, and will concentrate the team's efforts on the first part. You know that any improvement on the first part may have important reprecussions on the overall performance.

An interesting part of using this type of tools was the dynamics of the process. You could really see how your efforts on the improvement of different parts of the solution were rewarded (or not) in terms of execution time.

I've never worked with a code profiler for vba and don't know any native tool from MS that does it. I guess you'll have to look for a 3rd party software.

The only one I've heard of is the one from Bandwood but there are probably more out there

http://www.bandwood.com/vbacp.htm

Try googling code profiling/profiler or code analysis tools