Application screenupdating not working in excel 2016
Whereas before I was pretty liberal, protecting and unprotecting whenever I needed to, now I’m unprotecting and reprotecting exactly one time. As an experiment, I removed all protection from the templates and removed all the unprotecting and reprotecting from my code. That cuts it in half, although I can’t release this into the wild with no protection.My client reports that it takes ~90 seconds on his machine.
It is much faster to read the data once and save it into memory than to reread it each time.The first command recalculates the entire workbook.The second command only recalculates a specific sheet.My code opens templates, unprotects them, writes stuff, combines them into a final report, and reprotects them.The first thing I did was get stingy about my protection.
This article is primarily focused on Excel VBA macros, however many of these rules apply to Microsoft Access VBA macros as well. Turn off automatic spreadsheet calculation This rule is well known, but it is the most important rule.