How do I disable and enable macros on the fly?

I would like to test an Excel VBA app I made.

However the VBA code messes around with the visibility of cel开发者_StackOverflow社区ls and that's a pest when editing the sheet.

Is there an option is enable and disable macro's on the fly without having to

Close the sheet
Change the macro settings
Reopen the sheet
Close the sheet
Change the macro settings.
etc.

As far as I know, you can't enable / disable macros from an opened workbook on the fly.
Yet, you shouldn't have to because macros are only triggered thanks to a user click.

The only case I would see is for the Event Procedures (Worksheet_Change or else).
You could then create procedures to activate / deactivate events and call them from buttons in your worksbook:

Sub enableEvents()
    Application.EnableEvents = True
End Sub

Sub disableEvents()
    Application.EnableEvents = False
End Sub

You can also try these tips from Chris Pearson website using global vars you would change depending on your needs:

Public AbortChangeEvent As Boolean

And check if afterwards:

Private Sub Worksheet_Change(ByVal Target As Range)
    If AbortChangeEvent = True Then
        Exit Sub
    End If
    '
    ' rest of code here
    '
End Sub

To disable macros on the fly, use "Application.EnableEvents = False" via the Immediate window in the VBA editor (and "Application.EnableEvents = True" to turn them back on).

You can also hold down SHIFT when you open a document to disable macros.

As of Excel 2010* there's actually an option on the "Developer" tab which allows you to disable macros as easy as ABC. Design Mode lets you do just that!

*Maybe even earlier versions.

I often fire Macros when opening a workbook but sometimes I don't want the Macro to fire so I can work on the code. So what I put a Macro in a separate workbook that does the following:

1. Disables macros
2. Opens the workbook in question
3. Enables macros
4. Then closes the original workbook
5. Leaves the second workbook open Here's the code:

Sub OpenClose()

    'Opens Workbook below with Macors disabled

    'After it is open Macros are enabled

    'This Workbook then closes without saving changes, leaving only the workbook below open

    '************************************************************
    'User only needs to change the workbook name on the next line
    WorkbookToOpenNoMacros = "Gaby.xlsm"
    '************************************************************

    Dim wb As Workbook

    Set wb = Application.ThisWorkbook

    Application.EnableEvents = False

    Application.Workbooks.Open (ActiveWorkbook.Path & "\" & WorkbookToOpenNoMacros)

    Application.EnableEvents = True

    wb.Saved = True

    wb.Close SaveChanges:=False

End Sub

I have Office 2013 and I found a button on the VBA window that did this for me very easily.

There's a play, pause and stop button on the toolbar. In Excel, they are actually called, Run Macro, Break and Reset.

Click the Break button (pause) and any running macros should stop running. I only tested it on one macro but seems reasonable to presume that this will work in general.

Also, I believe those buttons were there for many versions of Excel, so it's worth checking earlier versions.