Category Archives: Excel, Word & VBA
Microsoft Excel, Word and VBA.
When I was a senior in High School in 1981 students in our computer class where given access to the local University’s DECSYSTEM-20 computer. This was an exciting opportunity for our small group of enthusiastic wannabe programmers. We spent many hours at night and during weekends hunched over a terminal or impatiently waiting for a line printer to finish spewing out vast amounts of some documentation that we discovered.
One day we discovered a program so powerful that it could understand regular English commands and had seemingly magical artificial intelligence abilities. Type in a command using regular English and up popped an appropriate and often humorous response. It appeared as smart as HAL from Arthur C Clarke’s 2001 a Space Odyssey, but no, it was just Zork the now classic text adventure game, or interactive fiction as the genre is now called. With today’s computers and internet you may not easily imagine just how awestruck a few school boys could be about a game that was just text based, but believe me we were awestruck. Not amazed? Well perhaps you had to be there. We tried typing all the random things we could think of just to see what the response would be in much the same way that people probably do with now with Siri. We sometimes played it using a regular terminal and sometimes on a DECWriter that had a printer instead of a screen but which meant we could take the printouts home and marvel at them at later.
We were hooked, but not so much in playing it, our main goal was trying to figure out how it worked and how to write our own game. None of us had access to a computer at home, so we could not play Zork much. We spent many hours at home designing and writing code in BASIC that we hoped would work. The three of us each had a different approach and while each had merits, none of them were masterpieces. Even though my game was quite poor and never finished I continued thinking about it for many years.
Zork wasn’t the only text adventure that we played. There was also Colossal Cave Adventure by Will Crowther and Don Woods. Recently an excellent documentary was produced by Jason Scott called Get Lamp that covers the history of text adventure games. He is featured in a 2 hour Google Tech Talk that also shows much of the documentary.
For a brief time text adventures were popular. Now it seems that there is just a small group of enthusiastic fans that write and play games which is keeping the genre alive. You can play Zork online and also it and other interactive fiction games are included in the iPad and iPhone Frotz app.
Now more than 30 years later I’m currently working to complete my semi-serious attempt at creating a text adventure creation system that should be ready in the next few months so I can finally tick it off my bucket list. It is something that I have been quite excited working on. I don’t think it is up there with the higher level IF systems, but my system allows game creation without the need of any programming skills. I’m intending to release it for free and post it here when it is complete.
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.
Many people that use Excel may be surprised to learn that lurking in the back of Excel is a fairly decent programming language called Visual Basic for Applications or VBA and this is available for all users (or at least PC users) to play with. If you want to see it open Excel hold down Alt and press F11. A new window will open that looks quite different to usual Excel windows.
The language is based on Visual Basic but has been modified to make use of the features in Microsoft Office. It is also built into Word and other Office applications and it is there to enable users to create powerful tools within Office that can do a series of tasks or other tasks than cannot be done thie the usual builtin tools.
While it is not designed for creating free standing applications or creating games, to some that has become a challenge. Some quite sophisticated games have been created.
When I started to learn VBA I wrote a couple of games. One of them was a Tic Tac Toe or Noughts and Crosses as it is better known in Australia.
It runs in Excel 2003 and 2007 and probably 2010, but I have not tested it with that. Macros have to be enabled to run the game. See my Excel page to see how to do this. Feel free to modify and distribute it.
Download TicTacToe.xls (75kb)