3

I couldn't find this asked anywhere. In Visual Basic (excel), I can hit F8 and cycle through each line. But lets say I want to begin the sub procedure, and then after executing the first 2 lines, I'd like to skip to line 200. Until now, I've always just dragged the yellow arrow to the desired line. This is really time consuming and I was wondering if there's any command to simply say "run current line where selected" or something.

Additionally, even if I could begin to run through line by line, and quickly move the yellow selected arrow to the desired line, that would also work.

Community
  • 1
  • 1
Joseph Erickson
  • 938
  • 3
  • 8
  • 24
  • `and quickly move the yellow selected arrow to the desired line, that would also work` doesn't that answer your own question? As far as I'm aware that's the only possible way to do what you ask. – AStopher Jun 13 '16 at 15:59
  • Why not comment out the lines of code you don't want to execute? – Matt Cremeens Jun 13 '16 at 15:59
  • @cybermonkey Hm...don't entirely understand how that answers my question. The "slow drag" isn't quick at all and is the reason I'm asking this in the first place. – Joseph Erickson Jun 13 '16 at 16:00
  • @MattCremeens Good idea. I thought there might be a more direct path to the answer, but maybe that's the best solution. – Joseph Erickson Jun 13 '16 at 16:01
  • 1
    Then what you're asking simple is not possible automatically. You *can* comment out your code up to the point where you want to run, however I don't think that provides a versatile solution. – AStopher Jun 13 '16 at 16:01
  • @cybermonkey I guess my thought is, if there's a "slow click and drag" solution, why isn't there some kind of hotkey for skipping lines? – Joseph Erickson Jun 13 '16 at 16:02
  • I guess that's how procedural and OO programming works, but I agree that a hotkey would help. – AStopher Jun 13 '16 at 16:03
  • @MattCremeens If you want to respond with that answer formally I'll accept it so the post can be closed. http://stackoverflow.com/questions/12933279/how-to-comment-and-uncomment-blocks-of-code-in-the-office-vba-editor – Joseph Erickson Jun 13 '16 at 16:04
  • use immediate window to execute one line code – 0m3r Jul 10 '23 at 13:32

6 Answers6

7

If you have a 200-liner procedure that does so many things you'd like to skip most of it, it looks like you need to refactor a bit.

Extract "things the procedure is doing" into their own Sub procedures and Function scopes. If you have banner-like comments that say things like '*** do something *** then that's a chunk to extract into its own procedure already.

Stepping through that procedure could then involve stepping over (Shift+F8) the smaller procedures that do one thing, or break and skip the call altogether.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • The 200 lines are necessary and helpful, however, for testing purposes, and without creating a new "test sub", I simply want to validate a portion of it. – Joseph Erickson Jun 13 '16 at 16:13
  • 7
    Allow me to strongly doubt that. Turns out robust code isn't tested manually by stepping through, but by specifically writing a `Sub` procedure in a dedicated test module, that only runs *that* code you want to test; if the procedure does 20 other things, there's no way to test it cleanly and automatically. I don't know why basic OOP and clean code principles get systematically thrown out the window whenever we're talking about VBA code, AFAIK VBA is just as capable of SOLID and OOP code as Java or C# is. – Mathieu Guindon Jun 13 '16 at 16:22
  • 3
    Well, VBA has a lot of quirks when it comes to OOP. But those quirks don't prevent you from breaking your code up and writing it cleanly! I have never seen a valid reason for a super long subroutine in practice. Only weak contrived theoretical situations. At the end of the day, you can chain a series of subroutines together all called from one central subroutine and it ends up being more readable and sustainable than one big one. – AndASM Jun 13 '16 at 16:25
5

Right click on the line you want to jump to. Hit the Set Next Statement option in the menu. That's equivalent to dragging the arrow to that line. (Ctrl-F9 is the hotkey for this action.)

If you want it to quickly execute every line up to a certain line, set a breakpoint then hit run instead of stepping through the code line by line. Do this by clicking on the gray bar to the left side where the yellow arrow appears. A dark red dot should appear and the line should be highlighted in dark red. This tells visual basic to stop when it hits that line.

