vba excel - optimizing code

Code_End restores EnableEvents to the state it was in when Code_Begin was called. Are events disabled before you call Code_Begin?

You'd get the issue you describe if you are calling Code_Begin more than once, too.

You can either make sure you only call Code_Begin and Code_End once. Or if you know you want events to be enabled after Code_End, then you could change this line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

I am not trying to nit-pick here, but I want to make sure we are talking in like terms. What you have posted in NO way optimizes your code. What you have posted are in some cases best practices to help your code overall. Optimizing code is taking the code you have written and altering it so it accomplishes the same thing more quickly. You are just wrapping your code in some commands that may impact speed overall but do not improve your actual codes efficiency.

Having said that, a few things to point out. Some of these commands are generally good to use across the board, like:

Please Login or Register  to view this content.

The above is pretty universally helpful, as the majority of code people write will at some point update the contents of a sheet (yes there are exceptions). So it makes sense to include it in most of your code.

However many of these commands are not "One size fits all" as you are finding. The only reason you would, for example use:

Please Login or Register  to view this content.

Is if your code would trigger an event and you do NOT want it to, for example A Worksheet_Change event that changes the sheet. To prevent an endless loop of events you would have the event disable events, make the change, then enable events.

Otherwise, there is no benefit to using this command.

The other thing to keep in mind is that if your code breaks via an error and you do not have an error handler, you wont have toggled everything back on.

Instead of using a blanket routine for open/close, I use a "template" for my subs and functions, like:

Please Login or Register  to view this content.

With the above I have a generic error handler in place that will at least prevent a user from being given debug options and also ensure in case of an error screenupdating is turned back on. I store this in my personal workbook and copy it into a sub and then add in what I need from there. Some projects may require disabling events, some will not, some may need disabling alerts, calculations, etc but not all of them will.

Its also worth pointing out that if a sub calls another sub, and the parent sub disabled screenupdating for example, the child sub doesnt need to as well. If you did disable in the child too, you are just increasing the chances that along the way it doesnt get flipped back on.

All in all, if you have code that is running slower than you feel it should, you need to debug the code. Time how long parts of it take to run (using timers in VBA). Inspect your code for logical issues, for ways to simplify the approach, etc. There are for example many methods for moving information from one range to another, some are very slow and some are much faster. Which you use depends on the amount of data it will work with and how simply you want to accomplish the task. There is almost never a right/wrong way to do things in VBA. Usually there are 10 different ways that all have a purpose/use case and becoming a better programmer means knowing which tool to use for the job.

I don't know if this may be applicable to my situation since my date picker (uf called from cmdbutton) selection and output is to a text control within another userform. My question is should I change the declaration

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

From Mr. Pearson's site:

You can declare the EnableEvents as Private if only procedures with that form need to suppress events. However, if you have forms that are programmatically linked together, such UserForm2 adding an item to a ListBox on UserForm1, you should declare the variable as Public and set it for another form with code like the following:

Please Login or Register  to view this content.

The primary difference between the EnableEvents property and code shown above and the Application.EnableEvents property is that with a UserForm EnableEvents, all control on the form must have code to exit if EnableEvents is True. In other words, all the form's controls must cooperate and respect the setting of EnableEvents.

My (partial) code:

Please Login or Register  to view this content.

...and parent sub that calls the function:

Please Login or Register  to view this content.

I think OP wrote T/F to save the time/space of writing 2 lines.

Also @terriertrip I think what you really need to do is comb through your code and reconsider where in the code, if at all, you need to disable and enable events. Doing this is highly situational, again only needed when an event will trigger itself or another event in an unwanted way.

Especially focus on the scope/hierarchy of your enables/disables. Make sure you are disabling/enabling at the narrowest scope you can.

Ex:

Please Login or Register  to view this content.

The above would mean that events are disabled on all 3 called subs too. If all 3 have their own enable/disable it can conflict with the already set status from the main. If hypothetically you only need sub2 to have events disabled, you should only disable/enable in sub2. The above is a simplistic example, if your hierarchy is more complex it increases the possibilities of having conflicts or circumstances for it to be in an unexpected state.