Hi Gary,
This gets a little difficult, only because in order to obfuscate it, you need to feed it into some kind of routine to do so which... requires starting with the original password. At least you do if you want to save it in the code. And no matter what you do, you're going to need to have a routine to decrypt the password in your workbook. That means that if someone were to carefully follow it, they could reverse engineer it to figure out the algorithm.
Having said all this, if you still want to go ahead with it, the approach I would follow is this:
- Have the routine to generate the obfuscated password in another workbook
- Generate the password there
- Put it and the decryption routine in the workbook you need them in
- Make sure that the name of the decryption routine and password variables are obfuscated too.
Here's an example...
A relatively easy obfuscator that just takes each letter of the password and changes it into it's ASCII numeric equivalent:
Code:
Function obfuscate(sPassword As String) As String
'Written By: Ken Puls ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro Purpose: Obfuscate a password by changing letters to ASCII equivalents
'Note: Do not place in same workbook as the decrypter!
Dim lChar As Long
For lChar = 1 To Len(sPassword)
obfuscate = obfuscate & Asc(Mid(sPassword, lChar, 1)) & "|"
Next lChar
obfuscate = Left(obfuscate, Len(obfuscate) - 1)
End Function
So feeding the phrase "My Password" through this function would return "77|121|32|80|97|115|115|119|111|114|100". Remember, to make it as hard as possible for someone to decrypt what we've done, the encryptor should NOT be in the same workbook as our main code.
Now, the next part we need is a way to decrypt the password for use. This DOES need to be in the workbook where we need to use the password, or we have no way to get it back out. Here's a version that works to decrypt the password above (written legibly so you can understand what it's doing):
Code:
Public Function decrypt(s) As String
'Written By: Ken Puls ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro Purpose: Decrypt a passowrd encoded in ASCII equivalents Dim lChar As Long
Dim ary() As String
'Split password into an array
ary() = Split(s, "|")
'Convert each character back to normal text
For lChar = 0 To UBound(ary())
decrypt = decrypt & Chr(ary(lChar))
Next lChar
End Function
Of course, putting in a full labelled decryptor would be a little silly, as it let's someone work out what is happening very easily. And while we can't eliminate the fact that they could reverse engineer it, there is no point in making it easy. So if we obfuscate that code, remove the comments and whitespace, we get:
Code:
Public Function dp(s) As String
Dim c As Long
Dim a() As String
a() = Split(s, "|")
For c = 0 To UBound(a())
dp = dp & Chr(a(c))
Next c
End Function
Same thing, and feeding "77|121|32|80|97|115|115|119|111|114|100" into it will return "My Password"
Now, to use this, you could modify the protection routine above as follows:
Code:
Private Sub Workbook_Open()
Dim ws As Worksheet
Const p = "77|121|32|80|97|115|115|119|111|114|100"
With Application
.ScreenUpdating = False
.WindowState = xlMaximized
End With
ActiveWindow.WindowState = xlMaximized
For Each ws In ThisWorkbook.Worksheets
With ws
.EnableSelection = xlNoRestrictions
.EnableAutoFilter = True
.EnableOutlining = True
If Application.UserName <> "Ken Puls" Then
.Protect dp(p), , , , True
Else
.Unprotect dp(p)
End If
End With
Next ws
Application.ScreenUpdating = True
End Sub
So I've added a constant to the top of the routine that has the "encrypted" password, then call the obfuscated decryption routine to pass that to the protect/unprotect routines. I've also removed the Password:= arguments from the code, as that brings attention to what it is.
Some other things I would suggest...
- Put the decryptor in a separate module from your other code and place the line "Option Private Module" at the very top. This will allow the decryption routine to be called from within the modules, but will hide it from the macro run dialog.
- Move the Const line out of the Workbook_Open routine, and place it under the Option lines in the new module. You'll also want to mark it as "Public Const" so that it can be seen from other modules. The benefit of this is that it isn't close to your original protection routine, which makes it harder to find.
So this will help you prevent the casual coder from guessing the password, but someone determined... well... there's nothing you can do to stop them.
With regards to the registry... unless you are distributing your workbook with a setup routine in another workbook, I would avoid the registry. It sounds great in theory, but you need to have the password in the file in order to write it to the registry when the user opens the workbook so that you can retrieve it.
On the other hand, if you're trying to prevent anyone from unprotecting it, I suppose this might be one way that you could. Have another workbook that writes the password key to the registry, and have your main workbook retrieve the key from the registry. If that other workbook has never been opened, then they won't have the key at all. The only issue is that you would have there however, is that it won't be able to re-run the "UserInterfaceOnly=True" each time as you wouldn't have the password to set it. Does that make sense?
