Our company upgraded to office 2013 about 6 months ago. I have pre-existing code that imports CSV files, performs calculations, and updates graphs. Since the 2013 upgrade, when running Excel VBA code, other Office 2013 programs (e.g., Word, Outlook) become non-responsive to mouse or keyboard input until the VBA code completes. Interestingly PowerPoint does not appear affected. In my VBA code i use typical speed-up techniques: application.windowstate=xlMinimized, use manual calculations, and turn screen updating off. I had come across one page in stack overflow (Application.Screenupdating = false affects all of office) that indicated screen updating set to false was a potential problem. I tried following the suggestions from that link and numerous others to modify my code so Word and Outlook are still functional while the macros are running. This includes using "Excel." prefix (e.g., Excel.Application.ScreenUpdating=False), leaving window maximized, using "Excel.Application.Windows(ThisWorkbook.Name).WindowState =xlMinimized, and so forth. Nothing i have tried has eliminated the effect of Excel VBA on the other Office applications. i don't understand fundamentally why office is (apparently) so intertwined that one app can adversely affect others. I did not see this kind of effect in Office 2010 or earlier versions. Any helpful insight would be much appreciated.
2 Answers
After quite a bit of debugging I finally realized why my VBA code was inhibiting response in Word/Outlook. I never use the Office clipboard but for some reason i opened it in Word while working in Excel, and saw that there was evidence of copied data, including charts. Although i had previously cleaned up the code to try to eliminate copy/paste (i.e, using the Clipboard) there were two areas in which this had not been done. The harder of the two to change was in the area of chart updates. Being lazy, i had developed the code for chart update in a way that it always required at least one pre-existing chart on the pages on which embedded charts are located. The technique i had used was to copy the top chart, delete ALL charts on the page, paste the copied chart into the top position, then duplicate this for all chart locations. Then code runs to re-map the source data from a worksheet with all the data to be plotted. I modified the code to delete all but the first chart, then duplicate. This avoids the use of .Copy. Once i had cleaned up all use of the clipboard, i was able to run the VBA code in Excel and without interfering with normal usage of Word or Outlook. As i mentioned, I never have been one to use the Office Clipboard; the old Windows clipboard has been fine by me. In my code i have been diligent to clear the clipboard following a copy/paste, using Excel.Application.CutCopyMode = False (or, just Application.CutCopyMode = False). I thought this would prevent any problems regarding interference with other programs and until we began using Office 2013, i had not seen any issues. But, even though this clears the Windows clipboard, it does not clear the Office clipboard. I reviewed many web pages trying to find a way to clear the Office clipboard and none of them did. It seems the ability to clear the Office clipboard has been gone since 2000. I found this to be a helpful reference, as i tried the code listed here and other similar variations to clear the Office clipboard, but they all only clear the Windows clipboard: http://www.pcreview.co.uk/threads/vba-clear-clipboard.2237563/. I hope this is helpful to others who may have had similar experience.
One more thing... while editing in Word, i found that it worked fine until i copied a figure (in Word) while the Excel VBA code was running. At this point, Word became unresponsive again. Since i had already eliminated all use of the clipboard, i rechecked the VBA code and found there were still places where Excel.Application.CutCopyMode = False was being executed. I commented out all these lines and repeated the test. Now with the Excel VBA running, i can edit in Word with no interference, including copy/paste.
Add-ins. Disabled all add-ins in Word. Now my Excel VBA can run all day and not cause Word to choke. I have done some experimentation to try to narrow down which Add-In. I believe Acrobat PDFMaker Office Com Addin is the culprit. I have seen that referenced as a possibly affecting VBA in trying to research this issue, before i posted this question on Stack Overflow. I had discounted this Add-In as being the problem source, because we include Acrobat PDFMaker in Power Point as well; and i had not observed Power Point being 'frozen' when running Excel VBA as i had in Word and Outlook. However, after disabling PDFMaker in Outlook, it now also appears clean, no longer hung up by Excel executing VBA.
