Catch VBA Errors in VB.Net/VSTO-CodePudding

I have search on this site and beyond for an answer to these questions, and also trawled through a collection of VB/C# books I have on VB/VSTO. So far I've drawn a blank. I've posted the same question on a VB.NET facebook group, and if I get a solution, I'll post it here so it'll help others.

I am developing an application-level Add-In for Excel using VB.Net & VSTO.

Part of the functionality involves opening .xlsm (Excel macro-enabled) files. I have three questions I hope you can help me with.

I want to open each xlsm file with macros enabled, but not launch any Auto_Open macro in the xlsm file, or trigger the Workbook_Open event. Is that possible?
The xlsm file may have it's own ribbon attached to the file. Is it possible to inhibit the xlsm's ribbon from being added?
The xlsm file may have ActiveX controls that are connected to VBA macros. If a VBA macro produces an error, is it possible to catch the error in the .NET Add-In? The error may include "macro not found"

For info, at present I disable macros using the code snippet below. Whilst that helps with item #1, it doesn't help with items #2 or #3 (in fact, disabling macros is the cause of item #3).

Currently when I open the xlsm file(s) I disable macros using the following code:

' WorkbookType is a string representing a keyword within the Excel filename

        ' xlApp is running instance of Excel
        Dim xlApp = Marshal.GetActiveObject("Excel.Application")
        aWorkbook = xlApp.ActiveWorkbook
        aSheet = xlApp.ActiveSheet

        ' Save current macro security setting
        Dim oldSecurity = xlApp.AutomationSecurity

        ' Disable macros
        xlApp.AutomationSecurity = 
        Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable

        ' Find & open WorkbookType workbook files in ProjectFolder
        Dim info As New DirectoryInfo(CurrentProjectFolder)
        For Each fileItem As IO.FileInfo In info.GetFiles
                If fileItem.Name.Contains("~") Then
                ' Ignore temporary files
                ElseIf fileItem.Name.Contains(WorkbookType) AndAlso fileItem.Name.Contains(".xls")             Then
                        xlApp.AskToUpdateLinks = False
                        Dim wb = xlApp.Workbooks.Open(CurrentProjectFolder & "\" & fileItem.Name, UpdateLinks:=False)
                        xlApp.AskToUpdateLinks = True
                End If
        Next

        ' Restore macro security setting
        xlApp.AutomationSecurity = oldSecurity

CodePudding user response：

The xlsm file may have ActiveX controls that are connected to VBA macros. If a VBA macro produces an error, is it possible to catch the error in the .NET Add-In? The error may include "macro not found"

COM add-ins (represented by VSTO add-ins) and VBA macros are entirely different entities. You can't handle VBA errors in COM add-ins, or the opposite. But you may react to the application events, see Object model (Excel) for the list of available events.

The xlsm file may have it's own ribbon attached to the file. Is it possible to inhibit the xlsm's ribbon from being added?

To prevent the custom ribbon UI from loading you need to edit the file by removing the ribbon XML customizations contained inside the Excel file. VBA doesn't deliver any UI customizations nowadays. You can use the Open XML SDK for editing open XML documents on the fly from VSTO add-ins without involving the host application or its object model.