1

The following code was provided by this website and I use it to unprotect sheets I don't know the passwords for.

Sub DisableSheetProtection()
Dim password As String
On Error Resume Next
For i = 65 To 66: For j = 65 To 66
For k = 65 To 66: For l = 65 To 66
For m = 65 To 66: For n = 65 To 66
For o = 65 To 66: For p = 65 To 66
For q = 65 To 66: For r = 65 To 66
For S = 65 To 66: For t = 32 To 126
ActiveSheet.Unprotect Chr(i) & _
Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(n) & Chr(o) & _
Chr(p) & Chr(q) & Chr(r) & _
Chr(S) & Chr(t)

Next t: Next S: Next r: Next q
Next p: Next o: Next n: Next m
Next l: Next k: Next j: Next i
MsgBox "Sheet protection disabled."
End Sub

The question is: how or why does that work? Chr(65) is A and Chr(66) is B and Chr(32) to Chr(126) is the whole alphabet and special characters.

Basically if you would want to try out all possibilities you would need to do this:

Sub DisableSheetProtection()
    Dim password As String
    On Error Resume Next
    For i = 32 To 126: For j = 32 To 126
    For k = 32 To 126: For l = 32 To 126
    For m = 32 To 126: For n = 32 To 126
    For o = 32 To 126: For p = 32 To 126
    For q = 32 To 126: For r = 32 To 126
    For S = 32 To 126: For t = 32 To 126
    ActiveSheet.Unprotect Chr(i) & _
    Chr(j) & Chr(k) & Chr(l) & _
    Chr(m) & Chr(n) & Chr(o) & _
    Chr(p) & Chr(q) & Chr(r) & _
    Chr(S) & Chr(t)

    Next t: Next S: Next r: Next q
    Next p: Next o: Next n: Next m
    Next l: Next k: Next j: Next i
    MsgBox "Sheet protection disabled."
    End Sub

But the code works just fine as it is for unprotecting and you cannot print out the correct password. I assume this has something to do with the VBA unprotect/protect method.

braX
  • 11,506
  • 5
  • 20
  • 33
Lucas Raphael Pianegonda
  • 1,151
  • 12
  • 29
  • Possible duplicate of [How does Excel's worksheet password protection work](https://stackoverflow.com/questions/12852095/how-does-excels-worksheet-password-protection-work) – Plagon Mar 12 '18 at 14:00

1 Answers1

4

Internal XL passwords are about as useful for security as tissue paper. The reason is that the passwords you enter (i.e., with Tools/Protect/Protect Worksheet or /Protect Workbook) are not used directly in protection. Instead they are hashed (mathematically transformed) into a much less secure code. Effectively, any password of any length is transformed into a string of 12 characters, the first 11 of which have one of only two possible values. The remaining character can have up to 95 possible values, leading to only

2^11 * 95 = 194,560 potential passwords.

This may seem like a lot, but it only takes a few seconds for a modern computer >to try them all. As a comparison, a 4-character password containing just the 26 lower case alphabet characters has 456,976 combinations, and a 3-character password consisting of lower case, upper case and the digits 0-9 will have 238,328 combinations.

Again, it doesn't matter what your original password is, one of those 194K strings will unlock your sheet or workbook.

For more read this.

Community
  • 1
  • 1
Plagon
  • 2,689
  • 1
  • 11
  • 23