Force addin load in Excel /automation

Question

I have an Excel Automation addin providing a set of UDFs. I want to get those UDFs working even when opening Excel via the Interop APIs. I know that when opening Excel via the Interop APIs Excel is started with the parameter /automation which makes Excel not load addins. To counter this I added the following Workbook_Open() event:

Private Sub Workbook_Open()
    Application.AddIns("SomeApp.DemoAddin").Installed = False
    Application.AddIns("SomeApp.DemoAddin").Installed = True
End Sub

This code successfully loads the addin. I verified that using some logging in the addin.

I put together a test workbook that calls the UDF =myDemoFunction(). When opening that workbook via "C:\path\to\EXCEL.exe" /automation C:\path\to\workbook.xlsm I observe the following behavior. At first the cell with the UDF displays the correct result ("15.0" in this case). The formula displays as =SomeApp.DemoAddin.myDemoFunction() though, which is usually only the case when the addin is not loaded. When doing a recalculation with CTRL+ALT+F9 or doing an Application.CalculationFullRebuild in VBA or even marking all filled cells as dirty using ThisWorkbook.Worksheets(1).UsedRange.Dirty all cause the cells with the UDF to display #NAME?. When editing one of the UDF cells (select, F2, Return) then all UDFs on that one sheet (not on other sheets also using that UDF) suddenly return the correct result again and the formula changes to =myDemoFunction() again.

So my question is, how can I trigger Excel to recognize the addin programmatically?

My code base is a lot bigger, but below is a stripped down version of the Automation addin showing this behavior (in VB.net):

Imports Extensibility
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel

<GuidAttribute("352B1C10-DC5A-4BF8-9D31-DB9913B07364"),
ProgIdAttribute("SomeApp.DemoAddin"),
ClassInterface(ClassInterfaceType.AutoDual)>
Public Class DemoAddin
    Implements IDTExtensibility2

    Private excelApp As Excel.Application

    Public Sub OnBeginShutdown(ByRef custom As Array) Implements IDTExtensibility2.OnBeginShutdown
    End Sub
    Public Sub OnAddInsUpdate(ByRef custom As Array) Implements IDTExtensibility2.OnAddInsUpdate
    End Sub
    Public Sub OnStartupComplete(ByRef custom As Array) Implements IDTExtensibility2.OnStartupComplete
    End Sub

    Public Sub OnDisconnection(RemoveMode As ext_DisconnectMode, ByRef custom As Array) Implements IDTExtensibility2.OnDisconnection
        Marshal.ReleaseComObject(excelApp)
        excelApp = Nothing
    End Sub

    Public Sub OnConnection(application As Object, connectMode As ext_ConnectMode, addInInst As Object, ByRef custom As Array) Implements IDTExtensibility2.OnConnection
        excelApp = CType(application, Excel.Application)
    End Sub

    Public Function myDemoFunction() As String
        Return excelApp.Version
    End Function
End Class

Patrick Böker Patrick Böker · Accepted Answer · 2019-10-28T14:37:11

Calling all the UDFs that are broken from VBA suffices. So an updated Workbook_Open() does the trick. The code below also does some probing to not reload the addin needlessly, but that is not necessary. The real magic is in the second last line Application.Run "myDemoFunction".

Private Sub Workbook_Open()
    ensureAddinLoaded
End Sub

Public Sub ensureAddinLoaded()
    Debug.Print "Probing for addin"
    On Error GoTo NotLoaded
    Application.Run "myDemoFunction"
    Exit Sub
NotLoaded:
    Debug.Print "Addin not loaded. Reloading."
    Application.AddIns("SomeApp.DemoAddin").Installed = False
    Application.AddIns("SomeApp.DemoAddin").Installed = True
    Application.Run "myDemoFunction"
End Sub

This solution requires knowing what exect UDFs there are, so it's suboptimal.

Force addin load in Excel /automation

1 Answers