Prevent working in separate Excel session interrupting macro

Hi all,

Very inexperienced with VBA, I'm afraid!

I have a code to generate several thousand files which I need to re-run. The last time I ran the code, I noticed that it would be interrupted if I switched to do some work in a separate session of Excel. (It also seemed as if moving to e.g. Word would result in an interruption as well, though I might be imagining this.)

The code makes various references to ActiveWorkbook, so I presume that moving to a different session of Excel whilst the code is running is causing an issue here.

I would like to be able to continue to do some other work in a second session of Excel - is there something I can add to my code so that this is possible and will not interrupt the macro?

Thanks in advance.

XOR

ActiveWorkbook will only refer to the active workbook in the same excel instance - it can't work cross-process.

Note: when I say it can't work cross-process, I mean just using ActiveWorkbook or Application.Activeworkbook. You can, of course, hold a reference to a separate instance and use xlApp.Activeworkbook.

Thanks, Rory.

Can you think of anything off the top of your head that might cause switching to another instance of Excel to interrupt the code running in another instance?

Regards

What about using ThisWorkbook instead of ActiveWorkbook?

For example Range("A1:A5").copy will copy any active workbook range so it is better to build Particular target reference to that workbook like ThisWorkbook.sheets("YourSheetName").Range("A1:A5").copy

Thanks Sixth.

Yes, of course I should've mentioned that I may well have "unqualified" (at the workbook/sheet level) references in there, although I try to always precede with e.g. ActiveWorkbook.

Could this be the issue, then? That if I have any references to ranges without the Sheet/Book level then switching to the other session may cause the code to assume the range in question is in that session?

Regards

Originally Posted by XOR LX

if I have any references to ranges without the Sheet/Book level then switching to the other session may cause the code to assume the range in question is in that session?

Exactly!

Range("A1:A5").value = "Abc"

Or

Activesheet.Range("A1:A5").value = "Abc"

Both are same...

ThisWorkbook refers to the Workbook on which the Code resides.

It is better to use Range / Worksheet variables to set the particular range / Worksheet in a variable so that calling the variable somewhere in the code will take care of the entire target reference.

Please Login or Register  to view this content.

Activeworkbook/activesheet refers to any wb/ws which is active at the time the code runs...

So it is better to set the object in a variable

No. Unless you have a reference to the other application, and use it, an unqualified reference will not refer to a separate instance of Excel.

Do you run any code in the other instance when you switch to it?

Just a sample code for easy understanding

Please Login or Register  to view this content.

Originally Posted by romperstomper

No. Unless you have a reference to the other application, and use it, an unqualified reference will not refer to a separate instance of Excel.

Do you run any code in the other instance when you switch to it?

No.

I'm wondering if the issue is not so much that I work in another session of Excel, but that I work in any other application.
Could be connected to my laptop's annoying habit of random cursor-jumping, e.g. in Outlook messages. But even then, wouldn't it need a specific key-combination to stop the macro, which I'm unlikely to hit upon randomly?

Regards

Typically, any key combination that ends/interrupts the macro would pop up a dialog - do you see one, or does the code pause, or does it actually end completely?

No. I don't see any dialog. The only thing that tells me that the code has stopped is that it gets as far as opening one of the template files, though instead of then populating and saving and closing, it simply stays open with its original filename.

I realise I haven't been very specific and that you haven't got much to go on, so I'm going to do some more testing and try to narrow down what specific circumstances cause the issue.

Thanks a lot for your time, everyone.

Regards

There is a bug in Excel where if you happen to hold down the Shift key while executing a Workbooks.Open command, it can stop the code in its tracks. Could that be it?

Originally Posted by romperstomper

There is a bug in Excel where if you happen to hold down the Shift key while executing a Workbooks.Open command, it can stop the code in its tracks. Could that be it?

That's it!! Just tested the code and held down SHIFT and it stopped instantly!

Great stuff, Rory!! Thanks for your patience as well.

Now I know that I can continue working...as long as I don't use SHIFT! (Slight drawback for me since I don't usually use a mouse at all... )

Thanks again. Much appreciated.

Regards

Originally Posted by romperstomper

There is a bug in Excel where if you happen to hold down the Shift key while executing a Workbooks.Open command, it can stop the code in its tracks. Could that be it?

Rory I don't think it is a bug in excel...

Microsoft made it purposely to avoid running macros when opening the workbook by holding the shift key.... Eventhough the file is in trusted location or macros enabled setting holding the Shift will bypass the coding.

