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;
        }
    }
}

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!