Simple Shift Cipher Part 1

Aim

You are going to use Excel VBA to develop code cracking tools.

VBA stands for Visual basic for applications. It is a very powerful programming language which hides behind microsoft Excel. You can use it to perform operations on strings of letters such as codes.

These are the types of tool that this webpage will seek to develop:

Alphabet Shifting

Alphabet Shifting

This uses a Spin Button to shift a cipher alphabet relative to the plain text alphabet.

Open a new Excel workbook and type the alphabet across the first row of cells.
Then get the worksheet into Design Mode by clicking on the Design Mode toggle.

NB Once you have typed the alphabet in click, tools, options and the custom lists tab. Then use the import from cells option to add the alphabet to your custom list profile. This will save you a lot of effort in your code breaking career.

Now click on the Spin Button object to select it.

Now left click on your workbook and then drag across before releasing to place a Spin Button object on the workbook.

The blobs indicate that the object is in design mode. If you right click on it then you can get at its properties menu.

Change the three fields shown:
LinkedCell to AA1
Max to 26
Min to 0

Right click on the Spin Button object to open up its property menu.

Now exit design mode by clicking on the design mode icon again. The blobs should have disappeared from around your control.
Click on the control and watch what happens to the value in cell AA1

Now copy and paste this code, exactly, into cell A2 and press return.
Then copy it right across the 2nd row.
Use left click and drag to do this.

=CHAR(65+MOD((CODE(A1)-65+$AA$1),26))

Code returns the ASCII (computer code) for the letter in cell A1
A has a value of 65 so 65 is taken off to start the alphabet at value 0.
Then the value of cell A1 is added. The $ signs make this an absolute reference so that the formula isn't upset when you copy it along the row.
MOD value, 26 reduces your number modulo 26. That is it gets it back into the start of the alphabet if it shoots off either end.
Finally CHAR turns the number that you have got back into a letter, having added 65 back on again.

If all has gone well then for a shift of 9 you should have a cipher alphabet that looks like this.

last updated 13th June 2011