Can I count total lines of VBA Code Executed in a Series of Subs?

[Guest]

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 2200 lines of actual VBA Code.

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,000 lines of
code just 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 count lines of code executed from when I push the
button until it's all done without slowing the code down.

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

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

 

[Guest]

there is no direct support for it or any reason that there would be.
If you want to optimize your code, lines of code would have a week
correlation.

a=3: b=4: c=5: for i = a to c*b step 2: cells(i,5) = i + c: next i

is one line of code as an example. and a legitimate single line of code
such as

colLetter = split(cells(1,i).Address(1,0),"$")(0)
can be slow.

 

[Guest]

I see your point. Having nested functions on the same line causes the problem.

Is there some tool out there that can at least analize a piece of code when
I run it and tell me what subroutines and functions tend to be called most
frequently? That way I can focus on improving certain sections of code that
tend to be called most.

Thanks,

 

[Guest]

Try searching google for Profiler (or code profiler) and VBA (or maybe event
vB6). I am not aware of such a tool for Excel although such tools exist for
development environments.

Here is one I found: "Code profiler" vba

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

--
Regards,
Tom Ogilvy

 

[John Coleman]

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!

 

[Guest]

Another easier way could be this?...
Copy All Module Code to excel worksheet,
Write a routine to read each line of code using offset from the top line.
If the line includes a code... ie not blank and doesn't start with comment,
Insert a line of code: ECL = ECL +1
Paste array of new code (with the ECL Counter on every other line) into a
new sheet.
Copy all code from the new sheet and paste back into the module I'm checking.
Clean up any bugs and run.

Sounds plausible to do either way but I think I'll see if there is a utility
to somehow do it for me.

Thanks for your help! It's always interesting to find a way to do something
that can't be done.

 

[John Coleman]

I was trying to avoid an approach using code on the theory that it
would probably be more time than its worth (at least to me). Having
said that, if you really want to do it in code, the way to go would be
to write some code scripting the VBIDE (a surprisingly but not
hopelessly complicated thing to do) to create a general purpose
routine for either adding or deleting the counters in a clean way. To
get a handle on the VBIDE object model, see the excellent on-line
article: http://www.cpearson.com/excel/vbe.htm

Hope that helps

-John Coleman