Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA - Pause code execution

Tags:

excel

vba

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.

like image 226
Borisw37 Avatar asked Jul 28 '15 15:07

Borisw37


People also ask

How do I stop a VBA code from execution?

Stopping a Procedure To break the running VBA program, do one of the following: On the Run menu, click Break. On the toolbar, click Break Macro icon. Press Ctrl + Break keys on the keyboard.

How do you pause a macro for 5 seconds?

To wait 5 seconds, like I did in the demo above, you feed Application. Wait the current time with the Now function. Then, you use the TimeValue function to add 5 seconds to the current time. Once the 5 seconds elapses, your macro will continue.

How do you pause a macro when running?

You can interrupt a macro in Excel at any time by pressing Esc or Ctrl + Break. 1.

Is there a delay function in VBA?

You may want some way of pausing or delaying VBA code execution and you can do this with two functions called Wait and Sleep.


1 Answers

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.

like image 145
Soulfire Avatar answered Sep 28 '22 07:09

Soulfire