VBA: Move executing line anywhere
Summary
Okay, so this may be a bit weird for a blog post, but my mind was blown when I saw this. An extremely nice gentleman by the name of Jordan Webb of EXOS showed me this. More proof that you can learn anything, anywhere, anytime, from anybody. Thank you Jordan!!
The examples used here will be with Excel 2013, but it goes for any VBA IDE. Stepping through lines of code is as easy as using the F8 key. Stepping over a line of code is as easy as Shift + F8. But what if you want to make the currently executing line of code just go straight to another line, without executing anything at all. Here we’ll discuss two methods for doing just that. This does assume you’re stepping through your code and not just running it (i.e. pressing F5 in the IDE).
Say what?
This may be a little confusing, so I’ll try to elaborate a bit here. I’m going to be using a very simple piece of code for this example:
Dim MyVariable As Variant
MyVariable = “abc”
Debug.Print MyVariable
MyVariable = “def”
Debug.Print MyVariable
End Sub[/sourcecode]
If we’re stepping through this code and we come to the line where the variable is set for the second time (MyVariable “def”), say we do NOT want to execute this line, but we want to go back and run the line where the variable is set the first time. (I realize this is a very simplistic example, but bear with me, this will make sense it a moment.) There is no built-in method to step back to a previous line, it’s always executing going down lines of code, never back up. Here we will show two methods of going back up in your routine with the line currently being executed (i.e. the highlighted line).
Making it happen
In the below picture we’re executing code (in break mode) and the highlighted line is the code which will be executed the next time we press F8. So at this point the variable ‘MyVariable’ is set as a string to “abc”. If we execute the next line of code it will be changed to “def”.
At this point if I change the original variable, which could be for many reasons, an example being maybe I just want a different value for ‘MyVariable’, so I change the value while in break mode.
In the above picture the first time ‘MyVariable’ is set was changed to “xyz” instead of “abc”, all while in break mode. To make this code actually execute we need to get the highlighted line back up to where that code runs.
Method 1
This awesome method is by far the easiest way I’ve ever seen, and while others may have known this for years, Jordan showed it to me and blew me away.
Simply click and drag the yellow arrow back up to where you want to execute the code from.
Bam! That easy. Why I didn’t know this before astonishes me, but I’m thanking Jordan because I will use this CONSTANTLY!
Method 2
This is how I used to perform this method, until I was shown the method above.
CAUTION: It works just fine, but a side effect is it will wipe out any break points you have. |
By selecting the entire
This will return the line to be executed (highlighted line) to the first time ‘MyVariable’ is declared, as seen below.
Summary
While this may not be used all the time, when debugging or stepping through code this can save tremendous time, especially as your routines/functions grow in size.
You can also right-click some previous line and use “Set Next Statement” – fairly equivalent to dragging the yellow arrow.
Oops — or some later line — doesn’t NEED to be previous!
Sweet – never used that one before either. Thanks Bob!! 🙂
And the shortcut key for “Set next statement” to the line with the cursor is Ctrl+F9, which is probably the one I use most often, being a keyboard guy.
Tony, that’s brilliant. I will use that constantly, and probably most often! Thanks!
7 years later and the answer I’ve been looking for, for longer than that! Thank you!!!!!!!!!!!
Glad we were able to provide the insight that you needed.
[…] recent post over at Excel & Access Experts got me thinking: how many ways are there to change a […]
This is a useful trick that I use all the time ExcelAndAccess, but you need to be careful. I frequently reset prior to a loop, and the stack can complain if overdone!
Duly noted Bob. Thanks!
This is good to know and I’ll definitely keep this in mind next time I’m running through my code. I learned something new today!
Thanks for sharing.
Great article and example! I recently read the Professional Excel Development book and there is a great chapter on Debugging that explains all of these shortcuts. My mind was blown as well. How did I not know about these?
The one that really blew me away was the Ctrl+Space Bar shortcut to display a drop-down to auto-fill variable names, methods, properties, etc. I wish I knew this one years ago.
Thanks Jon! That book is hands down the best Excel book I’ve ever read. CTRL + Spacebar is definitely a must know and huge time saver. CTRL + J. Never leave home without that one either. 🙂