C# DLL with [ComSourceInterfaces] Attribute Not Working in VBA WithEvents

Question

I am trying to use a C# DLL in VBA with events using the [ComSourceInterfaces] attribute, but I am encountering issues. Here's what I have so far:

C# Code

ITaskRunnerEvents.cs

using System;
using System.Runtime.InteropServices;

namespace ComEventTest
{
    [ComVisible(true)]
    [Guid("c8614250-4291-4fb0-8b45-4aa305b0c595")]
    [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface ITaskRunnerEvents
    {
        void OnTaskCompleted(string result);
    }
}

TaskRunner.cs

using System.Runtime.InteropServices;
using System.Threading.Tasks;

namespace ComEventTest
{
    [ComVisible(true)]
    [Guid("ac9de195-73e8-44ae-8cf1-d8f110421923")]
    [ClassInterface(ClassInterfaceType.None)]
    [ComSourceInterfaces(typeof(ITaskRunnerEvents))]
    public class TaskRunner
    {
        public delegate void TaskCompletedEventHandler(string result);
        public event TaskCompletedEventHandler OnTaskCompleted;

        public void RunTask(string input)
        {
            Task.Run(async () =>
            {
                await Task.Delay(5000); // Simulate work
                OnTaskCompleted?.Invoke($"Task completed with input: {input}");
            });
        }
    }
}

VBA Code

Class Module: TaskRunnerEventHandler

Option Compare Database
Option Explicit

Public WithEvents taskRunner As ComEventTest.taskRunner

Private Sub taskRunner_OnTaskCompleted(ByVal result As String)
    MsgBox result
End Sub

Public Sub InitializeTaskRunner()
    Set taskRunner = New ComEventTest.taskRunner
End Sub

Usage Module

Sub TestTaskRunner()
    Set eventHandler = New TaskRunnerEventHandler
    eventHandler.InitializeTaskRunner
    eventHandler.taskRunner.RunTask "Test Input"
End Sub

Problem

1. When I run eventHandler.taskRunner.RunTask "Test Input" in the TestTaskRunner subroutine, I get the error:

Method or data member not found

2. I am unsure if I am correctly using the [ComSourceInterfaces] attribute in the C# code.

Answer 1

Finally, I found a solution to the issue of handling events from a C# COM object in VBA using the WithEvents keyword. Below is the code and explanation that helped me resolve the problem:

C# Code :

In the C# code, I implemented a COM object that raises an event (OnTaskCompleted) when a task is completed. The key part is the use of the [ComSourceInterfaces] attribute, which allows the COM object to expose the event to VBA.

TaskRunner.cs

using System;
using System.Runtime.InteropServices;
using System.Threading.Tasks;
using System.Collections.Concurrent;

namespace ComEventTest
{
    [Guid("cc6eeac0-fe23-4ce4-8edb-676a11c57c7c")]
    [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface ITaskRunner
    {
        [DispId(1)]
        void RunTask(string input);
    }

    [Guid("619a141c-5574-4bfe-a663-2e5590e538e2")]
    [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface ITaskRunnerEvents
    {
        [DispId(1)]
        void OnTaskCompleted(string result);
    }

    [ComVisible(true)]
    [Guid("9acdd19f-b688-48c0-88d9-b81b7697d6d4")]
    [ClassInterface(ClassInterfaceType.None)]
    [ComSourceInterfaces(typeof(ITaskRunnerEvents))]
    public class TaskRunner : ITaskRunner
    {
        [ComVisible(true)]
        public delegate void TaskCompletedEventHandler(string result);

        [DispId(1)]
        public event TaskCompletedEventHandler OnTaskCompleted;

        private ConcurrentQueue<string> taskQueue = new ConcurrentQueue<string>();
        private bool isProcessingQueue = false;

        public void RunTask(string input)
        {
            taskQueue.Enqueue(input);
            ProcessQueue();
        }

        private async void ProcessQueue()
        {
            if (isProcessingQueue)
                return;

            isProcessingQueue = true;

            while (taskQueue.TryDequeue(out string input))
            {
                try
                {
                    await Task.Delay(5000); // Simulate work
                    OnTaskCompleted?.Invoke($"Task completed with input: {input}");
                }
                catch (Exception ex)
                {
                    OnTaskCompleted?.Invoke($"Task failed: {ex.Message}");
                }
            }

            isProcessingQueue = false;
        }
    }
}

ComEventTest.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Library</OutputType>
    <TargetFramework>net48</TargetFramework>
    <AssemblyTitle>VbaEventLibrary</AssemblyTitle>
    <ComVisible>true</ComVisible>
    <LangVersion>8.0</LangVersion> <!-- Specify C# version 8.0 -->
    <SignAssembly>true</SignAssembly>
    <AssemblyOriginatorKeyFile>ComEventTestKey.snk</AssemblyOriginatorKeyFile>
  </PropertyGroup>

</Project>

Note: For VBA usage, you need to ensure the following steps are completed:

1. Create a Strong Name for Your COM Component

   • Use the sn.exe tool to generate a strong name key file for your assembly.
     sn -k YourKeyName.snk
   • Apply the strong name to your project by signing the assembly with the .snk file. You can configure this in the AssemblyInfo file or in your project settings under Signing.

2. Register the COM Component in the Registry

   • Use the Regasm.exe tool to register the COM component. regasm ComEventTest.dll /codebase /tlb:ComEventTest.tlb
   • Ensure you use the /codebase flag if your assembly is not in the Global Assembly Cache (GAC)

VBA Code :

In the VBA code, I used the WithEvents keyword to handle the OnTaskCompleted event. This allows VBA to listen for and process events raised by the C# COM object. I also created an event handler class (TaskRunnerEventHandler) to handle the event and process the results.

Class Module: TaskRunnerEventHandler

Option Compare Database
Option Explicit

Public WithEvents taskRunner As ComEventTest.taskRunner

Private Sub taskRunner_OnTaskCompleted(ByVal result As String)
    'MsgBox result
    Debug.Print result
End Sub

Public Sub InitializeTaskRunner()
    Set taskRunner = New ComEventTest.taskRunner
End Sub

Public Sub FireEvent(poraka As String)
    taskRunner.RunTask poraka
End Sub

Usage module

Option Compare Database
Option Explicit

Dim eventHandlers As Collection

Sub InitializeEventHandlers()
    Set eventHandlers = New Collection
End Sub

Sub TestTaskRunner(Optional retr As String)

    If eventHandlers Is Nothing Then
        InitializeEventHandlers
    End If

    Dim newEventHandler As TaskRunnerEventHandler
    Set newEventHandler = New TaskRunnerEventHandler

    newEventHandler.InitializeTaskRunner

    eventHandlers.Add newEventHandler

    Dim i As Integer
    For i = 1 To 10
        newEventHandler.FireEvent "Task " & retr & "-" & i
        Sleep 100 ' Simulate delay for async task running
        Debug.Print "Task " & retr & "-" & i & " is running asynchronously!"
    Next i
    
End Sub

Sub TestTaskRunner_MultCalls()
    ' Fire multiple calls to TestTaskRunner
    Dim i As Integer
    For i = 1 To 10
        Debug.Print "New CALL SUB fire " & i
        TestTaskRunner CStr(i)
        Sleep 500 ' Simulate delay between multiple calls
    Next i
End Sub

Explanation:

The C# COM object exposes an event (OnTaskCompleted) that is triggered after completing a task asynchronously.

In VBA, I used the WithEvents keyword to declare the COM object and catch the (OnTaskCompleted) event. This allows me to process the result of each task in the taskRunner_OnTaskCompleted method. I also simulated multiple task submissions in the VBA code using Sleep to delay the execution and give time for the events to be raised and handled.

This solution worked, and now I can handle asynchronous events from the C# COM object seamlessly in VBA.

Any ideas to improve the above solution are welcome!