Coffeehouse Post

Single Post Permalink

View Thread: Excel 2013 Performance Issue
  • User profile image


    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"
        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