stepping through a procedure

[Nick Vittum]

I'm beginning to get a grasp on using f8 to step through a procedure. Is there any way to step backwards, when I see a step the makes me say "Wait, how did I get here?"

[HansV]

No, you cannot "undo" steps.

[Nick Vittum]

Okay, thanks

[Doc.AElstein]

Hi Nick,
You can , in some situations, do some things which may have a similar effect for you as things like … ..backwards, … "Wait, how did I get here?" ….
At least in the macros I do it has a similar effect.
( It is partly based on a good understanding of the Immediate window, which I will bore you with recommending once again: I don’t labour recommending many things a lot, but the time you invest learning to use the Immediate window effectively will usually be paid back in the time you save soon after , the next time, a macro does not quite do what you want…
https://eileenslounge.com/viewtopic.php ... 21#p247121 )

_._______________________________-

_1 ) Drag the yellow arrow about:
If you don’t know already, then the yellow arrow in the left margin which you see in the f8 step mode can be dragged up or down. So you can go back , start again from anywhere, or even drag it forwards to miss bits out. ( The code lines are not done as you drag the yellow arrow up and down over them )

_2) Use the immediate window to do things.
Easiest is to explain this with an example. .... Lets say for example, you were stepping through a macro and the value of a variable, Lr , is 8 , and that value, 8, will give you grief later . You are not sure yet why and where it got that value from, but you would like to continue with f8 step mode , but with the value of Lr being something else, such as 9
Simple: In the Immediate window type
Lr = 9 , then Enter
That changes the variable value in the macro that you are stepping through

_3) Hover over variable to see what in them.
I expect you know this already, but just for completeness: If in f8 step mode you hover over simple variables with the mouse, then Excel shows you what is in them

_4) Watch window ( & Immediate window ).
Again this is just for completeness, as you probably know already, ( https://eileenslounge.com/viewtopic.php ... 16#p267116 )
If you are using f8 step mode , then it is a great addition to set watches on variables, as you see immediately how they get filled.
Remember that in the case of the Watch Window, and the Immediate window , you can drag them to somewhere convenient, even outside the VB Editor, ( and re size them ) , so you don’t have to have them cluttering up the code window of the coding you are stepping through

Alan

[Nick Vittum]

Thanks, Alan. I've been thinking it was time to get a better understanding of the uses of the immediate window, and to learn what the watch window is and how to use it. Time to take that plunge, I guess.

1 ) Drag the yellow arrow about...

I had discovered you could move the arrow around within a routine. Where I find confusion, and why I hoped there was the equivalent of a back arrow, is when I'm stepping through one routine, and suddenly it jumps to another somewhere else that I hadn't expected (and maybe hadn't intended, or maybe put in so long ago I can't remember, or maybe is a vestige of an approach I was taking earlier, but have changed) and I want to go back to the exact step previous and re-examine it.

The code in this main project I've been working on has become terribly convoluted, due to the fact that it's been an evolutionary process rather than a planned one. I made some real progress yesterday in cleaning it up. The guidelines snb posted helped a lot, but to a considerable extent, progress was the result of stepping through the code form end to end, over and over again until I'd all but memorized the steps and could see which were no longer necessary, and which ones could be better located somewhere else.

Having had my origins in the building trades, like you, I can see that the next time I take on a project of this extent, I'll draw out a blueprint of its code structure before I start. But like an old house, it's always harder to apply that logic to something already built than something being built from the ground up.

[Doc.AElstein]

.... Where I find confusion, and why I hoped there was the equivalent of a back arrow, is when I'm stepping through one routine, and suddenly it jumps to another somewhere else that I hadn't expected (and maybe hadn't intended,....

It is an often reported bug that things like that happen in the f8 step mode. One common cause seems to be when you have UDFs, and have them referenced in a spreadsheet cell.
If you can temporarily stop or remove your UDFs when using f8, that often stops that annoying thing happening.
I think a code line something to do with Application .. Calculations = or similar can also help overcome this problem.
I remember a Thread on it. I can’t find it quickly just now, if I do I will re post …

[Nick Vittum]

You flatter me :~) I have only one or two UDFs throughout, and those are only called in a couple of rather isolated situations. No, I think it's just the structure (or lack of it) of my code as a whole. Lack of blueprint. But getting better as I work it through.

[Doc.AElstein]

Sounds ,like its not the problem I was thinking then. But anyway I found the thread
https://www.excelforum.com/excel-progra ... pping.html
(It doesn’t actually explain too clearly what its talking about, but I expect it is suggesting this code line
Let Application.Calculation = xlCalculationManual )

