When I was young I loved playing with codes and ciphers. When I started to learn about Excel I thought it would be cool to use a spreadsheet with VBA to make it easier to break ciphers. So that’s what I set out to do. What I developed doesn’t break ciphers but it does make it easier to do it myself by replacing all occurrences of a letter when a substitute is chosen.
The spreadsheet is called Excel Cipher Assistant and you can download it in xls format. It works using Excels built-in macros so you will need to allow macros to run for it to work. I have included 10 encrypted messages to test your skill and you can easily add your own encrypted text to work on.
There are instructions within the spreadsheet.
How does it work?
The encrypted text is placed in the ‘Encoded Message’ cell. When the Decode button is pressed the Decode macro cycles through the characters in the Encoded Message cell and checks them against those entered in the ‘substitutions’ section. If a match is found it replaces the character with the one chosen by the user. When all characters have been checked the updated text is placed in the ‘Decoded Message’ cell and then it goes through the text and sets the style bold for each character that has been substituted.
Lately I have been thinking about ways to improve it, but I think I’ll just start again. I will add a post about it if I do. Feel free to modify it or change it.
Back in the ‘70s when I was 10 a friend and I had a secret club and we sent messages using ciphers. We stuck bits of paper with letters on them over the keys on our typewriters with sticky tape. Type in a message using the new letters on the keys and out came an enciphered message. Type an enciphered message and out came a deciphered one. Simple, but due to the different layout of the keys it messed up any typing skills I had.
Since then I have had an interest in cryptography, so when I started playing with VBA in Microsoft Word and Excel I naturally thought of what I could do with them and wrote some macros for Word to encypt and decrypt text using the Caesar ciher and Vigenère ciphers. These are two popular historic encryption methods.
The Caeser cipher
The Caesar cipher is named after Julius Caesar. It is believed that this is one of the ciphers he used to correspond with his army. With it all the letters in the alphabet are moved on a set number of places, in Caesar’s case three places. The recipient simply replaces all letters with those down the same number of places in the alphabet.
The Vigenère cipher
According to the Wikipedia Vigenère cipher article, the Vigenère cipher was originally invented by Giovan Battista Bellaso and was described in his book in 1553. I looked at this cipher when I was 10 but I did not understand it and moved on to others, but after reading about it on the internet I soon learned how to use it and it really is quite simple. It’s a great cipher to learn.
If someone got a copy of the ciphers I used they could easily decode any messages. The Vigenère cipher differs as it uses a keyword or phrase for enciphering and deciphering. Having a copy of the cipher does not help much… well not to most of us anyway. It is the key that you share with those the message is intended that is important.
The Vigenère cipher is a table of 26 Caesar ciphers.
How to use it
Traditionally all spaces are removed from the message. This makes the message harder to break. My Word macro ignores spaces and will leave them if they are included.
A keyword is chosen and written repeatedly above the message for the length of the message. Each letter of the plaintext is encoded by finding the intersection in the table between the plaintext letter and keyword letter. The first letter of the plaintext in the example below is M which is enciphered using the alphabet in row C, which is the first letter of the keyword. This is done by looking at the letter in row M and column C which is O. The rest of the message is enciphered the same way. Deciphering is done the same as enciphering.
Keyword: CATCATCATCATCATCATCATCATCATCATCAT Plaintext: MEETATTHEOLDMILLATTHEENDOFTHEROAD Ciphertext: OEXVAMVHXQLWOIENAMVHXGNWQFMJEKQAW
Another way to think about how the cipher works is to think of each character of the keyword corresponding to a number between 1 and 26. If we look at the keyword ‘CAT’ again and the number corresponding to each characters location in the alphabet we get 3, 1 and 19. These numbers represent how far we need to ‘shift’ each letter. In fact characters are shifted 1 place less than their spot in the alphabet bacuse there is no shift in the first column, so it would actually be 2, 0 and 18. This is the way that the Word macro works.
Once you understand how the cipher works it becomes obvious that the longer the keyword, the stronger the encryption as more ciphers are used in a more complex pattern. Also, the first column in the cipher corresponding to ‘A’ is not encoded at all, so if the single character ‘A’ is used as the keyword the message will not be encrypted.
Using Word to do the work
I made some macros for Microsoft Word that do the hard work of enciphering and deciphering. Simply select text in a document and push the appropriate button and the text will be updated. Download the word document to try it.
Download CaesarAndVigenereCiphers.docm (Word 2007 for PC only 75kb)
You are free to distribute or modify it or use the code for your own purposes. It could be easily improved. Rather than replacing the text in the document, the message could be displayed in a user form. The Caesar cipher could display all 26 deciphered versions of the text in a user form which would make it immediately obvious which one was used
I hope you have fun with it.