option to disable machine hive (i.e. assume always non-elevated) + pre-register ribbon [in installer]

[jmkinzer]

The ribbon portion of the addin causes excel to crash on various corporate machine running 365. It's related to the ribbon registration although it's hard to say what the root-cause is (after monkeying around the error tends to vanish and then cannot be re-introduced). I believe this suggestion however is along the lines need to resolve it:
#317 (comment)

These two steps would probably prevent any possible problems here:

1. These machines are never allowed to run excel elevated so an option to disable CanWriteMachineHive would be helpful
2. I already register the main addin using an installer and the add-in then never fails if the installer succeeds - only the dynamic registrations are a problem. If there was a function I could call from the installer to mimic the dynamic registration this should head off any runtime issues

[govert]

Hi @jmkinzer - we've already implemented step 1 with the optional machine hive check (only when running as admin) for the next version 1.8.0. Maybe you can try the current pre-release version 1.8.0-alpha3 and confirm that it works right for you.

Step 2 is a bit more tricky - It can be done with the current library, but you need to configure the ribbon a bit differently:

1. Set up the project with the ribbon to not load automatically, but instead be published with Excel-DNA as a 'ComServer'.
2. Call regsvr32 to register the COM class
3. Add the required registry entries to Excel for the ribbon ProgId to be a known COM AddIn
4. 'Connect' the ComAddIn when the add-in is loaded

Each of these needs a bit more explanation. I've published a test add-in with the below code here https://github.com/govert/TestRibbonRegistration but you have to do the registration steps yourself.

1. In the project file, you need a property to indicate you will by exposing types as COM classes through the Excel-DNA .xll add-in:

    <ExcelAddInComServer>true</ExcelAddInComServer>

Then your ribbon class would not derive from ExcelRibbon as usual, but instead just be an ExcelComAddIn which implements IRibbonExtensibility explicitly:

using System;
using System.Runtime.InteropServices;
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using Microsoft.Office.Interop.Excel;

namespace TestRibbonRegistration
{
    internal class ComConstants
    {
        // Choose your own ProgId and a new Guid here
        public const string RibbonProgId = "TestRibbonRegistration.MyCustomRibbon";
        public const string RibbonGuid = "E595901B-903E-4FE7-8D06-6E7F3D5A2C4F";

        // This is the Guid for the IRibbonExtensibility interface - don't change this
        public const string IRibbonExtensibilityGuid = "000C0396-0000-0000-C000-000000000046";
    }

    [ComVisible(true)]
    [ComImport]
    [Guid(ComConstants.IRibbonExtensibilityGuid)]
    [InterfaceType(ComInterfaceType.InterfaceIsDual)]
    internal interface IRibbonExtensibility
    {
        [DispId(1)]
        string GetCustomUI(string RibbonID);
    }

    [ProgId(ComConstants.RibbonProgId)]
    [Guid(ComConstants.RibbonGuid)]
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDispatch)]
    public class MyCustomRibbon : ExcelComAddIn, IRibbonExtensibility
    {
        public string GetCustomUI(string RibbonID)
        {
            return 
                @"<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui'>
                  <ribbon>
                    <tabs>
  
                      <tab id='tab1' label='My Tab'  >
                        <group id='group1' label='My Group'>
                          <button id='button1' label='My Button' onAction='OnButtonPressed'  />
                        </group >
                      </tab>
                    </tabs>
                  </ribbon>
                </customUI>";
        }

        public void OnButtonPressed(IRibbonControl control)
        {
            var app = (Application)ExcelDnaUtil.Application;
            app.StatusBar = "Button Pressed!";
        }
    }
}

2. In your installer you would need to call "regsvr32 MyAddIn.xll" or "regsvr32 MyAddIn64.xll" or do the equivalent. This calls the DllRegisterServer() export of the x.ll add-in, which writes (to the user hive) the PorgId / ClsId etc. registration for the COM classes exposed by the add-in (i.e. the MyCustomRibbon class). It registers the .xll file as the 'InProcServer32' so this is quite different from the usual COM registration for .NET classes (which use mscoree.dll as the server).

In my test I just run from a (non-admin) command line:

PS C:\Temp\TestRibbonRegistration\bin\Debug\net472> regsvr32 .\TestRibbonRegistration-AddIn64.xll

3. Your installer will then need to add the ProgId as a known COM Add-In to the Excel part of the registry. To the key HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\Excel\Addins you add a new key with the ProgId name, and I think at least the LoadBehavior value. The Description and FriendlyName are optional, and appear in the Excel dialog boxes.
   

