Multithreading in Excel

That's clearly a line out of an event handler. I suspect that it's possible you are switching off event handling for longer than it needs to be. As far as I am aware, that is an Application wide setting ... as in

Please Login or Register  to view this content.

I think it depends on how much code there is in your event handler(s). You should switch off event handling for as short a time as possible and ensure that, in the event of an error, it is switched back on before exiting the routine.

For example, in a Worksheet Change Event handler, you only need to switch off event handling if/when you actually make change while your in the handler to avoid unnecessary execution. Bear in mind that the WCE handler will fire every time you make a change on the worksheet but you probably won't need to process every event. So, you don't need to switch off event handling and then make the decision whether or not you are going to process the change event.

Let's say you are only interested in changes in range B11:B30. That's just 20 cells out of potentially millions that you could change. The more you limit the scope, the less opportunity there is for issues to arise.

Application.Range("Vn_Input_HANA_Production") will refer to that range in the active workbook. If the workbook that is being changed isn't the active one, then your event wouldn't do anything.

hen your event wouldn't do anything.

It would not throw any error? Thanks!

Hmm so what if i would have activated 2nd workbook but run code from first one?

I think it depends on how much code there is in your event handler(s). You should switch off event handling for as short a time as possible and ensure that, in the event of an error, it is switched back on before exiting the routine.

For example, in a Worksheet Change Event handler, you only need to switch off event handling if/when you actually make change while your in the handler to avoid unnecessary execution. Bear in mind that the WCE handler will fire every time you make a change on the worksheet but you probably won't need to process every event. So, you don't need to switch off event handling and then make the decision whether or not you are going to process the change event.

Let's say you are only interested in changes in range B11:B30. That's just 20 cells out of potentially millions that you could change. The more you limit the scope, the less opportunity there is for issues to arise.

thank you!

thank you very much for your explanation. It can be an issue. I have worksheet and only few cells should be triggered using event handler.

So it is a good practice to do application.enableevents before each cell which can be triggered (each line of code) and after it just turn on this?
Like if range(B30) = "Yes" then
application.enableevents = false
range(B20) = "1"
application.enableevents = true
end if
??

And how to address only specific range to be triggered like B11:B30?

Best,
Jacek

It should throw an error (1004) but I don't know if you have error handling.

I note you refer to the users having multiple instances of your master workbook open - is that a template, or are they using multiple instances of Excel?

So it is a good practice to do application.enableevents before each cell which can be triggered (each line of code) and after it just turn on this?

Pretty much, yes. However, if possible, you would be better off referring to the Target cell rather than specific cells

And how to address only specific range to be triggered like B11:B30?

You already have the right construct:

Please Login or Register  to view this content.

Note that a Change Event handler, if in place, will fire for every change. It's more a case of limiting how much work it needs to do when it is fired.

This is the solution to a recent thread. Notice that quite a few checks, lookups and calculations are done before event handling is switched off. The "range of interest" is more complex in this example.

Please Login or Register  to view this content.

The only things I know of that can stop events from running are enableevents and faulty UDFs used in conditional formatting.

I simply mean that if you use a UDF in a conditional formatting formula, and it causes an error, that can terminate all event code. As a rule, it's best to avoid UDFs that use any properties of cell other than value or formula in a conditional formatting rule.

You're welcome. Thanks for the rep.

Again, thanks for the rep. I suspect the critical thing is not how long the routine takes to execute but what, if anything, could cause it to fail during execution. If your normal practice is to switch off event handling as the very first thing you do, and switch it on again as the very last thing you do, there is scope for an error to crash the event handler and exit before switching event handling back on.

If you want a more detailed answer, you would need to share (all) the code and/or a copy of the template.