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

Tech-Archive recommends: Speed Up your PC by fixing your registry

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

.

Relevant Pages

  • Re: Alternatives to call back a vba function from a (worker) thread
    ... I write them into excel through a callback function ... function from my worker thread. ... I did not find any other information on how to callback a vba function from ...
    (microsoft.public.office.developer.com.add_ins)
  • Re: Detect Excel application minimize/restore?
    ... callback into the userform. ... class that set the hook at app startup, ... I appreciate this hook is safer than conventional subclass & callback. ... if the code is in plain view the user could crash Excel by adding ...
    (microsoft.public.vb.general.discussion)
  • Re: Run time error
    ... The .SpecialCellsVBA function does not work as expected ... "Richard" wrote in message ... >>>>See:>>>>Microsoft Knowledge Base Article - 832293>>The .SpecialCellsVBA function does not> work as expected>>in Excel>> ... >>> inputagain: Exit Sub ...
    (microsoft.public.excel.worksheet.functions)
  • Re: in Excel per VBA =?iso-8859-1?Q?pr=FCfen,_ob_irgend_etwas?= =?iso-8859-1?Q?_gedr
    ... wenn in Excel ein Worksheet leer ist, ... > kommenden Callback vom Drucker. ... Du machst zunächst einen Testdruck in eine Datei mit folgendem Code ...
    (microsoft.public.de.excel)
  • Re: Using Instances of a Class
    ... Usually I put these variables in spreadsheet cells and call the ... an object of my class from a function call in excel. ... But you can pass them to a VBA function. ... wich have it`s own properties and functions. ...
    (microsoft.public.excel.programming)