-2

Unprotecting/protecting a sheet using VBA is much slower in Excel 365 compared to Excel 2007. Does anyone have the slightest idea as to why this is the case? And is there anything that can be done to resolve this without just omitting the protect-functions?

To make sure the issue is caused solely by the unprotect/protect statements, I tested with a blank workbook containing a single module with the following code:

Dim secondsElapsed As Double

startTime = Timer
Sheets("test").Unprotect ("****")
ThisWorkbook.Sheets("Timers").Cells(1, 1) = Round(Timer - startTime, 2)

startTime = Timer
Sheets("test").Protect Password:="****", DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
ThisWorkbook.Sheets("Timers").Cells(2, 1) = Round(Timer - startTime, 2)

`

This yields the following averages:

  • Excel 2007
    • unprotect: 0.02 seconds
    • protect: 0.01 seconds
  • Excel 365
    • unprotect: 0.52 seconds
    • protect: 0.47 seconds
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
W. Biesmans
  • 9
  • 3
  • 1
    It's hard to know without seeing the code. For instance, are you using `.Select`/`.Activate`? (If so, [please don't](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)). How are you unprotecting the sheets? – BruceWayne Feb 14 '19 at 19:16
  • No, I refrain from using `.select` or `.activate` unless absolutely necessary. I (un)protect the sheets using the following lines of code: `ActiveSheet.Unprotect ("********")` and `Sheets(Sheet_Name).Protect Password:="********", DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True`. These individual lines easily take +0.5 seconds more to execute. – W. Biesmans Feb 14 '19 at 20:15
  • The reason people ask to see the rest of your code is they can try to recreate the problem and help you solve it or find something else that may be causing the problem. This is not a known problem and not one that I have encountered while using Office 365. – Darrell H Feb 14 '19 at 21:28
  • Ok, I see where you're getting from. I didn't think about it that way because I've noticed it happening in all modules independent of the rest of the code in the respective module. Therefore, I was fairly certain the rest of the code is irrelevant. To make sure of this I re-tested the problem with a blank workbook, 1 module and the bare minimum off code. The problem remained. I will adjust my original question. – W. Biesmans Feb 14 '19 at 22:43
  • Please include a [mcve] so everyone can reproduce the issue. – Pᴇʜ Feb 18 '19 at 07:17
  • The exact, complete code to reproduce the issue is already included? – W. Biesmans Feb 19 '19 at 17:12

1 Answers1

0

After more research and asking on other forums, I have found a solution. It is possible to protect only for the user, and thus not for VBA, by adding the UserInterfaceOnly:=True parameter in the protect statement. Therefore, it is merely necessary to protect once (i.e the (un)protect statements in the rest of the code can be omitted).

Of course, this does not yet explain why the (un)protect statements take this long. To try to find an answer tot this problem, I will contact Microsoft directly.

W. Biesmans
  • 9
  • 3