Coffeehouse Post

Single Post Permalink

View Thread: Excel 2013 Performance Issue
  • User profile image
    robwellis

    Hi,

    I have noticed that password protecting a workbook and/or a worksheet from within VBA runs over  two thousand times slower in Excel 2013 than it does it Excel 2010.

    Consider this simple code snippet

    Sub Test()
        Dim i As Integer, max As Integer, tStart As Single
        max = 100
        tStart = Timer
        For i = 1 To max
            ActiveWorkbook.Worksheets(1).Protect Password:="pword"
            ActiveWorkbook.Protect Password:="pword"
            ActiveWorkbook.Unprotect Password:="pword"
            ActiveWorkbook.Worksheets(1).Unprotect Password:="pword"
        Next
        Debug.Print max & " iterations took " & Timer - tStart & " seconds."
    End Sub
    

    This takes 142 seconds in Excel 2013 and 0.06 seconds on the same computer using Excel 2010.

    The same code without the passwords takes just 0.15 seconds in Excel 2013 and 0.05 seconds in Excel 2010. A factor of 3.

    I am using Excel 2013 version 15.0.4420.1017 which as far as I can tell is the most up to date.

    Has anyone else experienced this type of performance issue and found a fix for it?

    Thanks in advance

    Rob