The same thing is globally used in Ms-Access also, using the shift key at the time of opening the access file will stop all the startup codes / populating of user form also restricted.

Here is the article related to that...

http://excelribbon.tips.net/T010281_..._Workbook.html

I have a suggestion, while starting the code disable the Shift Key by way of code and reenable it before the end.

Refer the below link to know how to disable and enable the key..

http://www.rondebruin.nl/win/s4/win012.htm

That's fantastic, Sixth, and will also make sure that I don't accidentally forget that I shouldn't be using it!

Much appreciated.

Originally Posted by :) Sixthsense :)

Rory I don't think it is a bug in excel...

Microsoft made it purposely to avoid running macros when opening the workbook by holding the shift key

I know why it's there but it's a bug that it stops code that is opening other workbooks. It should not do that.

Sixth - sorry, my VBA understanding isn't great. Can you just confirm that all I'd need to do would be to add:

Please Login or Register  to view this content.

to the very start and also the very end of my code? Is the sytnax not different to turn it on or off?

Thanks a lot

Originally Posted by XOR LX

Sixth - sorry, my VBA understanding isn't great. Can you just confirm that all I'd need to do would be to add:

To Disbale

Please Login or Register  to view this content.

To Enable

Please Login or Register  to view this content.

Magic. Thanks a lot.

Hmmm.

Doesn't seem to like it. Does my procedure have to be re-defined in any way?

Or does this line have to go in a particular place in the code? (I've put the "off" one just after my declarations and the "on" one just before End Sub).

Thanks again for your help.

Regards

To the best of my knowledge you can't use OnKey to disable the Shift, Alt or Ctrl keys on their own. (I also don't actually think it would prevent this problem if you could). You'd need a low-level keyboard hook to block the actual input to the keyboard buffer and even then I'm not sure it would work since Shift on its own doesn't do anything.

Thanks. Then it looks like I'll just have to use some sort of workaround. Or simply use another laptop.

Regards

Originally Posted by XOR LX

Doesn't seem to like it. Does my procedure have to be re-defined in any way?

Rory is right.... and sorry for taken you somewhere with my suggestion

Originally Posted by XOR LX

simply use another laptop

In this case I have another suggestion for you

Install multiple versions of Ms-Office like Ms-Office 2007 & Ms-Office 2010 so that you can use 2007 version for running the macro and 2010 version for doing some other job simultaneously

At present I am having Ms-Office 2003, 2007 and 2010 in my PC

Thanks, Sixth, but that seems like a big workaround!

If I can't disable just the SHIFT key, is there perhaps a way to at least increase the length of time for which it has to be depressed in order to trigger the cancelling of the Workbook.Open event?

I found myself having to resort to ridiculous workarounds like using Insert/Symbol every time I wanted to enter a bracket or dollar sign yesterday, for fear of touching the SHIFT key!!

Cheers

(please delete - duplicate post)

Originally Posted by XOR LX

cancelling of the Workbook.Open event?

The Shift key is specifically assigned to disable all the codes when it is used at the time of opening a file. So when the coincidence occurs (i.e. using shift when code tries to open a workbook) the code gets stopped.

Preventing Esc is possible with the below line of code but I don't see any property to disable this (Shift)...

Please Login or Register  to view this content.

Why on earth did Microsoft decide to make it a single key, and such a common one at that??

I mean, why not have some combination of two or three keys for this purpose, so that it can never "accidentally" be activated?

Regards

Originally Posted by XOR LX

Why on earth did Microsoft decide to make it a single key, and such a common one at that??

Because (IMO) Excel is not designed to do multi tasking (for example we can't run two macros simultaneously)

But I don't necessarily want to work in Excel! Even working in Word or Outlook whilst the code is running is a danger if I ever use the SHIFT key!

Anyway, guess there's not much I can do about it.

Thanks again for all your help.

Originally Posted by XOR LX

Even working in Word or Outlook whilst the code is running is a danger if I ever use the SHIFT key!

I think Outlook,Excel,Word,Access,Powerpoint each are considered as different applications so using shift in another app will not affect this one I believe

I beg to differ: http://www.jkp-ads.com/Articles/WorkbookOpenBug.asp


@XOR
Apologies - I had forgotten that Jan Karel has a good workaround listed there.

Hi Rory,

Again thanks for the correction and sharing the alternate method link