• Home Page
• Codes
• Board Games
• Classic Squad Leader
• Panzer Blitz etc.
• Ancients
• Logo

Custom Lists

It is very useful to customise Excel so that it will autofill with the alphabet if you right click and drag having only typed in AB etc.
To do this first type in the alphabet and then left click on the tools tab and left click on options.
Click on the custom lists tab and import your alphabet. Left click the table tab at the RH end of the textbox and then select the alphabet from your spreadsheet. Excel should now recognise the alphabet and autofill for you.

Viginere

You need to have the alphabet entered across the top row of your spreadsheet and then enter it starting from row 2 down the 1st column of the workbook.

Right click on the tab at the bottom of the spreadsheet and rename the page "viginere."

Your workbook should look like the image below.

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

Enter the code below into cell B2 and press return. The letter B should appear.

=CHAR(65 + MOD( (CODE( A2)-64),26))

Now left click on B2 and then left drag it by its bottom RH corner to autofill the entire table with shifted alphabet rows.

Your workbook should look like the image below.

ENCODE Button

To encode a message with the viginere tablet one starts using the cipher in row 2 for the first letter, that in row 3 for the 2nd letter and so on. After the 26th letter you return to the top row.

The message "message" would encode as "MFUVELK"

To achieve this in VBA add two text boxes and two buttons for ENCODE and DECODE. Also add a cell to use to control how many rows of the tablet are used. I have used cell AF10 and coloured it yellow to remind me that this is a value that I can change. Some versions use a codeword to start the tablet of whilst others just use 5 random rows.

Go into design mode and double left click on the ENCODE button. The cursor is automatically positioned in the correct place for adding VBA code to the 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 return to your workbook and exit from design mode. Design mode is entered or left by left clicking its toggle switch.

This code is very similar to that in the shift cipher system. There is an extra variable rowInd which keeps track of which alphabet row to use for encipherment. If rowMax is reached then row Ind is reset to 2. The code has been adjusted so that the cipher letter is chosen from the correct row. rowInd isn't incremented for spaces.

DECODE Button

This code is very similar to that of the shift system DECODE button. It has the modifications outlined above to ensure that the correct code line is used to decode the message.

It is vital to test your system thoroughly to check that it functions as expected. The way to do this is to use only two rows and use message with repeated letters and spaces.

Refinements

You could use a codeword to seed the viginere instead of the alphabet.

Running Key

This uses a text as the key. Use a famous speech etc.

Autokey

This uses a keyword or passage to start the encipherment of but then switches to using either the plaintext or ciphertext as the viginere key.

Code for viginere 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
rowInd = 2
rowMax = Worksheets("viginere").Cells(10, 32).Value + 1
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 + " "
rowInd = rowInd - 1
End If
If (countpos < 27) Then
newStr = newStr + Worksheets("viginere").Cells(rowInd, countpos).Value
End If
rowInd = rowInd + 1
If (rowInd > rowMax) Then
rowInd = 2
End If
Next i
TextBox2.Value = newStr
End Sub

Code for viginere DECODE 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 CommandButton2_Click()
rowInd = 2
rowMax = Worksheets("viginere").Cells(10, 32).Value + 1
newStr = ""
sourceStr = TextBox1.Value
For i = 1 To Len(sourceStr)
codeStr = ""
For n = 1 To 26
codeStr = codeStr + Worksheets("viginere").Cells(rowInd, n).Value
Next n
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 + Worksheets("viginere").Cells(1, countpos).Value
End If
If (countpos = 27) Then
newStr = newStr + " "
rowInd = rowInd - 1
End If
rowInd = rowInd + 1
If (rowInd > rowMax) Then
rowInd = 2
End If
Next i
TextBox2.Value = newStr
End Sub

Keyword

Autokey