Unlock Password Protected Worksheet or Workbook

VBA

A VBA macro to unprotect a sheet or workbook without the password.

The Excel VBA code below unprotects a sheet or workbook using the Excel backdoor password. The code tries each of the 57,120 possible backdoor passwords until the sheet or workbook is successfully unprotected. This technique is possible because, every time a sheet or workbook is protected, an alternate password is created that is much easier to guess using this brute force password cracking method which involves trying all possible passwords until the correct one is found. The backdoor password is constructed using a series of uppercase A's and B's followed by a single character between ASCII 32 and 255. The password is usually between 4 and 8 characters in length based on the original password.

To use the code, place all the code below in a general module and then call the macro UnlockSheet to unprotect the active sheet or UnlockWorkbook to unprotect the active workbook. Note that the code provided below does not unprotect a VBA project — this task is more involved and is covered in a different article.

Option Explicit

Private Sub DisplayStatus( _
      ByVal PasswordsTried As Long _
   )

   Static LastStatus As String

   LastStatus = Format(PasswordsTried / 57120, "0%") & " of possible passwords tried."
   If Application.StatusBar <> LastStatus Then
      Application.StatusBar = LastStatus
      DoEvents
   End If

End Sub

Private Function TrySheetPasswordSize( _
      ByVal Size As Long, _
      ByRef PasswordsTried As Long, _
      ByRef Password As String, _
      Optional ByVal Base As String _
   ) As Boolean
   
   Dim Index As Long
   
   On Error Resume Next
   If IsMissing(Base) Then Base = ""
   If Len(Base) < Size - 1 Then
      For Index = 65 To 66
         If TrySheetPasswordSize(Size, PasswordsTried, Password, Base & Chr(Index)) Then
            TrySheetPasswordSize = True
            Exit Function
         End If
      Next Index
   ElseIf Len(Base) < Size Then
      For Index = 32 To 255
         ActiveSheet.Unprotect Base & Chr(Index)
         If Not ActiveSheet.ProtectContents Then
            TrySheetPasswordSize = True
            Password = Base & Chr(Index)
            Exit Function
         End If
         PasswordsTried = PasswordsTried + 1
      Next Index
   End If
   On Error GoTo 0
   
   DisplayStatus PasswordsTried

End Function

Private Function TryWorkbookPasswordSize( _
      ByVal Size As Long, _
      ByRef PasswordsTried As Long, _
      Optional ByVal Base As String _
   ) As Boolean
   
   Dim Index As Long
   
   On Error Resume Next
   If IsMissing(Base) Then Base = ""
   If Len(Base) < Size - 1 Then
      For Index = 65 To 66
         If TryWorkbookPasswordSize(Size, PasswordsTried, Base & Chr(Index)) Then
            TryWorkbookPasswordSize = True
            Exit Function
         End If
      Next Index
   ElseIf Len(Base) < Size Then
      For Index = 32 To 255
         ActiveWorkbook.Unprotect Base & Chr(Index)
         If Not ActiveWorkbook.ProtectStructure And Not ActiveWorkbook.ProtectWindows Then
            TryWorkbookPasswordSize = True
            Exit Function
         End If
         PasswordsTried = PasswordsTried + 1
      Next Index
   End If
   On Error GoTo 0
   
   DisplayStatus PasswordsTried

End Function

Public Sub UnlockSheet()

' Unprotect the active sheet using a backdoor Excel provides where an alternate
' password is created that is more limited.

   Dim PasswordSize As Variant
   Dim PasswordsTried As Long
   Dim Password As String

   PasswordsTried = 0
   If Not ActiveSheet.ProtectContents Then
      MsgBox "The sheet is already unprotected."
      Exit Sub
   End If
   On Error Resume Next
   ActiveSheet.Protect ""
   ActiveSheet.Unprotect ""
   On Error GoTo 0
   If ActiveSheet.ProtectContents Then
      For Each PasswordSize In Array(5, 4, 6, 7, 8, 3, 2, 1)
         If TrySheetPasswordSize(PasswordSize, PasswordsTried, Password) Then Exit For
      Next PasswordSize
   End If
   If Not ActiveSheet.ProtectContents Then
      MsgBox "The sheet " & ActiveSheet.Name & " has been unprotected with password '" & Password & "'."
   End If
   Application.StatusBar = False
   
End Sub

Public Sub UnlockWorkbook()

' Unprotect the active workbook using a backdoor Excel provides where an alternate
' password is created that is more limited.

   Dim PasswordSize As Variant
   Dim PasswordsTried As Long
   
   PasswordsTried = 0
   If Not ActiveWorkbook.ProtectStructure And Not ActiveWorkbook.ProtectWindows Then
      MsgBox "The workbook is already unprotected."
      Exit Sub
   End If
   On Error Resume Next
   ActiveWorkbook.Unprotect ""
   On Error GoTo 0
   If ActiveWorkbook.ProtectStructure Or ActiveWorkbook.ProtectWindows Then
      For Each PasswordSize In Array(5, 4, 6, 7, 8, 3, 2, 1)
         If TryWorkbookPasswordSize(PasswordSize, PasswordsTried) Then Exit For
      Next PasswordSize
   End If
   If Not ActiveWorkbook.ProtectStructure And Not ActiveWorkbook.ProtectWindows Then
      MsgBox "The workbook " & ActiveWorkbook.Name & " has been unprotected."
   End If
   Application.StatusBar = False
   
End Sub

Feedback

Question, correction, or comment? Let us know.