MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Identifying which procedure is running

  Asked By: Gwen    Date: Dec 30    Category: MS Office    Views: 1738

Is there a way within VBA to identify the name of the procedure
currently running?



8 Answers Found

Answer #1    Answered By: Stacy Cunningham     Answered On: Dec 30

I'm sure there are other ways, butI use Debug.Print statements and
watch the Immediate window.

Put this first in each routine:
Debug.Pring "Enter RoutineName"

Put this last:
Debug.Print " Exit RoutineName"

You can get creative and add these when one routine calls another,
when various milestones are reached. You can indent the text
differently for various levels of subroutine call. You can also print
variable values at times.

This way you have a record of the program flow to examine in
difficult situations.

My code it really peppered with these and that in itself can be a
problem - keeping it all straight. I developed some standards that
help me do this. But, then, I also tend to comment my code out the
wazooo. (:-) working on the code in five years, I'll really
appreciate this.

Answer #2    Answered By: Jimmie Ramirez     Answered On: Dec 30

I prefer to use:

Application.StatusBar = "Procedure Name"

Procedure Name will appear on the StatusBar which located on the
bottom of the Excel worksheet. Make this one of the first lines in
each of your Subs. I also use this with a timer and when I am using a
loop you can print the loop number so that you know where you are in
the loop.

This works like Debug.print except that it prints on one line only
and you don't need the intermediate window open.

Answer #3    Answered By: Gilberto Thompson     Answered On: Dec 30

To add to the others comments... There is no easy current way to identify
which procedure  is running  without adding at least one line of code per

If you have a lot of procedures then this can be a real PITA!

Fortunately there are ways to automate the process but some of them can be a
bit contrived. It *is* possible to write VBA code to go through every module
and put code into the code... As it is possible to write code to delete that

There are caveats for doing this though and if you want to go there I can
explain further.

Then there are 3rd party programs. I can totally recomend MZTools, version 3
of which... Though now out of date... Is free and still works beautifully.
This will allow you to enter whole bunches of code with a click as well as a
whole bunch of other stuff.

There are advantages to both Debug.Print and using the status bar. A
disadvantage to using the status bar is that it doesn't leave you anything
to look at after.

Then you can add line numbers while debugging if you like and report the
line number you're on... Actually I've not used that in VBA so I don't know
if it still works.... Hmmmm... Time for a google session!

A disadvantage to Debug.Print is that it has a limited amout of space so you
may think you have the whole history but you don't!

You can comment whole sets of debug.Print statements out in one go though.

It's also possible to use conditional "compiling" so that you leave code in
but it only gets run if a certain switch is set.

But I'd first look how many procedures we are dealing with. Is it 30 or is
it five. Five is manageable by hand but 30 is starting to get into seriously
boring repetition :-)

Let us know how you get on or if you want more info.

Answer #4    Answered By: Abbas Hashmi     Answered On: Dec 30

As suggested, I already sprinkle debugging statements throughout my
coding. In all but the most trivial of procedures I embed something
like this:
If Not gDebug Then On Error GoTo finished
gProcedureName = (whatever) '...Global string variable
Application.Statusbar = gProcedureName
finished: if Err.Number > 0 then Wrapup
End Sub '... or Function

Wrapup is a "graceful exit" routine that Ends or Stops according to
the setting of a global constant gDebug (Boolean).

I was hoping that I could use a single generic function call instead
of customizing the definition of gsProcedureName each time. Guess not.
(My typical project uses well over 30 procedures.)

Lisa, thanks also for the tip re MZTools. I'll check it out.

Another question: Anybody know of a good cheap/free VBA code printer?
I've seen a few that run $40-60, but that seems a bit high. Until I
find something better, I'll continue using Notepad++ (which I highly
highly recommend as a substitute for Notepad).

Answer #5    Answered By: Jana Franklin     Answered On: Dec 30

There is a free ecvaluation version and it suits my needs without spending

I't at... Or was at... http://submain.com/?nav=products.pcp

The evaluation version has a nag screen but I can live with that. Worth
checking out anyway IMHO.

Answer #6    Answered By: Clay Cook     Answered On: Dec 30

Looks like this thread hasn't come up with the solution *I*
personally was hoping for!

Perhaps if we ask it another way:

The Debugger has a Call Stack dialog box (Ctrl-L) which is useful in
debugging during break mode.
But, are there any Functions that can return the contents of the Call

Haven't been able to find any, but if the MS Wizards were able to get
it to PRODUCE the Call Stack Dialog, then they MUST be there!

Answer #7    Answered By: Josephine Gomez     Answered On: Dec 30

The best we poor developers will be able to do is try to impliment a class
that gets activated for certain *recognized* events... It still needs code
added. See Chip Pearsons site.

The call stack is well hidden and possibly for good reason along the lines
of Intrusion Alert and the Resistance is Futile.

I think one reason why the debugger will jump in there is because it *needs*
to... That's it's job... But all the same it only works with a memory
snapshot. You have a problem going backwards... Always. It's normal to evoke
a debug environment *before* you run so that there *may* be a possibility of
steping back.... Never a certainty.

Classically, the way to find out what went wrong in a program is to take a
dump... No I don't mean anything brown and smelly... A memory dump... Of the

Tracing backwards don't give nothing... It's that last instruction in
assembler that's important and only that... What's it trying to do and why
can't it do it?

It sez... I want to do this... Tries to do it ... And bombs.

Now we come to the tricky bit... Why?

There are actually a limited number of options.

Most programs are seperated into data and instructions... A typical problem
is that the pointer to the next *instruction* gets lost... And tries to runn
a piece of data instead of a real instrucion. The adverse is true... The
progrma may get lost and try to retrieve data that is actually an

This is the basis of a *lot* of malware... The object being to get into the
program and disrupt it.... And or.. Upset the place in memory that's being
looked at.

The bottom though line for this Q though ... I feel anyway... MS ain't goona
tel us how to get at that stack

Answer #8    Answered By: Aadi Martin     Answered On: Dec 30

I suspected as much. but thought SURELY someone
reverse-engineered the Call Stack Dialog box...

I also dredged up some memories from back in
my Focus/Fortran/C++ days, and I distinctly remember
interrogating the stack... Then I remembered,
I BUILT the stack...

Looking around, it looks like that's what MS recommends.
Creating a Class object and using push/pop to manage the stack.

Oh well... i thought I might've had a work-around...
Guess not..

Didn't find what you were looking for? Find more on Identifying which procedure is running Or get search suggestion and latest updates.