Macro Practice Part II

(Still working on the new WordPress theme so the site is still messy.)

Yesterday I finished posting part I of my macro practicing which was basically the game of encrypting a phrase. Today, I will note some of the macros I developed for preparing to decrypt. I haven’t figured out how to decrypt but I can prep for what I think would be a set up for decryption.

Right now I have basically five macros: 1) clear contents; 2) text to columns; 3) count letters; 4) list words; and 5) decode.

I have created buttons that are shown on a tab called “Decode”. You press the buttons and the assigned macro runs. To create these buttons, go to the Developer tab, then in the controls section click on Design Mode, and then click on the icon that looks like a toolbox. That toolbox will say “Insert”. Upon clicking that toolbox, you will be given a list of “forms”. I used the first one: “Button”. After clicking on the “Button” form, drag your mouse over to where you want the button to reside and make a square. After creating your button, Excel will ask you which macro you want the button to be associated with. And that’s it to creating buttons with macros assigned to them.

Next, I will give a brief description of the layout of the “Decode” tab so that the macro program section will make more sense.

In the “Decode” tab, I have reserved cells E1 to E14 to hold the encrypted phrases. Then cells A18 to A32 contain numbers 1 through 15. B18 to B32 counts up how many words contain 1 letter, how many contain 2 letters and so on up to 15 letters. Then cells D18 to L32 is reserved for a listing of those words so I can see what kind of words there are.

Cells E50 to AM60 contain the LEN() formulas that counts up the number of letters in each word. This section feeds into cells B18 to B32 mentioned above. Below that in cells E70 to E83 I am just retaining the original encrypted phrases.

Rows 85 to 98 splits up the words into the individual letters so I can count up the letters used in the encrypted phrase.

Then finally, rows 99 to 113 is reserved for showing the decrypted phrase.

Whew! I think I’m going to have to upload that tab so you can see the visual of how that tab is laid out. The visual will help in following the macros. In this embedded document, you won’t be able to see the macro buttons.

The first macro is basically a clearing macro which clears out the last decryption exercise to start the page clean. So it clears out the encrypted cells E1 to AM14, the original encrypted phrase in cells E70 to E83, the decrypted phrase, rows 85 to 98 holding all of the letters, section D18 to L32 holding the words and finally section S19 to S46 which holds my guess for what the decrypted letter should be. The macro consists basically the ranges.ClearContent.

[ap_divider color=”#CCCCCC” style=”solid” thickness=”3px” width=”100%” mar_top=”20px” mar_bot=”20px”]

Clear Contents Macro

Range(“E1:AM14”).ClearContents
Range(“E70:E83”).ClearContents
Range(“E100:E113”).ClearContents
Range(“85:98”).ClearContents
Range(“D18:L32”).ClearContents
Range(“s19:s46″).ClearContents

[ap_divider color=”#CCCCCC” style=”solid” thickness=”3px” width=”100%” mar_top=”20px” mar_bot=”20px”]

After you use the Clear Contents macro, I can then paste the encrypted phrase in cells E1 to E14. The first thing I want to do with this encrypted phrase is to separate out the “words” into columns using the Text to Columns feature in Excel. My second macro calls for this Text to Column feature. You will note that the macro first copies the original encryption and saves it to cells E70 to E83, so I can retain it and to cells E100 to E113 so I can decrypt it.

[ap_divider color=”#CCCCCC” style=”solid” thickness=”3px” width=”100%” mar_top=”20px” mar_bot=”20px”]

Text to Columns macro

Range(“E1:E14”).Select

‘First copy the selection before separating out the words
Selection.Copy
Range(“E70”).Select
ActiveSheet.Paste
Range(“E100”).Select
ActiveSheet.Paste

Range(“E1:E14”).Select
Application.DisplayAlerts = False
Selection.TextToColumns Destination:=Range(“E1″), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, TrailingMinusNumbers:=True
Application.DisplayAlerts = True

End Sub

[ap_divider color=”#CCCCCC” style=”solid” thickness=”3px” width=”100%” mar_top=”20px” mar_bot=”20px”]

Now that the phrases have been broken up into words in rows 1 to 14, section E50 to AM63 uses the LEN() formula to count up the number of letters in each word. E50 to AM63 feeds into cells B18 to B32 to show how many words contain 1 letter, how many contain 2 letters and so on.

Next, I want to break up the phrases even further into letters, which is what macro #3 does.

[ap_divider color=”#CCCCCC” style=”solid” thickness=”3px” width=”100%” mar_top=”20px” mar_bot=”20px”]

Break into letters macro:

Sub SeparateLetters()
‘
‘i = row where the phrase to be transformed
‘j = letter in the phrase

Sheets(“Decode”).Select

For i = 1 To 14
If Cells(69 + i, 5) <> “” Then
For j = 1 To Cells(69 + i, 1)
Cells(84 + i, 4 + j) = Mid(Cells(69 + i, 5), j, 1)
Next j
End If
Next i

MsgBox (“Program is done.”)
End Sub

[ap_divider color=”#CCCCCC” style=”solid” thickness=”3px” width=”100%” mar_top=”20px” mar_bot=”20px”]

Now remember, cells E70 to E83 contain the original encryption (the code cells(69 + i, 5) refers to E70 to E83). The macro is going to go through each phrases and pick out each letter using the Mid(cells(69 + i, 5), j, 1). The letter i refers to the row and j refers to the letter position in that row. The code Cells(84 + i, 4 + j) is where the single letter will go, which is basically cells E85 to ??98.

I think I will stop here since I have covered 3 macros so far. Tomorrow, cross my fingers I have time, I will finish up with the remaining macros that I have.