Performance issue in MS Excel 2013
Recently, a client whom Olas developed a customised MS Excel 2010 application a couple of years ago for, reached out looking for help with an issue surrounding performance of the application in Office 2013. The developed application is sold to customers in the construction industry. In Olas’ experience of training & development of Office applications, Office 2013 has not yet been widely adopted in Ireland and many companies are only transitioning or planning to transition now, hence the issue only recently surfacing.
The developed application is a complex one, with over fifty worksheets and hundreds of calculations – it operates in a website like fashion, displaying one worksheet at a time and allowing the user to navigate to their desired destination within the application. In order to protect the integrity and structure of the application and ensure the validity of the calculations, each worksheet is protected to prevent the user from introducing errors into the application.
Upon investigation of the issue we learned that MS Excel 2013 uses a more sophisticated encryption algorithm when protecting the worksheets. The impact of the increased security is a performance hit when unprotecting and protecting a number of worksheets in succession – (it’s largely unnoticeable when performing the operation for a single worksheet). In the developed application, this operation is performed numerous times in succession when the application is started up and resulted in a considerable delay in opening the application in MS Excel 2013.
Arguably when a new version of software is released, one might expect it to be more efficient and resourceful than its predecessor, and while Office 2013 in general is quite slick, it’s a little disconcerting that an operation that previously performed seamlessly is now a major design consideration for future projects (this is sometimes referred to as ‘backwards compatibility’). It raises the possibility that other operations could become similarly affected in future releases of the product.
It could be argued that MS Office 2013 should have a compatibility mode whereby incarnations of Excel files created in a previous version of MS Excel would run exactly as per the original version, thus ensuring that there is no adverse effect on existing applications. Despite this, MS Excel 2013 is still a stellar product providing a vast range of functionality and remains a vital tool in almost every company that uses technology today.
Note: This issue is acknowledged by Microsoft at the following page (relevant text below):
Click this link for details.
Excel: Protecting sheets takes longer Protecting sheets takes much longer than in previous Excel versions. It’s not noticeable when you’re manually protecting a sheet, but if you have code that protects or unprotects sheets repeatedly, it can become a performance issue.
The delay is caused by increased security in Excel 2013 for password protected files, because it uses a stronger hashing algorithm (SHA-512). In Office 2010 the default hashing algorithm for password protected files is SHA1. This change makes Excel password protected workbooks more secure than they were in previous versions, but can cause a performance issue for some Office Developers.
Workaround This is by design so there is no workaround.