COM and threading in VBA

Nick Palmer · Joined: 04 Oct 2007 Posts: 3

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

Archived from group: microsoft>public>vb>ole

Dick Grier · Joined: 04 Oct 2007 Posts: 272

Hi,

You don't provide any details about your COM object. If it is a DLL, then
in runs "in-process" so you shouldn't see what you see, so I have to assume
that is an EXE. Look at the Instancing property of your public class
module. What is it?

Dick

--
Richard Grier (Microsoft Visual Basic MVP)

See www.hardandsoftware.net for contact information.

Author of Visual Basic Programmer's Guide to Serial Communications, 4th
Edition ISBN 1-890422-28-2 (391 pages) published July 2004. See
www.mabry.com/vbpgser4 to order.

Nick Palmer · Joined: 04 Oct 2007 Posts: 3

Dick,

The COM object can be run as both an in-process DLL or as an out-of-process
EXE. I have tested this problem both ways and got the same results.

Nick

"Dick Grier" wrote in message%23k$eFHA.900@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> You don't provide any details about your COM object. If it is a DLL, then
> in runs "in-process" so you shouldn't see what you see, so I have to
assume
> that is an EXE. Look at the Instancing property of your public class
> module. What is it?
>
> Dick
>
> --
> Richard Grier (Microsoft Visual Basic MVP)
>
> See www.hardandsoftware.net for contact information.
>
> Author of Visual Basic Programmer's Guide to Serial Communications, 4th
> Edition ISBN 1-890422-28-2 (391 pages) published July 2004. See
> www.mabry.com/vbpgser4 to order.
>
>

Dick Grier · Joined: 04 Oct 2007 Posts: 272

DLLs run in-process, so there cannot be any sharing or threading problems
(there aren't any threads created with a DLL). An EXE can be Instanced in
different ways. That's why I suggested that you examine the Instancing
property.

--
Richard Grier (Microsoft Visual Basic MVP)

See www.hardandsoftware.net for contact information.

Author of Visual Basic Programmer's Guide to Serial Communications, 4th
Edition ISBN 1-890422-28-2 (391 pages) published July 2004. See
www.mabry.com/vbpgser4 to order.

Nick Palmer · Joined: 04 Oct 2007 Posts: 3

Now I understand. I'll check and see what the instancing is set to.

The other strange thing that we notice, is that access to the clipboard is
affected as well. What I mean by this is that if one of the Excel/VBA's is
in a COM call, and the other Excel/VBA tries to access the clipboard, say to
do a range.Copy for instance, it hangs until the COM call completes. This
is strange since we are not doing anything with the clipboard in our COM
object.

Nick

"Dick Grier" wrote in message@TK2MSFTNGP12.phx.gbl...
> DLLs run in-process, so there cannot be any sharing or threading problems
> (there aren't any threads created with a DLL). An EXE can be Instanced in
> different ways. That's why I suggested that you examine the Instancing
> property.
>
> --
> Richard Grier (Microsoft Visual Basic MVP)
>
> See www.hardandsoftware.net for contact information.
>
> Author of Visual Basic Programmer's Guide to Serial Communications, 4th
> Edition ISBN 1-890422-28-2 (391 pages) published July 2004. See
> www.mabry.com/vbpgser4 to order.
>
>

Dick Grier · Joined: 04 Oct 2007 Posts: 272

I don't have a clue about the clipboard. This sounds like an Excel funny,
but (not being an Excel user), that's only a guess.

--
Richard Grier (Microsoft Visual Basic MVP)

See www.hardandsoftware.net for contact information.

Author of Visual Basic Programmer's Guide to Serial Communications, 4th
Edition ISBN 1-890422-28-2 (391 pages) published July 2004. See
www.mabry.com/vbpgser4 to order.