Excel VBA - Pause code execution

Question

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.

Answer 1

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:

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:

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.