Stop the execution of a macro using another macro(button) using the sendKeys function

Question

I have this Vba code that takes a lot of time to execute and i did this sub to stop the execution, But it didnt work , i used the Sendkeys function.

sub stop ()
SendKeys "{Ctrl,Pause}"
end sub

i want also to resume the execution of my Macro, i tried clicking Ctrl+Break but it didnt resume. Thank you for helping.

Answer 1

Open VBE and insert a module and copy-paste the below code

Sub Main()
    For i = 1 To 100000
        DoEvents
        Debug.Print i
    Next i
End Sub

Sub PauseMacro()
    Application.SendKeys "^{BREAK}"
End Sub

Go back to Sheet1 and on the developer tab insert a button and assign the PauseMacro to it.

Now run the Main sub and hit the button to stop the execution of the Main macro

Oh, btw. here's how to use the Application.SendKeys method.

Answer 2

You should avoid using SendKeys.

An approach similar to the one suggested by me how, but without using SendKeys could be this:

Global IsTimeToStop As Boolean

Sub Main()
    IsTimeToStop = False
    For i = 1 To 100000
        DoEvents
        Debug.Print i
        If IsTimeToStop Then Exit Sub
    Next i
End Sub

Sub PauseMacro()
    IsTimeToStop = True
End Sub

Replace Global with Dim if this is not a standard module.