Alternatives to call back a vba function from a (worker) thread

[jj_p]

Hi,

I have a ATL/COM/C++ add-in using IDTExtensibility2.
My add-in catches events from an external software. To do so I needed to
implement a thread loop.
Once the data captured, I write them into excel through a callback function
in vba. I used AddressOf to pass the reference of the function to my add-in.

If I callback the vba function from the main thread, it works fine (I have
implemented the IMessageFilter for when Excel is busy).
I know that Excel is STA and therefore do no support (straight away?) a call
from a worker thread. But Ideally I would have liked to call this vba
function from my worker thread. (If I do so, Excel hangs or crashes...)

I have no idea how I can overcome the problem. But I see three possibilities
for which I have not found information I needed.
1) Maybe it is possible to define my callback so that it is not a function
pointer?
At the time being, I do something like
----------- vba code
Public Function MyVBACallback(ByVal cVal As String) As Integer
....
End Function

.... 'Register my callback
MyAddIn.RegisterCallback AddressOf MyVBACallback
---------- C++ code
STDMETHODIMP CMyAddIn::RegisterCallback (VARIANT myVbaCallback){
myCallback_ = (MYFUNCTIONPOINTER)myVbaCallback.byref;
}

.... //Call my callback
(*myCallback_)(myData);

This is far nor not ideal. This function pointer is not safe at all. But
I did not find any other information on how to callback a vba function from
my COM/C++ add-in.


2) Maybe it is possible to call my vba function from a worker thread with
the correct use/implementation of some com interface?

3) Another alternative is to "hook" the main thread message loop so I can
pull the data from my worker thread into the main thread and to call excel
from this main thread on the data update. But I don't know how. I don't know
where to look at to find information on that if it is possible.



Hope I was clear enough (and I hope it is the correct forum).



Many thanks

 

[Scott McPhillips [MVP]]

Hi,

I have a ATL/COM/C++ add-in using IDTExtensibility2.
My add-in catches events from an external software. To do so I needed to
implement a thread loop.
Once the data captured, I write them into excel through a callback
function
in vba. I used AddressOf to pass the reference of the function to my
add-in.

If I callback the vba function from the main thread, it works fine (I have
implemented the IMessageFilter for when Excel is busy).
I know that Excel is STA and therefore do no support (straight away?) a
call
from a worker thread. But Ideally I would have liked to call this vba
function from my worker thread. (If I do so, Excel hangs or crashes...)

I have no idea how I can overcome the problem. But I see three
possibilities
for which I have not found information I needed.
1) Maybe it is possible to define my callback so that it is not a function
pointer?
At the time being, I do something like
----------- vba code
Public Function MyVBACallback(ByVal cVal As String) As Integer
....
End Function

.... 'Register my callback
MyAddIn.RegisterCallback AddressOf MyVBACallback
---------- C++ code
STDMETHODIMP CMyAddIn::RegisterCallback (VARIANT myVbaCallback){
myCallback_ = (MYFUNCTIONPOINTER)myVbaCallback.byref;
}

.... //Call my callback
(*myCallback_)(myData);

This is far nor not ideal. This function pointer is not safe at all.
But
I did not find any other information on how to callback a vba function
from
my COM/C++ add-in.


2) Maybe it is possible to call my vba function from a worker thread with
the correct use/implementation of some com interface?

3) Another alternative is to "hook" the main thread message loop so I can
pull the data from my worker thread into the main thread and to call excel
from this main thread on the data update. But I don't know how. I don't
know
where to look at to find information on that if it is possible.



Hope I was clear enough (and I hope it is the correct forum).



Many thanks

The usual solution to this problem is to create an invisible window in your
main thread. The existing Excel message pump should forward messages to
that window for you. (I use a modeless dialog derived from CAxDialogImpl
and it works just fine in a Word addin.)

The worker thread can post messages to the invisible window, and then your
message handler can make your callback from the main thread. You can pass
any data you like with the message by allocating a heap struct in the worker
thread, then passing its address as WPARAM or LPARAM in PostMessage, then
processing and deleting the struct in the main thread message handler.

 

[jj_p]

Thank you