6

I have a complex VBA function and a workbook with multiple sheets. Let's say the code goes through every row of Sheet1 and does something with that data.

I would like to be able to pause on SourceRow = 16, check out results I'm getting on different sheets and then continue on (press ok, or some key stroke)

I've tried

If SourceRow = 16 Then
    MsgBox "ReachedRow 16"
End If

But the Message box is modal and I can not switch to a different sheet(s) to see the data.

P.S. 16 is just as an example, hardcoded for now but will not be later on.

Improve this question
3
  • 2
    Debug.Assert False? Commented Jul 28, 2015 at 15:21
  • 2
    Are breakpoints sufficient? That would allow the code to stop when a line is reached (Can be inside an If Statement) and then allows you to change sheets etc. and then resume the code Commented Jul 28, 2015 at 15:23
  • @Evan I agree that breakpoints are a possible option, but when I'm doing what Boris refers to, if it's something where I'm including a break for a specific point in a loop and need to add an "if i = x" statement, then using Stop as Joshua indicates below removes the requirement of adding an extra line of code just to click a breakpoint on it. Commented Jul 28, 2015 at 15:26

2 Answers 2

Reset to default
14

You can use the Stop statement and then press F5 to resume the code execution. This is akin to adding a breakpoint.

If SourceRow = 16 Then
      Stop
End If

The row will be highlighted yellow while it is paused and you should be able to navigate work sheets.

Example:

enter image description here

In similar fashion you can use Debug.Assert (sourcerow <> 16) which will pause the code when sourcerow<>16 evaluates to false, that is to say when sourcerow equals 16. (Thanks to RBarryYoung for the comment)

If you don't want a permanent stop in your code, consider adding a breakpoint by clicking the gray region to the left of the editing window in the VB editor, which should will look like this:

enter image description here

The code will stop when the line is reached. Again press F5 to resume code execution. Alternatively you can continually press F8 in order to step through the code line by line. This eliminates the need to set many breakpoints.

Simply click the maroon circle to dismiss the breakpoint. Breakpoints are not saved in your code, so this is a temporary method (as opposed to the two methods listed above which would stay in your VBA code if you save the workbook).

The images were taken from this tutorial from Wise Owl regarding breakpoints.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Actually you can just use Debug.Assert (SourceRow <> 16) and do without the If structure also.
Updated because that's a better way.
6
  1. Right-click on SourceRow (anywhere in your code) and click Add Watch....

  2. In the dialog that appears, enter the following in the Expression textbox:

    SourceRow = 16

  3. Under Watch Type, select Break when value is True.

  4. Click OK.

This will automatically cause your program to break/pause when the value is reached and there's no need to add any new code just for debugging/pausing purposes.

Improve this answer

4 Comments

Thanks for that! I never knew what to do with "Watch". Learn something new every day. :thumbsup: (Quick question: do you think it's better to add some 'watches' instead of using say 5 'breaks' in the code? Or is it 100% user preference, and both accomplish the same?)
That's a question that could be asked on it's own and generate a lot of comments. Watches are more about variable inspection and breakpoints are about pausing execution. However, many debuggers (like VBA's) build some breakpoint features into their watches so you can create what is essentially a conditional breakpoint.
What,s cool about Watch is that if the code is long, you don't have to fish out all the break points. Also, you may or may not want to break on a line depending on the condition, whereas a breakpoint always will break there. If your condition is i = 5, the Watch will have the code break every time i = 5, so that's more powerful.
@DavidGM - that's an excellent point. I have a few break points I use but only have them because I'm waiting for a loop to reach a certain value. This has been a surprisingly fruitful thread for me. Thanks to you both!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.