4. Loading the ribbon when your .xll add-in loads can now be done from an AutoOpen() handler, though you still need to take some care. You should avoid doing it directly in the AutoOpen but instead run as a macro after initialization.

using ExcelDna.Integration;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;

namespace TestRibbonRegistration
{
    public class AddIn : IExcelAddIn
    {
        public void AutoOpen()
        {
            // It's better if we don't load other add-ins during the AutoOpen method
            // So we schedule the ribbon load to run after the add-in is initialized, 
            // when Excel is idle again.
            ExcelAsyncUtil.QueueAsMacro(() => RegisterRibbon());
        }

        public void AutoClose()
        {
        }

        public void RegisterRibbon()
        {
            var app = (Application)ExcelDnaUtil.Application;
            var progId = ComAPI.RibbonProgId;
            // Enumerate the registered add-ins, and load the one with the right ProgId
            foreach (COMAddIn addIn in app.COMAddIns)
            {
                if (addIn.ProgId == progId)
                {
                    addIn.Connect = true;
                    break;
                }
            }
        }
    }
}

[jmkinzer]

@govert - This is fantastic! I really appreciate your taking the time to put this together. I have upgraded to 1.8.0a3 and will now give this approach a try. Thanks again.

[jmkinzer]

@govert - I confirm this approach resolves the issues. Thanks again!

The one major headache I had revolved around Wix (which I've decided I hate) and COM server registration. This was related to the fact that while I could easily get the DllRegisterServer approach to work, when using Wix that failed in the installer for some reason while trying to just replicate the registry entries never worked out.

I skimmed articles about running heat and harvesting reg entries but I couldn't master the arcane Wix incantations so to end the pain quickly I just whipped up a self-contained .net app that performs all the various registrations and can be called out from Wix or just run standalone. Not sure if this is a totally kosher approach but it seems to work flawlessly in my environment (and at least I can troubleshoot it).

Attaching the various helpers in case it's useful.
Helpers.zip

var folderPath = "path/to/addin/folder";
var fileName = "myaddin.xll";

ComAddinHelpers.SelfRegisterComServer(folderPath, fileName, (m) => Console.WriteLine(m));
ComAddinHelpers.RegisterComAddin(ComConstants.RibbonProgId, "MyRibbon", (m) => Console.WriteLine(m));
AddinHelpers.RegisterAddIn(folderPath, fileName, true);

var isInstalled = ExcelHelpers.VerifyAddinInstalled(fileName);
Console.WriteLine($"VerifyAddinInstalled: {isInstalled}");

var isInstalled2 = ExcelHelpers.VerifyComAddinRegistered(ComConstants.RibbonProgId);
Console.WriteLine($"VerifyComAddinRegistered: {isInstalled2}");

(Note I was only concerned with x64 installs but it could be extended easily to support both)
(Note 2: I should mention I am using .net 8 so not sure if that somehow complicated my life since the Wix CA is .net framework 4.8)

[jmkinzer]

And fwiw if one decides to make a standalone exe to be called by Wix the following should help. It would definitely be better to have this as just part of the CA but as noted I had difficult-to-debug issues with the ComServer registration with that approach...

...

    <CustomAction Id="EXECUTE_ADD_AFTER_FINALIZE"
      Execute="immediate"
      Return="asyncNoWait"
      FileRef="registrar.exe"
      ExeCommand="add"
    />
    <CustomAction Id="EXECUTE_REMOVE_BEFORE_FINALIZE"
      Execute="immediate"
      Return="asyncNoWait"
      FileRef="registrar.exe"
      ExeCommand="remove"
    />

...

    <InstallExecuteSequence>
      <Custom Action="EXECUTE_ADD_AFTER_FINALIZE" After="InstallFinalize" Condition="NOT Installed" />
      <Custom Action="EXECUTE_REMOVE_BEFORE_FINALIZE" Before="RemoveFiles" Condition="Installed AND NOT UPGRADINGPRODUCTCODE AND NOT REINSTALL" />
    </InstallExecuteSequence>

...

      <Component Id="registrar" Guid="{7e42fa34-0d0d-4817-92e9-1334237b6acf}" Directory="AddinFolder">
        <RegistryValue Root="HKCU" Key="Software\!(loc.ProductManufacturer)\!(loc.ProductNameNoSpace)Registrar" Name="registrar" Value="Helpers" KeyPath="yes" Type="string" />

        <File Id="registrar.exe" Source="YourRegistrationExeHere.exe">
        </File>

        <RemoveFile On="uninstall" Id="registrar" Directory="AddinFolder" Name="Platform.AddIn.InstallationEngine.exe" />
      </Component>