Shift Cipher

ENCODE DECODE CASE MOVE ASCII USAGE

Aim

This page shows you how to add text boxes to the Simple Shift Alphabet. so that you can encode and decode messages.

Make sure that you have a good backup or two of your previous workbook in case you trash it when you alter it!

Adding text boxes.

Get the worksheet into Design Mode by clicking on the Design Mode toggle.
Now click on the Text Box object to select it.

Drag and drop two text boxes onto your work book. These need to be pretty big to make life easier later on.

Your workbook should look like the image below.

Make sure that you save your workbook after every change; especially if the change works.

Now select a command button from the object menu. Four of these are needed to do various jobs.

Right click on each button to change its caption.

The other buttons need to be called:
DECODE
CLEAN n CASE
MOVE TO TOP

The VBA code now needs to be entered for each command button. To do this you use the VBA editor. The easiest way to summon this is to double left click on an object whilst in design mode.

The cursor is automatically positioned in the correct place for adding VBA code to a control.

Copy and paste the code below into the editor.
Make sure that you don't duplicate the top and bottom lines as they are provided automatically by the editor.
Once you have entered the code you must save the changes you have made. Then reurn to your workbook and exit from design mode. Design mode is entered or left by left clicking its toggle switch.

ENCODE Button

This button takes a message from the top text box, encodes it and places the result in the lower textbox.

The message must be in uppercase and have had all punctuation stripped out.

Private Sub CommandButton1_Click()
alphStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
newStr = ""
sourceStr = TextBox1.Value
For i = 1 To Len(sourceStr)
countpos = 27
For n = 1 To 26
If (Mid(sourceStr, i, 1) = Mid(alphStr, n, 1)) Then
countpos = n
End If
Next n
If (countpos = 27) Then
newStr = newStr + " "
End If
If (countpos < 27) Then
newStr = newStr + Worksheets("Sheet1").Cells(2, countpos).Value
End If
Next i
TextBox2.Value = newStr
End Sub

Str stands for string. This is the computer science name for a string of letters.
alphStr is the 26 letter English alphabet.
newStr will contain the encoded message.
sourceStr is the message that will be processed. This should be in the top textbox.
The for loop controlled by i runs from 1 to the length of the source text.
Mid(sourceStr,i,1) reads 1 letter from position i of the source message. This is then checked against the alphabet. If a match is found then the marker countpos is given the current value of n.
If countpos is 27 this indicates that there is a space in the message. If countpos has been altered to a value less than 27 then the appropriate letter is read from the 2nd row of the spreadsheet. This letter is then added to newStr.
Once the end of the source message has been reached, newStr is sent to the lower textbox.

Check that the message has been encoded correctly. Check that the encoding changes when you alter the value of shift and press ENCODE again.
A shift of 26 or 0 should leave the message as it was.

If you have made the slightest mistake in your VBA then you will get lots of horrible error messages which cover your project in yellow writing. Read through these carefully and correct them. Compare them with my originals and try using the help files.
You must also click on the reset button in order to restart VBA properly.

DECODE Button

This button takes a message from the top text box, decodes it and places the result in the lower textbox.

The message must be in uppercase and have had all punctuation stripped out.

Private Sub CommandButton4_Click()
codeStr = ""
For n = 1 To 26
codeStr = codeStr + Worksheets("Sheet1").Cells(2, n).Value
Next n
newStr = ""
sourceStr = TextBox1.Value
For i = 1 To Len(sourceStr)
countpos = 27
For n = 1 To 26
If (Mid(sourceStr, i, 1) = Mid(codeStr, n, 1)) Then
countpos = n
End If
Next n
If (countpos = 27) Then
newStr = newStr + " "
End If
If (countpos < 27) Then
newStr = newStr + Worksheets("Sheet1").Cells(1, countpos).Value
End If
Next i
TextBox2.Value = newStr
End Sub

This code is very similar to that of the ENCODE button.
Instead of alphStr codeStr is read in from the 2nd row of the spreadsheet.
Each letter of the source text is then compared with the code.
Once a match is found the corresponding letter of the alphabet is placed in newStr.
The process repeats until the end of the source message is reached.

CLEAN n CASE Button

This button takes a message from the top text box, strips out the punctuation and converts it to uppercase. The result is placed in the lower textbox.

Private Sub CommandButton2_Click()
newStr = ""
sourceStr = TextBox1.Value
For i = 1 To Len(sourceStr)
If (Asc(Mid(sourceStr, i, 1)) >= 65) Then
If (Asc(Mid(sourceStr, i, 1)) <= 90) Then
newStr = newStr + Mid(sourceStr, i, 1)
End If
End If
If (Asc(Mid(sourceStr, i, 1)) = 32) Then
newStr = newStr + Mid(sourceStr, i, 1)
End If
If (Asc(Mid(sourceStr, i, 1)) >= 97) Then
If (Asc(Mid(sourceStr, i, 1)) <= 122) Then
newStr = newStr + chr(Asc(Mid(sourceStr, i, 1))-32)
End If
End If
Next i
TextBox2.Value = newStr
End Sub

