How far are you willing to go to satisfy your morbid curiousity?

As Tom poined out, executed lines of code is not a good metric for
code efficiency. Still, it *is* an interesting metric. I figured out a
way to obtain it, at the cost of some preliminary work:

1) Make a copy of your spreadsheet! This experiment will trash your
code layout and possibly introduce some bugs, so it really only makes
sense to either do it on a copy or do it on the original then restore
from backup afterwards.

2) Make sure you have the edit toolbar displayed (with code indenting/
outdenting and commenting in/out blocks). Also - turn off autosyntax
checking (an annoying feature) but make sure that syntax errors are
still highlighted.

3) Go through each module and select all (ctrl A) then outdent the
heck out of everything so all code is flush left

4) Go through each module and remove all blank lines and all whole-
line comments

5) In edit /replace, using options "search current project" and "find
whole word only" ,
do the following edit replaces (replace all): Public with 'Public
(note the comment), Private with 'Private and similarly with the key
words Dim, End, Next, Loop, Wend (if you use it) and Option

6) module by module, select all and comment out *twice*. Note that
this will cause things like Dim to appear with three ' in front

7) In edit/replace *turn off find whole word only* then:
a) do replace all with replace ''' by nothing (empty string)
b) do replace all, replacing '' by ELC = ELC+1: (with a space
after the colon)
If for some strange reason you are already using ELC ("executed lines
of code") just pick another identifier that you are not using.

8) certain things will have been handled wrong. Using (project-wide)
edit/find and repeatedly finding next, search for the following and in
some cases delete the ELC = ELC +1:

a) Sub
b) Function
c) _ (space,underscore) - ELC = ELC+1 should not appear in the
middle of a continued line
d) Else - ELC = ELC+1: Else is a syntax error. I didn't want to
put it in the list of things to put an initial ' in because it might
appear in the middle of a 1 line if
e) If - for some reasonELC = ELC +1: If ... is a syntax error.
But - it makes sense to count the number of times a conditional is
executed, so I replaced these with
ELC = ELC + 1
If ...
(on two lines)

parts d and e should correspond to lines highlighted as syntax errors,
so you can scan the source file visually instead of using edit/find.

9) Fix any other syntax errors (the above worked for me but YMMV)

10) Declare Public ELC as Long in a general code module

finally:

11) Put ELC = 0 at the beginning of the sub that you are interested in
and
Msgbox ELC at the end.

I did this with a 200 lines of code program I've been working on.
After I figured out how to do it, it only took me about 5 minutes to
do actually do the above steps. Since many of them are project-wide
search and replaces or global manipulations of all the code in a
module, my guess is you could do it in about 20 minutes for a 2200
line program. Incrementing counters is a cheap operation, so I didn't
notice a significant slowdown (even though my program was a simulation
that went through several million steps).

HTH

-John Coleman

p.s. Don't forget to make a copy!
On Feb 19, 4:58 pm, MikeZz wrote:
I have a workbook that imports and manipulates data from another worksheet.
I press 1 button which in turn runs about 30 subroutines and functions
totalling about 2200linesof actual VBACode.

Call it morbid curiosity or bragging rights... or just pure inefficiency but
I'd guess I probably run through anywhere from 100,000 to 500,000linesofcodejust because of all the loops, functions & subroutines. Obviously that
number also changes based on the amount of data that I'm trying to import.

I'd like to have a way to countlinesofcodeexecuted from when I push the
button until it's all done without slowing thecodedown.

I'm visualizing something similar to taking the "now" time at the beginning
and end, caclulating the difference.... but withlinesofcode.

This would also help me to improve the efficiency because I could run the
same source data file for each change to see if it really helped.

Thanks,
MikeZz