Is it possible to code a variable password for an excel workbook?

[dougmarkham]

Hi Folks,

When you use a card reader, a website gives you a number to input, and the card reader outputs a number which gains you access.
I was wondering if it is possible to replicate this process with two workbooks.

Workbook 1: contains the sensitive data. On opening, an input box appears with different 6 digit number (each time the wb is opened) and a text entry box to type the key.
Workbook 2 (keygen wb): contains an activeX Text Box to enter the 6 digit number and a button to set off an algorithm to generate a key and put it into clipboard

Has anyone tried this or have any alternative solutions using VBA?

Kind regards,

Doug.

 

[Dan_W]

If I understand you correctly, you mean like the process you go through for online banking (as an example). Where you have a physical 'calculator' thingamabob, into which you input a code generated by the bank website. The calculator then issues you with a response code, which you then put into the website, and tada - you're in?

If that is indeed what you mean, I've not seen it done before (but then the fact that I've not seen it done doesn't mean much at all!). I suppose the code in both workbooks would be almost the same - both would need to be able to perform the same encryption algorithm. The only difference being that one workbook would generate the random number, and the other would receive it from the user as input and then produce the result.

I don't think it would be particularly secure though - it's pretty easy to see the VBA code in a workbook. Though I can't say I've given it any serious thought, I imagine there must be better ways of authenticating access.

 

[dougmarkham]

If I understand you correctly, you mean like the process you go through for online banking (as an example). Where you have a physical 'calculator' thingamabob, into which you input a code generated by the bank website. The calculator then issues you with a response code, which you then put into the website, and tada - you're in?

If that is indeed what you mean, I've not seen it done before (but then the fact that I've not seen it done doesn't mean much at all!). I suppose the code in both workbooks would be almost the same - both would need to be able to perform the same encryption algorithm. The only difference being that one workbook would generate the random number, and the other would receive it from the user as input and then produce the result.

I don't think it would be particularly secure though - it's pretty easy to see the VBA code in a workbook. Though I can't say I've given it any serious thought, I imagine there must be better ways of authenticating access.

Hi Dan_W,

Thanks for replying.
Yeah, that's exactly what I'm considering.

I could password protect the VBA, but last year, I quickly found other vba code to break VB editor module passwords, which helped solve our problem (my predecessor left without leaving a password to an important workbook).

SavvySoft’s Calc4Web software (quite expensive) compiles the Excel workbook and its VBA code into an application *.exe file. If I could find something cheaper, that might allow this sort of encryption to be more secure.
On the basis I could secure the VBA, might be interesting to see if I could try setting up bank style encryption using two workbooks.

Only thing I've found so far is this code but will have to give it proper consideration on Monday:

Option Explicit

Sub test()

'this sub is only present to demonstrate use of the function!
'it is not required to use the function.

Dim r As Range, retVal, sKey As String
sKey = Application.InputBox("Enter your key", "Key entry", "My Key", , , , , 2)
retVal = MsgBox("This is the key you entered:" & vbNewLine & Chr$(34) & sKey & Chr$(34) & vbNewLine & _
"Please confirm OK or Cancel to exit", vbOKCancel, "Confirm Key")
If retVal = vbCancel Then Exit Sub
For Each r In Sheets("Sheet1").UsedRange
If r.Interior.ColorIndex = 6 Then
r.Value = XorC(r.Value, sKey)
End If
Next r

End Sub


Function XorC(ByVal sData As String, ByVal sKey As String) As String

Dim l As Long, i As Long, byIn() As Byte, byOut() As Byte, byKey() As Byte
Dim bEncOrDec As Boolean

'confirm valid string and key input:
If Len(sData) = 0 Or Len(sKey) = 0 Then XorC = "Invalid argument(s) used": Exit Function

'check whether running encryption or decryption (flagged by presence of "xxx" at start of sData):
If Left$(sData, 3) = "xxx" Then
bEncOrDec = False 'decryption
sData = Mid$(sData, 4)
Else
bEncOrDec = True 'encryption
End If

'assign strings to byte arrays (unicode)
byIn = sData
byOut = sData
byKey = sKey
l = LBound(byKey)

For i = LBound(byIn) To UBound(byIn) - 1 Step 2
byOut(i) = ((byIn(i) + Not bEncOrDec) Xor byKey(l)) - bEncOrDec 'avoid Chr$(0) by using bEncOrDec flag
l = l + 2
If l > UBound(byKey) Then l = LBound(byKey) 'ensure stay within bounds of Key
Next i
XorC = byOut
If bEncOrDec Then XorC = "xxx" & XorC 'add "xxx" onto encrypted text

End Function

Kind regards,

Doug.

 

[Dan_W]

Hi Doug

I could password protect the VBA, but last year, I quickly found other vba code to break VB editor module passwords, which helped solve our problem (my predecessor left without leaving a password to an important workbook).

This was the point I was trying to make. VBA is great for many things, but I don't think it's particularly good at protecting itself from prying eyes. I suppose it will depend on what it is you want to protect, and from who. Encryption is certainly not my area, but I wonder whether it might be easier for you just to use standard encryption/decryption software.

Thank you for sharing the code above - I tried it and it seemed to work. Because it works by going over each worksheet range (cell) in the designated area, it can be quite time-consuming if you have worksheets with a lot of data to encrypt. Probably worth testing it on some dummy data when you get the chance.