CODEWITHSUNDEEP
excelvbaoutlookbloombergxlwings
RAY
RAY

Reputation: 7100

Excel Macro freezes Outlook

I have this rather heavy Excel macro. When it runs Outlook freezes.

I know it is not using all the CPU power because
a) I have a powerful multiple-core machine
b) all my other programs (even heavy-duty ones) are running fine.

It seems like Outlook and Excel are sharing a process that Excel is hogging.

My macro does nothing Outlook-related, at least not knowingly. It pulls data from Bloomberg and does some calculation.

Upvotes: 3

Views: 6963

Answers (4)

diane gerencser
diane gerencser

Reputation: 1

I have the same issue. I avoid other Excel workbooks locking up when I run code in a workbook by opening them in new instances. Note that by default Excel now opens all workbooks in the same instance. For workbooks that I have VBA in and for large workbooks - I always try to open in a new instance to avoid freezing and confusion over where the VBA should run. I do this holding the ALT key down when opening Excel (if another instance is already open) and verify I want to open a new instance. This works for other applications as well.

However - I don't know how to tell Outlook to open in a new instance since it is the only instance of Outlook running. Sometimes Outlook locks up when I run code in Excel and sometimes it doesn't (running the same code). I've had some luck in closing Outlook and opening it back up to get it "detached" from my Excel instance I want to run long running macros in.

Upvotes: 0

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66245

Keep in mind that all calls to the Outlook Object Model are marshaled to the Outlook's main thread, so if your Excel macro is using the Outlook Object Model, Outlook can and will become unresponsive.

Extended MAPI (C++ or Delphi) can be used in a multi-threaded environment.

What does your macro do? Please post the relevant snippet of your code.

Upvotes: 1

Lyon
Lyon

Reputation: 21

The same thing happened to me when migrating my macros to Office 2016. The solution: uncheck the library "Microsoft Office 16.0 Object Library", which is included by default when you create a new vba module.

Upvotes: 2

David Ackroyd
David Ackroyd

Reputation: 76

I appreciate the horse has very much left the corral...however, I too have experienced this and with no references in my code to the Outlook model. I've also noticed my Excel locking up whilst macros in another Excel instance are running.

To resolve this you need add a

DOEVENTS

line to your code, to quote the MSDN site

DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue

Where to put the DOEVENTS line very much depends on your code, but in a rather heavy macro of mine where I make a number of SQL calls, I've placed it just before each SQL call which appears to work well. Allowing me to work quite happily in one Excel instance whilst the background instance does a number of SQL fetches.

Upvotes: 3

Related Questions