VBA code Tracer

[Damann]

Hello, Does anyone know if there is a some code, or a program that will trace my vba code. I would like to see which way its branching, what procedures are being called in the order called, mabey a flowchart showing the actual flow of code. Since the code is a few thousand lines long, 200 + procedures this would make things a lot eaiser to follow as stepping through is soooo tedious, expecisally when i hit one of the loops.


a few years ago i found a program to do it but dunno where its at or what its called.

i tried the commerical program vistin, works ok but the flowchart it self could use a little work.


TIA Damann

[Ken Puls]

I don't, but I'm posting here to listen in on the discussion. :)

[Damann]

not found any yet, so looking at making a crude procedure to paste procedure names in a seperate ws in the order processed.


psuedo
pn = procedure name ( here is my problem how to get procedure name, is there a built in function to get this info, or one has to be made? )

set storage locations ("a1")
set wb
set ws
get procedure name
is there somthing like
MsgBox "The name of the procedure is " & ActiveSheet.procedure.Name
make storage book activefind next empty cell in colum A (use offset )
store in active cell = PN
return to calling procedure. ( or possibly place function call at start of all procedures )


any ideas, or a possible function ?

[XLGibbs]

You can "watch" the code run in the Locals window, but I am not sure if you can trap the Locals window elements efficiently for storing somewhere.....

Would be interesting of something were out there to do this........

I think the biggest issue is having a subroutine that is running to capture other subroutines running in process...would seem like a class would have to be built to actually trap every procedure call.

I suppose another alternative would be to paste the code into a word document...and use word macros to "find" procedure calls and associate them with the routines...sounds challenging at the minimum glance...

[Damann]

Well it kinda sounded simpler thanit might me. i was only thing about putting a call at the start of each procedure, calling the macro to store current procedure name in a cell.

somthing like.

sub generic ()
call PostProName

.................
your normal code

.................
End sub
_______________________________________________
sub PostProName()
dim proname as string 'variable for name temp storage
dim ws ' variable for ws Name
ws = "trace"
proname = GetSubName.active ( i know there no such thing just for explaination purpose)

Sheets.ws.Activate

Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = proname
End sub

except for the actual getting the name of the procedure i think this would work.

i would try to make a function but never tried to make a function, and i do not know how excel stores the procedure name. i have see somthing simular in a xla called code documentor, which does get the procedure name and paste it in a report sheet. but its just an analysis of objects, forms, procedures... in a tabulated form just to show whats there not the order of the calls. but i can't make heads or tails out of the code, beyond my skill level.

[Ken Puls]

If all you're looking for is the order of what's called, I would think that you could insert a line at the beginning of each routine to just paste the routine's name to the next available cell on a specified worksheet:

(untested)
With Worksheets("Trace")
.cells(.rows.count,1).end(xlup).offset(1,0).value = "TheNameOfThisProcedure"
End with

You'd have to clear the data between runs though.

:dunno

[XLGibbs]

Yeah, if that is all he wants is a consecutive list of called procedures...I am sure from there it could be spun into a decision tree/flow chart type thing..

[Damann]

i thought about typing the name of the procedure in each procedure then have it store itself in the TRACE ws it.

then i figured if i just copy a call then pasted it it to each procedure that would be less time consuming then copying and pasting each procedure name.

doing it the way i sugested would make it universal for all my different spread sheets.
and all i would be copying and pasteing would be the Call.

i was just looking to see what other options were, and not trying to re-invent the wheel if its already out there.

[Andy Pope]

Hi,

Maybe Stephen Bullen's CallTree (http://www.oaltd.co.uk/Excel/Default.htm) will do what you want.