(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.