COM and threading in VBA

[Nick Palmer]

-- Crossposted on :

microsoft.public.office.developer.vba
microsoft.public.office.developer.clipboard.dde
microsoft.public.vb.ole
microsoft.public.win32.programmer.ole
microsoft.public.excel.programming

since I wasn't sure where the correct place for this was.



Hi all,

I've got a question about COM calls and threading in VBA. At least I think
that that's what the issue I am experiencing is.
Here is the problem that I'm seeing. I've got an Excel VBA application. It
has a primary COM object that the VBA code
instantiates and then calls the public methods and properties of this
object. This all works fine and dandy. Now, what I have
discovered is that we run into a problem if we have two copies of Excel
running at the same time and they are both making calls
to the COM object. It seems like the instance of Excel that I started first
will run and only when its complete, will the other
instance of Excel start running and doing things. It almost seems like its
waiting on the COM calls even though each Excel has its
own copy of the COM object so there is no lock involved there. I have also
noticed strange behavior with the Excel and the clipboard when one copy of
Excel is very
busy doing things with the COM object. In one instance of Excel, it seems
access to clipboard (do a range.Copy for instance) is
locked until a call to the COM object completes in the other running of
Excel. Now, I have found that if I put DoEvents() calls around
the COM object calls, this does change the pattern of behavior with the two
Excels. After the first running copy of Excel has made a COM
call, and a DoEvent call is done, the second copy of Excel will run, until
the first one gets to a point of making another COM call.
Then, once the call is done and the DoEvents call is made, the first copy
will startup again. It seems like DoEvents is releasing
a lock of some kind. The DoEvents call also seems to unlock the clipboard
as well, because the range.Copy call will go thru in one instance
of Excel right after I do the DoEvents call in the other instance of Excel.

Well, I'm not sure this makes much sense, because it doesn't really to me,
but if anyone has any ideas on it, please let me know.

Thanks in advance,
Nick

 

[Neil Scott]

Hi,

I dont know if it will help fellow readers but I have what I think is a
simialr problem. I have a simple bit of code running in powerpoint based on
the example in MSN. This creates a dataobject, set the data and then puts it
in the clipboard.

I can paste this into a textbox on the form in powerpoint BUT, I cant paste
the information into any other application (including excel, notepad) UNTIL I
halt the macro in powerpoint. I added DoEvents() after the code but this
didnt make any difference.

If any one can point a way towards a solution I will be most thankful

Neil