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

Continue to Strategies & Timings →