Protecting Sheets In Excel 2013 Is Slow

Home/VBA – Macros/ Protecting Sheets In Excel 2013 Is Slow

Protecting Sheets in Excel 2013 is Slow

By Wyn Hopkins

Protecting sheets takes much longer in Excel 2013 than it did 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.

For example, protecting 30 sheets in Excel 2007 / 2010 using the below code will occur almost instantly. In Excel 2013 the same code running on the same workbook will take around 10 seconds.

Sub ProtectAll()
   Dim wks As Worksheet

   For Each wks In Worksheets

      wks.Protect Password:=“abc”, userinterfaceonly:=True

   Next wks
End Sub

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.

This is by design, so there is no way around it unfortunately!


2017-03-08T03:54:05+00:00 VBA – Macros|