Personal Project with Macro Programming

I’ve started practicing with macros again for preparation for next year because I need to remain familiar with macro programming. And it’s just so amazing how FAST macros run. They are not foolproof though, because I think sometimes something is running in the background on my laptop and that something “jiggles” the macros, making errors pop up. So that’s the one little drawback with macros.

But the speed and the accuracy can’t be beat.

I decided to do my own little game or project, so I’ve been developing a macro to do cryptography. Ha ha, I am not a cryptography expert; I have no idea what I’m doing. I’m just going along to see how it would work. So I found a random phrase to use and decided, first I would create a macro that would encrypt the phrase.

It took a while but I finally figured out one way. It is basically a find and replace but some tricks had to be deployed because the typical find and replace replaces all letters at the same time which you really don’t want to do. For example, say you first replace all a’s with b’s. Next, when you go to replace all b’s with e’s, the typical find and replace will replace all of the b’s that had previously replaced the a’s. See the problem?

So the following paragraphs describe the encryption process:

Step 1: Randomly assign a code for each letter of the alphabet. I first assign numbers 1 – 26 to the letters, with a being 1, b being 2 and so on. These letters and numbers reside in columns O and P (for example). Then in column B (as an example) I have the same alphabet again but in column C, I will reference to a cell that has a randomizer assigning each letter a random number. Step 2 will detail why column C references to cells with randomizer formula rather than contain the formula itself. In order to prevent letters having the same numbers, I will use a large range of numbers, so I used RANDBETWEEN(1, 1000) in the reference cell. Column D then ranks column C in numerical order (this ranking ends up being 1 through 26). In Column E, I then deployed VLOOKUP to look up the rank (which is between 1 and 26 in column D) in column O and find the corresponding new letter in column P. So in my example, letter a had a random number of 862 which ranked 5 in the list of randomly generated numbers. The number 5 equates to e. So all a’s will become e.

Step 2: Lock the random numbers. Because RANDBETWEEN generates new random numbers each time you hit enter, we need a way to “lock” the random numbers. I think I have talked about this before. My method basically involves a private worksheet macro that initiates when a certain cell changes. So in my example, every time cell C1 changes, the private macro starts to run. If cell C1 contains “x”, then the private macro locks the random numbers. If cell C1 is blank, then column C will be free to change each time you hit enter. Remember in Step 1 I said that column C will reference cells that have a randomizer? Well, column C actually has the following formula: =If($C$1 = “x”, S5, T5). Column T contains the formula RANDBETWEEN(1,1000) and S is a hard copy of the column T – this is the locking column.

The macro itself looks like:

Worksheet is in the left hand dropdown box at the top (instead of General) and Change is in the right dropdown box (instead of Declarations)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$C$1” Then
Application.Calculation = xlCalculationManual
If Range(“C1”) = “x” Then
Range(“T5:T30”).Select
Selection.Copy
Range(“S5:S30”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(“B1”).Select
End If
Application.Calculation = xlCalculationAutomatic
End If
End Sub

This is a very simple piece of code. I turned off the calculation and then turned it back on in the end, mainly in the hopes of speeding things. You can see that column T with the randomizer formula is copied over to column S.

Step 3: Layout the translation area. On another tab, I set up a section for encrypting a phrase that I found. The tab is called “Translate”. I set up cells E1 to E14 to contain the phrase to be encrypted. In column A I use the LEN formula to determine how many letters there are in the phrases, so cell A1 contains LEN(E1).

Step 4: Build macro to change encrypt letters, row by row, letter by letter. The macro itself is a regular macro, like one you would record so it can be found via Developer tab – Macros. The macro is called “Translate”. The macro works by going through each row and then each letter or space and replacing each letter with the encryption created in steps 1 and 2.

The full code is found below. Variable i is set for searching each row and determining if the row contains a phrase. Variable j goes through each letter in the phrase and replace the letter with its encryption code. “Assigning letters” is the tab with the encryption (the locking and the randomizer).

Note there are variables oldphrase and oldletter. Oldphrase is important because you don’t want to go back and change the encrypted code again, so oldphrase will hold only the encrypted codes. When the macro starts on a new row, the first letter is j=1, but there is a line oldphrase = Left(Cells(i, 5), j – 1) where j-1 is basically zero. So j-1 means no letters are in the oldphrase when j=1. To repeat, oldphrase just holds the encrypted portion of the phrase.

Oldletter is just the current letter the macro is working on to encrypt, so think of this as oldletter(j)

Newletter is the encrypted oldletter, so think newletter(j) as an encryption of oldletter(j). This encryption is done by using VLOOKUP. It looks like that in order to use a regular Excel function in a macro program, you use Application.WorksheetFunction before the Excel function.

Newphrase will contain only the portion starting from position j while oldphrase contains the encrypted letters 1 through j-1. The line newphrase = Replace(Cells(i, 5), Mid(Cells(i, 5), j, 1), newletter, j, 1) has a new code Replace (it’s new to me). To generalize it, it is usually denoted as Replace(the string you want to search and replace, the string you want to replace, what you want to replace with, starting from what position, and how many positions to replace). So the string you want to search and replace is simply the current row (Cells(i.5)). The string you want to replace is basically the current letter you are working on (Mid(Cells(i,5),j,1)). What you want to replace with is simply the encryption code or the newletter. You will start the replacement at your current position, or j. Finally, you will replace only 1 letter. If 1 had been -1, then Replace would have replaced all letters in the row that looks like the current letter.

If you want to learn more about the Replace code, click on the ? when you are in the VBA section, then enter “vba excel replace” in the search box. From the selections that next appear, choose “range.replace method (excel)”.

[divider]Translation Macro[divider/]

Sub Translate()
‘
‘  This macro first test each row in column E for words, and then goes letter by letter in each row, replacing each letter with its new code in “Assigning letters”.
‘  The code is found in columns J and K in “Assigning letters”.
Dim oldletter As String
Dim newletter As String
Dim oldphrase As String
Dim newphrase As String

‘i = row where the phrase to be transformed
‘j = letter in the phrase

Sheets(“Translate”).Select
For i = 1 To 14
If Cells(i, 5) <> “” Then
For j = 1 To Cells(i, 1)
oldphrase = Left(Cells(i, 5), j – 1)
oldletter = Mid(Cells(i, 5), j, 1)
If oldletter <> ” ” Then
newletter = Application.WorksheetFunction.VLookup(oldletter, Sheets(“Assigning letters”).Range(“J5:K32”), 2, False)
newphrase = Replace(Cells(i, 5), Mid(Cells(i, 5), j, 1), newletter, j, 1)
Cells(i, 5) = oldphrase & newphrase
End If
Next j
End If
Next I
‘Find and Replace method: does not work because it replaces all letters, even those that have been encrypted already.
‘Selection.Replace What:=oldletter, Replacement:=newletter, LookAt:=xlPart, _
‘    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
‘    ReplaceFormat:=False

MsgBox (“Program is done.”)
End Sub