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:

[sourcecode language=”vb”]Sub SomeRoutine()

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

[highlighted] line as well as everything in between the line you want to go back to – including the last character of the line you want to go to, as seen below, then copy and paste in place (CTRL + C, then CTRL + V).

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.