The first if statement allows capital letters through. The ASCII codes of the uppercase alphabet run from 65 to 90 inclusive.
The ASCII code for a space is 32 so this is also allowed through by the next if statement.
The final if statement takes lower case letters (ascii 97 - 122) and converts them to uppercase by subtracting 32.
The cleaned up message is then sent to the lower textbox.

MOVE TO TOP Button

This button takes a message from the lower text box and puts it into the top text box. This is a very useful function!

Private Sub CommandButton3_Click()
newStr = ""
TextBox1.Value = TextBox2.Value
TextBox2.Value = newStr
End Sub

The values of textbox 2 is moved into textbox one and an empty string is written to textbox 2.

ASCII Codes

The computer represents letters of the alphabet by numbers known as ASCII codes. You can save yourself a lot of time and effort by making maximum use of these codes.

If you cut and paste the code below into a the top left cell of a new workbook page. Then copy the rows down the page. You should then obtain the ASCII codes for upper and lower case letters. Remember that the ASCII code for a space is 32.

NB in a workbook cell the command to get a letter back from an ASCII code is Char(CODE) but in VBA teh command is Chr(CODE)!

65=char(A1)97=char(c1)
66=char(a2)98=char(c2)

Using This Page.

The page that you have made can actually be used with three different types of mono-alphabetic substitution cipher.

  • Shift Ciphers. In these the alphabet retains its normal order but is moved sideways. This is what your shift button does. Shift ciphers have already been demonstrated during the construction of your workbook.
  • Keyword Ciphers. These use a keyword to start the cipher alphabet.
  • Random Ciphers. The letters of the cipher alphabet are randomised or are arranged by some system that makes them appear random.
  • Partial Decode. This workbook can also be adapted to cope with decoding messages when you are guessing letters of the cipher.

Keyword Ciphers.

To generate a keyword cipher the easiest method is to add two more buttons and adapt the code from the existing ENCODE and DECODE buttons.

Type your keyword in at the start of row 3. The keyword cannot contain the same letter twice. (Or else you must eliminate its repetition.) The rest of the alphabet minus the letters of the keyword follows on after the keyword.

Give the buttons suitable names to differentiate them from the other controls.

Open the VBA editor by double clicking on the "ROW 3 ENCODE" button. Then cut and paste the code from the first ENCODE button into your new one. You only need to alter one line of code.

Change the line that reads:

newStr = newStr + Worksheets("Sheet1").Cells(2, countpos).Value

To read:

newStr = newStr + Worksheets("Sheet1").Cells(3, countpos).Value

Save and return to the normal spreadsheet screen and test your new encoding button.

Decoding from the row 3 cipher.

Open the VBA editor by double clicking on the "ROW 3 DECODE" button. Then cut and paste the code from the first DECODE button into your new one. You only need to alter one line of code.

Change the line that reads:

codeStr = codeStr + Worksheets("Sheet1").Cells(2, n).Value

To read:

codeStr = codeStr + Worksheets("Sheet1").Cells(3, n).Value

Save and return to the normal spreadsheet screen and test your new decoding button.

Test your controls very carefully with simple messages to check that they do exactly what you expect. Encode and decode messages by hand to be sure that everything is working.

Random cipher.

Type a random alphabet in instead of one with a keyword but be careful to keep a copy and not to repeat letters.

Trial Decode

If you are guessing a cipher alphabet then type your guesses into row 3. Your guesses will probably arise from frequency analysis or spotting repeated letter groups.

Add an new button to the spreadsheet and call it TRIAL DECODE.

Cut and paste the code from ROW 3 DECODE into its VBA section. Then change the top for loop to cope with blanks in the cipher alphabet in row 3

Change this code:

For n = 1 To 26
codeStr = codeStr + Worksheets("Sheet1").Cells(3, n).Value
Next n

To this:

For n = 1 To 26
trialStr = Worksheets("Sheet1").Cells(3, n).Value
If (trialStr = " ") Then
trialStr = "*"
End If
codeStr = codeStr + trialStr
Next n

And change the line that reads:

newStr = newStr + " "

To this:

newStr = newStr + "*"

Save and return to the normal spreadsheet screen and test your new trial decoding button.

Test your controls very carefully with simple messages to check that they do exactly what you expect. Encode and decode messages by hand to be sure that everything is working.

last updated 4th July 2013