0

Situation ..

For my project I need to perform some complex operations to the content of some Excel documents. So I thought this would be much more convenient to program the logic in c# rather than in a VBA macro. Since I need this logic in more than just one document, and I need different pieces of this logic in different document, I want to call the c# methods from within a VBA macro.

So ..

I came by this answer and it does perfectly solve the issue. However, it seems that since I need to pass the ThisWorkbook object from VBA into the .net methods, Excel.exe remains active in the background after closing the application (task manager > details). Next time I open Excel, it crashes immediately after which Excel.exe is then closed properly.
This is the only issue I have and I'm very frustrated I can't get it solved. Any ideas?

What I've considered ..

A working (non-perfect) solution is to make a document level VSTO add-in. But I don't get very excited about the need to make small changes and recompile the entire project for each different document. It would be way better if I could centralize the c# logic and require it from within the different documents.

Making an application level VSTO add-in is also undesired, since all users of the company I work for then need to install the VSTO add-in before they can open any document that requires it. This is too much risk and effort. I would rather centralize it, to prevent duplication and also to ensure that always the latest version is called.

My code ..

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelVSTOUtils
{
    [ComVisible(true), ClassInterface(ClassInterfaceType.AutoDispatch)]
    public class Utilities
    {
        public Excel.Workbook _ThisWorkbook { get; private set; }

        // parameterless constructor since vba does not support it
        public Utilities(/*Excel.Workbook ThisWorkbook*/)
        {
        }

        // helper function to set the workbook property after all
        public void Initialize(Excel.Workbook ThisWorkbook)
        {
            _ThisWorkbook = ThisWorkbook;
        }

        // method 1
        public void Method1()
        {
            // doing something complex with _ThisWorkbook ..
        }

        // method 2
        public void Method2()
        {
            // doing something complex with _ThisWorkbook ..
        }
    }
}


'ThisWorkbook object

Private Utilities As New ExcelVSTOUtils.Utilities

Private Sub Workbook_Open()

    Utilities.Initialize ThisWorkbook
End Sub

Sub Method1()

    Utilities.Method1
End Sub

Sub Method2()

    Utilities.Method2
End Sub
Bob Vandevliet
  • 213
  • 3
  • 14
  • 1
    *"Making an application level VSTO add-in is also undesired, since all users ... then need to install the VSTO add-in before they can open any document that requires it."* sounds like the problem isn't the Add-In but the hardcoded call inside the worksheet macro. A VSTO add-in would allow you to de-couple the programming from your worksheet. Reliably publishing the add-in shouldn't be an issue. – Filburt Jan 04 '22 at 12:22
  • I'd suggest you don't use an initialize method. Instead, pass each method the workbook you wish to use. At the end of the method, make sure you set the c# variable to nothing. – freeflow Jan 04 '22 at 12:25
  • Thanks @Filburt, what do you mean by *hardcoded call inside the worksheet macro*? I want to call from vba so I can call different methods in different documents. – Bob Vandevliet Jan 04 '22 at 12:42
  • Thanks @freeflow, I've tried this before, but it didn't solve the issue unfortunately. – Bob Vandevliet Jan 04 '22 at 12:43
  • Then it might be worth checking how your class terminates to make sure that the __ThisWorkbook variable is set to nothing when you quit excel. This is because Excel will happily set Utilities to nothing in the VBA environment but this may well leave a detached process in VSTO space if you don't explicitly set the reference in VSTO space to nothing. – freeflow Jan 04 '22 at 13:05
  • @BobVandevliet I mean you should invoke all your methods from Ribbon buttons of your VSTO add-in to avoid any dependency of your workbook to your app. – Filburt Jan 04 '22 at 13:10
  • It doesn't seem to matter if I call `_ThisWorkbook = null;`, the process remains running in the background. When using a VSTO add-in, my guess is that Excel sets all references to nothing automatically, but a referenced .tlb COM library does not dispose properly. I've also tried a destructor setting _ThisWorkbook null, didn't work. @Filburt, sorry I don't see how ribbon buttons would prevent references to ThisWorkbook in my .net app, since I need that reference. – Bob Vandevliet Jan 04 '22 at 13:29
  • @Filburt, I will give it a try using an app level VSTO COM add-in, I've tested [this](https://learn.microsoft.com/en-us/visualstudio/vsto/walkthrough-calling-code-in-a-vsto-add-in-from-vba?view=vs-2022) and it works, so I have hope here. Then I will try to add some vba to the workbook to verify if the add-in is installed. Thanks all for your support! – Bob Vandevliet Jan 06 '22 at 07:44

1 Answers1

0

So I decided to go with an application level Add-In and check in a Workbook_Open Sub in each Workbook that requires it if the Add-In is installed before continuing. To achieve this, I've followed this tutorial and it works perfectly. However, this is application level, but I need class instances that are bound to specific workbooks.

The solution:

public partial class ThisAddIn
{
    private AddInUtilities utilities;

    protected override object RequestComAddInAutomationService()
    {
        if (utilities == null)
            utilities = new AddInUtilities();

        return utilities;
    }
}

[ComVisible(true), ClassInterface(ClassInterfaceType.None)]
public class AddInUtilities
{
    public WbVSTO GetWbVSTO()
    {
        return new WbVSTO(Globals.ThisAddIn.Application.ThisWorkbook);
    }
}

[ComVisible(true), ClassInterface(ClassInterfaceType.None)]
public class WbVSTO
{
    private Excel.Workbook _ThisWorkbook;

    public WbVSTO(Excel.Workbook ThisWorkbook)
    {
        _ThisWorkbook = ThisWorkbook;
    }
}

'ThisWorkbook object

Private AppVSTO As Object
Private WbVSTO As Object

Private Sub Workbook_Open()

    Set AppVSTO = Application.COMAddIns("AppVSTO").Object
    Set WbVSTO = AppVSTO.GetWbVSTO()
End Sub

What is very important is to make all classes that need to be exposed to Excel ComVisible.

Bob Vandevliet
  • 213
  • 3
  • 14