You can also comment lines out by starting them with an apostrophy.

Finally, you can break code into subroutines and execute them independently of eachother.

Sub Subroutine1()
    'This is a commented out line. It does nothing.
    MsgBox "Do stuff here"
End Sub

Sub Subroutine2()
    Subroutine1 'This will run all the code in subroutine 1
    MsgBox "Do more stuff here"
End Sub

In the above example, if you run Subroutine1 you'll get one message box popping up. If you run Subroutine2 you'll get two message boxes.

AndASM
  • 9,458
  • 1
  • 21
  • 33
2

Unfortunately it is not possible to do what you ask directly.

However, you may comment out the lines of code above the code you want to be executed for example:

Sub Workbook_Open()
    'Application.DisplayFullScreen = True
    'Application.DisplayFormulaBar = False
    'ActiveWindow.DisplayWorkbookTabs = False
    ''ActiveWindow.DisplayHeadings = False
    Application.EnableEvents = True
    Password = "1234"
    ActiveWorkbook.Protect
    ThisWorkbook.Protect (Password = "1234")
End Sub

You may use GoTos, but however this is not considered good practice and may actively harm your code:

Sub Workbook_Open()
    GoTo ExecuteCode
    Application.DisplayFullScreen = True
    Application.DisplayFormulaBar = False
    ActiveWindow.DisplayWorkbookTabs = False
    ActiveWindow.DisplayHeadings = False
    Application.EnableEvents = True
    ExecuteCode:
    Password = "1234"
    ActiveWorkbook.Protect
    ThisWorkbook.Protect (Password = "1234")
End Sub
AStopher
  • 4,207
  • 11
  • 50
  • 75
  • 1
    you probably gave a competing alternative answer that didn't do things the way another person would. Sure your answer may be valid, but it isn't their valid answer! Here, have an upvote for one of many possible valid solutions. – AndASM Jun 13 '16 at 16:22
  • You said what he asked for isn't possible, but you can't you run the selected line by right clicking and hitting "Set Next Statement" when you're in debugging mode, instead of having to drag the arrow down while scrolling? – AndASM Jun 13 '16 at 16:30
  • @AndASM True, but the OP had already covered this in their question and they said that they wanted to know whether there was a hotkey of some sort. – AStopher Jun 13 '16 at 16:31
  • But there IS a hotkey. Ctrl-F9. – AndASM Jun 13 '16 at 16:33
  • (a random upvote brought me back here) - FTR this *was* my downvote. Reason for the downvote is that a 200-liner procedure will involve a ton of local (maybe global too) state, and selectively commenting-out everything up to the one line you want to run means your "test" is meaningless, because your "test" isn't being executed with the state it's meant to be executed with, *or* achieving that correct state will require *very* careful commenting-out. That's a good way to *introduce* bugs, not a good way to fix them. Refactoring the 200-liner proc is the only sensible way to deal with this IMO. – Mathieu Guindon Dec 12 '18 at 19:33
  • @MathieuGuindon Did you comment on the wrong answer? – AStopher Dec 13 '18 at 20:03
  • No, I saw 'reason for downvote?' and, not seeing any comment of mine here I figured I'd drop a note while I'm here. I guess I should have posted that comment 2 years ago =) – Mathieu Guindon Dec 13 '18 at 20:09
1

This is how I do it - basically if I know that my code up to line 200 is working properly but I'm pretty sure there's an error between 200-300 then before compiling - scroll down to line 200 and mark it (to the left of the code). Then compile it - click F5 and it will execute everything up to line 200 - then you can step through each line thereafter individually.

LD16
  • 95
  • 10
0

I normally comment out lines of code that I don't want to run with apostrophes. Alternatively, you can break up your code into smaller procedures so that you can easily pick and choose what you want to test/run.

Matt Cremeens
  • 4,951
  • 7
  • 38
  • 67
0

I found adding a module for testing and copy pasting snippets of code in it as a best way for troubleshooting.

IGRACH
  • 3,506
  • 6
  • 33
  • 48