Is it possible for xlam Excel addin to override a sub from a workbook?

Question

The xlam Excel addin I'm building is interacting with externally sourced spreadsheet. The trouble is that the sheet, among other things in Workbook Change event has code that throws a MsgBox popup, messing with automation as it waits for user input. The popup is not called directly - the Workbook Change event is calling a custom Sub called "ShowPopup". Then inside the "ShowPopup" we have MsgBox call.

My thought was that if I could override the ShowPopup sub, I could prevent it from creating the MsgBox. Is it possible to do?

Constraints to the potential solutions:

Fully overriding the Workbook Change event is a no-go, there is stuff there that's actually needed.
I can't make any modifications to the code of the externally sourced sheet that the xlam Excel addin is interacting with
Application.DisplayAlerts = False doesn't work for explicitly called MsgBox'es

Potentially you could (at runtime) remove the Msgbox call from the other workbook (via the VBA extensibility library) but that might be tricky to manage. Short of that (or Windows API calls to monitor for and dismiss the Messagebox) I'm not sure there's much you could do. — Tim Williams
@TimWilliams would you be able to point me in the right direction on how to remove that "ShowPopup" Sub using the VBA extensibility library? — LucasSeveryn
If this wasn't an add-in and could be invoked from the IDE, Rubberduck's unit testing Fakes API could be abused to do this... but invoking a Rubberduck test method in regular VBA code will not hook up the fake - the test engine does. We use EasyHook to hook into the VBA libraries and -yes, override MsgBox calls. So... it's technically possible, yes. — Mathieu Guindon
@MathieuGuindon I don't want to override MsgBox, I want to override a custom sub called "ShowPopup" that inside has a call to MsgBox. I can't edit the VBA of the file itself, but I have my .xlam addin that is used to assist with interaction with that file. Ideally I want to replace the "ShowPopup" sub that's inside the file with one that's inside the .xlam addin. — LucasSeveryn

LucasSeveryn LucasSeveryn · Accepted Answer · 2019-03-29T09:18:47

I managed to solve my problem, although not in the way I originally planned. In the end it involves overriding the MsgBox Function.

For future reference here are the steps.

In any of .xlam addin modules, create a global variable Global macroRunning As Boolean
In the sub where automation loop sits, we put this before the loop: macroRunning = True and opposite after the loop.

Create a new module, I called it MsgBoxHack and put this code inside:

Public Function MsgBox( _
Prompt, _
Optional Buttons As VbMsgBoxStyle = vbOKOnly, _
Optional Title, _
Optional HelpFile, _
Optional Context _
) _
As VbMsgBoxResult

  If IsMissing(Title) Then
      Title = "Overriden MsgBox"
  End If

  If macroRunning = False Then
      MsgBox = VBA.Interaction.MsgBox(Prompt, Buttons, Title, HelpFile, Context)
  End If

End Function

That's it, when your global variable macroRunning is set to True you won't see any MsgBoxes

Downside - This overriden MsgBox function will run on all workbooks you work on while the addin is enabled in Excel.

Is it possible for xlam Excel addin to override a sub from a workbook?

1 Answers