Conditional Formatting: Help with Excel Decryption Function

• Hello,
I have been assigned to compose an Excel sheet in which a message can be entered and the sheet will automatically encipher the message. It's basically cryptography. But to further demonstrate the process (this will be a presentation) the sheet also needs to be able to have a section in which an encrypted message can be entered and it will be deciphered.

Basically, I have to make a code that will go forwards into encryption and then backwards out of encryption.

My difficulties arise not from the encryption, but the decryption. Try as I might, I have had no luck getting there, so any help will be greatly appreciate. Thanks.

• Hi Anonymous,

Well, as I'm sure you will appreciate, its a little difficult to advise on something when you don't know what it is! Are we to assume that the encryption is so top secret that you were forced to post as 'Anonymous' and that you aren't prepared to post details of the encryption function?

OK, I'll talk in general terms and based upon my (very limited) knowledge of cryptography. Many ciphers revolve around shifting a set of characters by a set amount. For example, if we were to shift all the letters in the alphabet by 6 characters to the right (not restarting with A at 27 but using the CHAR values) ANONYMOUS would become GTUT_SU[Y. To decrypt the string you simply reverse the process.

So, to answer your question, you should be able to decrypt the string by 'undoing' the encryption process. If the encryption isn't on this basis then you will need to provide details of the basis used - otherwise nobody will be able to help you.

• Richie,
Thanks for your help. Sorry, I have no idea how to post an Excel sheet on the internet.

My main problem here is that I'm using modular math, with the alphabet as the plain text. We're using a (mod 26) in addition to an affine cipher, that is a multiplicative as well as an additive cipher.

The keys that I'm using are

multiplicative=3

-Jim

• Jim,

The lounge is the only place where you can post "unregisterd", so you either got lucky or decided not to register :biggrin:

Anyway, you aren't really tapping in to the wealth of knowledge here at Ozgrid by posting in the Lounge - The Excel/VBA forum is where this post should be if you want to attract the best help available.... the lounge is more for generally shooting the breeze & arguing about gardening etc :wink1:

Registering costs nothing (& to date I have never been spammed as a result...)

Can I ask you to register & post a reply to the thread so we can help a "real person"- it's so much nicer! - then I can move this post to the correct forum & I think you will be amazed at the level of free assistance you will get for your problem.

Regards,

Will

• Hi Jim,

Sorry, but math (modular or otherwise) was never really by forte and my cryptography experience is pretty limited.

Would you elaborate on how the encryption process works? Perhaps, by way of example, you could show us the encrypted text for 'Jim'.

Also, are you trying to do this with native Excel functions or have you created a User Defined Function in VBA for the process?

As regards posting example spreadsheets to the forum, I use the HTMLmaker designed by Colo and Ivan. Have a look here: http://www.interq.or.jp/sun/puremis/colo/

• Hi - Here is an idea that was posted a while ago that I thought was pretty dam good.

Basically it encodeds a file called PWD.pwd - that resides in the same location as the spreadsheet. It runs through a userform and allows you to add names & passwords. Thes are encoded into ASCII keys.

Option Explicit

'
'
Dim intUnit As Integer
Dim udtUser As UserId

On Error GoTo ErrAuthorize

intUnit = FreeFile
Open ThisWorkbook.Path & "\" & PASSWORD_FILE For Binary Access Read As #intUnit

Do While Not EOF(intUnit)
Get #intUnit, , udtUser
udtUser = Decode(udtUser)
Authorize = True
Close intUnit
Exit Function
End If
End If
Loop

ErrAuthorize:
Close intUnit
Authorize = False
Exit Function
End Function
'
'
Dim intUnit As Integer
Dim udtUser As UserId
Dim lngRec As Long

On Error GoTo ErrStoreUser

intUnit = FreeFile
Open ThisWorkbook.Path & "\" & PASSWORD_FILE For Binary Access Read Write As #intUnit

Do While Not EOF(intUnit)
Get #intUnit, , udtUser
udtUser = Decode(udtUser)
lngRec = lngRec + 1
StoreUser = 2 ' already in use
Close intUnit
Exit Function
End If
Loop

udtUser = Encode(udtUser)

Put #intUnit, , udtUser
Close intUnit
StoreUser = 0
Exit Function

ErrStoreUser:
StoreUser = 1 ' error
Close intUnit
Exit Function
End Function

Private Sub CommandButton1_Click()

If Authorize(TextBox1.Text, TextBox2.Text) Then
MsgBox TextBox1.Text & " is a valid user", vbInformation
Else
End If

End Sub

Private Sub CommandButton2_Click()

Dim lngStatus As Long

lngStatus = StoreUser(TextBox1.Text, TextBox2.Text)
If lngStatus = 0 Then
MsgBox "User " & TextBox1.Text & " added", vbInformation
ElseIf lngStatus = 1 Then
MsgBox "Unable to add User " & TextBox1.Text, vbExclamation
ElseIf lngStatus = 2 Then
MsgBox "Username " & TextBox1.Text & " is already in use", vbExclamation
End If

End Sub

Private Sub UserForm_Click()

End Sub

'This is the code for the userform - requires two feilds Name & Password

Option Explicit

Public Const ENCODE_OFFSET = 100
Public Type UserId
End Type

UserForm1.Show vbModal
End Sub

Public Function Encode(User As UserId) As UserId
'
'
Dim lngIndex As Long

For lngIndex = 1 To Len(User.Username)
Next
For lngIndex = 1 To Len(User.Password)
Next

End Function
Public Function Decode(User As UserId) As UserId
'
' Remove byte offset
'
Dim lngIndex As Long

For lngIndex = 1 To Len(User.Username)
If Asc(Mid(User.Username, lngIndex, 1)) >= (ENCODE_OFFSET + lngIndex) Then
End If
Next
For lngIndex = 1 To Len(User.Password)
If Asc(Mid(User.Password, lngIndex, 1)) >= (ENCODE_OFFSET + lngIndex) Then
End If
Next

End Function

This is under a standard modual - as you can see there are set limits on the password length as well as the Username length.

I am not the origional author so can't take any credit!!!!but this was posted a while ago so I had a look - Bloody splendid code works like a dream. Dam good idea as the names/passwords are not stored within the spreadsheet as can't be spotted regardless of how the spreadsheet is hidden

One day I to will be able to write code like this!!!!!

Anyway - I'd attach the spreadsheet but I can't but if you want the example send me your e-mail address on U2U could be adapted th suit what you want.

Hope this helps - Phil

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!