Automatic Macro Execution

Enable seamless macro execution during Bear Decisions analysis using VBA integration. While Microsoft's APIs don't allow direct macro execution from add-ins, this VBA solution automatically triggers macros and continues the Run Iteration calculation process.

Prerequisites

• Macro Execution Mode: Enable "Enable Macro Interruption" in Settings & Configuration
• VBA Access: Your Excel workbook must allow VBA macros
• Developer Access: You'll need to access the Visual Basic Editor (Alt+F11)
• Existing Macro: You should already have a working macro that you want to automate

Setup Instructions

Step 1: Access the Visual Basic Editor

1. Open your Excel workbook
2. Press Alt + F11 to open the Visual Basic Editor
3. In the Project Explorer (left panel), find your workbook

Step 2: Add Code to ThisWorkbook

1. In the Project Explorer, double-click on ThisWorkbook under your workbook
2. Copy and paste the following code:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    
    ' This event is triggered when Excel recalculates, which happens when Office.js writes to cells
    If Sh.Name = "MacroMessageQueue" Then
        Dim triggerVal As String
        triggerVal = Sh.Range("B3").Value

        Static LastTriggerValue As String

        If triggerVal <> LastTriggerValue And Left(triggerVal, 8) = "Trigger_" Then
            LastTriggerValue = triggerVal
            Call Execute_Macro
        End If
    End If
End Sub

Step 3: Add Code to Module1

1. In the Project Explorer, find or create Module1 under Modules
2. If Module1 doesn't exist, right-click on Modules → Insert → Module
3. Copy and paste the following code:

Sub Execute_Macro()
    
    ' Call your existing macro logic
    Call Integrated_Macro

    ' Send continue command via message queue
    Call SendContinueCommand
    
End Sub

Sub SendContinueCommand()

    On Error Resume Next
    
    ' Access the hidden message queue sheet
    Dim messageSheet As Worksheet
    Set messageSheet = ThisWorkbook.Worksheets("MacroMessageQueue")
    
    ' If the sheet doesn't exist, the add-in isn't running or hasn't set up the queue
    If messageSheet Is Nothing Then
        Debug.Print "MacroMessageQueue sheet not found - add-in may not be running"
        Exit Sub
    End If
    
    ' Send the CONTINUE command
    messageSheet.Range("B1").Value = "CONTINUE"
    
    On Error GoTo 0
End Sub

Sub Integrated_Macro()

    ' Your macro logic should handle its own event management if needed
    ' Only disable events around operations that might trigger unwanted events

End Sub

Step 4: Customize Your Macro

Replace the placeholder Call Integrated_Macro with your actual macro's name in the Execute_Macro() subroutine, or put your existing macro's code into the Integrated_Macro() subroutine.

Important Considerations

Retry Logic & Timing

The system includes automatic retry logic to handle timing issues:

• Default Retry Interval: 8 seconds between retry attempts
• Maximum Retries: 5 attempts before timing out
• Custom Timing: Adjust the retry interval by entering a duration (in seconds) in cell E1 of the hidden sheet "MacroMessageQueue"
• When to Adjust: Only modify timing if your spreadsheet is unusually slow to calculate

Testing Your Setup

1. Save Everything: Save your workbook with the new VBA code
2. Test Macro: Run your macro manually first to ensure it works
3. Small Analysis: Run a small Bear Decisions analysis to test the integration
4. Monitor Focus: Keep Excel in focus during the test
5. Verify Automation: Confirm the macro runs automatically and analysis continues

Troubleshooting

Next Steps

With automatic macro execution configured, you can now run complex analyses that include custom VBA logic seamlessly. Consider exploring best practices for reliable analysis workflows.

Continue to Best Practices →