The only other routines of mine that annoy me a lot by working when I did not want them are the Events procedures. I still get caught out when I have those sorts of procedures , and then I run another macro that does something to set them off unintrentionally. The corresponding code line to stop them is
Let Application.EnableEvents = False
But important is that you do that in pairs , like

Code: Select all

 Let Application.EnableEvents = False
 ' do the code line which gave you grief by kicking off an Events procedure which you did not want to be set off here
 Let Application.EnableEvents = True

If you do mess with any of those code lines, then you probably should make yourself an “Oops” macro.

Code: Select all

Sub Oops()
 Let Application.EnableEvents = True
 Let Application.ScreenUpdating = True
 Let Application.Calculation = xlCalculationAutomatic
End Sub 

I have a small button on a lot of my workbooks, that set off that macro. It basically turns things on that should usually be on, but which might unintentionally get switched off when you are messing about. If those things get switched off, then , as you might expect , things don’t work like they should. ( Those code lines will not error if the thing they try to turn on is already turned on, so usully running that macro doesn't do any harm)

Alan

[LisaGreen]

Hello Nick,

Do you use any "utilities" for your code? I'm thinking specifically of MZ Tools.

Lisa

[Nick Vittum]

@ Doc.AElstein Thank you. I seem to have managed to clean it up fairly well by getting rid of some redundancies, and in some cases moving event procedures to more appropriate locations to get rid of some "looping."

In other contexts I had a pair of macros to disable event and updating and so forth, that were called from a few spots. But they didn't seem to do the least bit of good, so I stopped using them. Sadly. I think what slowness remains in my app is due to need for a new computer, not the code itself. Not an option, right now.

@ LisaGreen No, no utilities. I don't know what MZ Tools is. The only thing of this sort I know about is. KuTools. But I haven't used it, and in general, I've treated these things with suspicion, not knowing who created them, or how safe and reliable they are.

[LisaGreen]

Nick,

I ask because it's possible to insert code into procedures that will send a message to the immediate window. Some tools will do this for you. This means you could put code like...

Code: Select all

Sub subDoTHis
dim vlSTime as variant

vlSTime= time()
Debig.print "Start of subDoThis"

' Code.

debug.print time() - time()
debug.print "End of subDoThis"
End sub

I have written code to do this. My code also has ...
' debug
... at the end. When I'm done with it I run another procedure that looks for that and deletes those lines.

When I was using dBase there was a very useful command that would print turn on a "history". THis would show the lines as they were executed. Would something like that be useful?

HTH
Lisa

[Nick Vittum]

When I was using dBase there was a very useful command that would print turn on a "history". THis would show the lines as they were executed. Would something like that be useful?

I think it would. Educational, at the very least. Could you tell me more?

[Jan Karel Pieterse]

Don't forget the View, Call stack. It'll tell you how you got where you are (and you can move back to see the statement you cam from, which is then indicated by a green triangle in the margin of the code pane).
Also, if you ever end in a seemingly never ending loop of calls into a UDF, simply hit control+shift+F8 to let Excel finish all calls to the UDF's.

[Nick Vittum]

Don't forget the View, Call stack. It'll tell you how you got where you are (and you can move back to see the statement you cam from, which is then indicated by a green triangle in the margin of the code pane).

Thank you! This looks like it will serve my needs perfectly.

[LisaGreen]

Hello Nick,

Please forgive me for not getting back till now. The truth is that I may have gone too far in my last answer to this thread and I'm actually very worried about it.

To ***very briefly*** answer your "... tell me more."...

It is possible to alter the code you write in the VBE with code of your own as I mentioned earlier.
If you insert code after every line in your procedures then it could give you a line by line report of exactly where it is.
In the code that does that you can also report values of before and after values of variable.
This would constitute a line by line trace report of your code execution rather than stepping through line by line.
After you've produced your report, examined it and corrected any errors, then the code lines you've added to produce the report can all be deleted in one swoop returning you to your original code.

It's not even very difficult ... but does require some knowledge of the functions needed to do that.
Aaaand if you want to trace through multiple procedures then a *** lot *** of lines need to be added. Wo/Manually, this is a lot of work but it's possible to automate that as well!

The reason I've been reticent in answering... and I realize that this is just a description of what's possible - no code ... is that you seem to be eager to understand every line of code suggested for you and I don't want to present you with lots of code that you may consider gobbledygook.

If you would like to go further with this then I'd be happy to but I don't think this thread is really the place.

Oh.. And BTW... Jan Karels' post is important!!! So many people dismiss the stack window!!! Me included!

Regards
Lisa