Solution - Experienced Challenge 2: The Secret Word is FGJAJGSHSBSW
So is this what real spies do all day: write Visual Basic for Applications (VBA) macros that decode five-word messages? Well, to be honest, we’re not at liberty to say; that’s classified information.
But as far as we know, yes.
So what kind of macros would a secret agent write? Well, again, we’re not a liberty to say. But if you promise not to tell anyone, we can show you the kind of macro a secret agent writes:
Sub Decode()
Const ForReading = 1
strTextFile = ActiveDocument.Path & "\WordList.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strTextFile, ForReading)
strWordList = objFile.ReadAll
objFile.Close
strWordList = Replace(strWordList, vbCrLf, " ")
For i = 0 To 25
intNonWords = 0
strText = ""
For Each objWord In ActiveDocument.Words
If Len(objWord.Text) > 1 Then
strWord = ""
strTestWord = Trim(objWord.Text)
For Z = 1 To Len(strTestWord)
strLetter = Mid(objWord.Text, Z, 1)
intLetterValue = Asc(strLetter)
intLetterValue = intLetterValue + i
If intLetterValue > 122 Then
intLetterValue = intLetterValue - 26
End If
strNewLetter = Chr(intLetterValue)
strWord = strWord & strNewLetter
Next
strCharacters = " " & LCase(strWord)
intWordFound = InStr(strWordList, strCharacters)
If intWordFound Then
strText = strText & strWord & " "
Else
intNonWords = 1
End If
End If
Next
If intNonWords = 0 Then
Exit For
End If
Next
Selection.EndKey Unit:=wdStory
ActiveDocument.ActiveWindow.Selection.TypeParagraph
ActiveDocument.ActiveWindow.Selection.TypeText LCase(strText)
End Sub
So what exactly does this macro do? Well, we’re not at liberty to – never mind. We’ll just tell you what this macro does.
For starters we define a constant named ForReading and set the value to 1. That brings us to this line of code:
strTextFile = ActiveDocument.Path & "\WordList.txt"
As we noted in the instructions (instructions that, alas, a few people failed to read carefully enough) your macro must decode the encoded phrase. And how are you supposed to know when the phrase has successfully been decoded? That’s easy: if all the decoded words can be found in the text file WordList.txt then the challenge has been solved. That means, of course, that one thing we need to do is read all the words found in WordList.txt. Before we can do that, we need to determine the path to the file (which, conveniently enough, we’ve stored in the same folder as our Word document). That’s what we’re doing here: we’re using the Path property of the ActiveDocument object in order to determine the folder we’re in, then we simply tack \WordList.txt onto the end to get the path to the text file.
Or, for the mathematically-inclined (and assuming our file is in the folder C:\OfficePalooza):
C:\OfficePalooza
+ \WordList.txt
__________________
C:\OfficePalooza\WordList.txt
We next use these two lines of code (and the Scripting.FileSystemObject) to open WordList.txt for reading:
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strTextFile, ForReading)
And seeing as how it’s no fun to open a file for reading without going ahead and reading it, we use the ReadAll method to read the entire contents of the file into a variable named strWordList, then use the Close method to close the file:
strWordList = objFile.ReadAll
objFile.Close
At this point strWordList consists of a list of words similar to this:
abaci
aback
abacus
abaft
To make it easier to search through this list, we’d actually like to have a list of words separated by blank spaces rather than carriage return-linefeeds. You know, like this:
abaci aback abacus abaft
And yes, we did kind of spoil the surprise, didn’t we? Well, now that the cat’s out of the bag, we might as well tell you that we used this line of code (and the Replace function) to search through strWordList and replace all the carriage return-linefeeds (vbCrLf) with blank spaces:
strWordList = Replace(strWordList, vbCrLf, " ")
Note. Are there other ways we could have tackled this problem? In a word: yes. However, we had this code leftover from a script we wrote a few years ago, and, in the spirit of the times, decided to recycle it.
And now we’re ready to do some real spy stuff. To begin with, we set up a For Next loop that runs from 0 to 25:
For i = 0 To 25
Why 0 to 25? Well, as you might recall, our message has been encoded using a simple letter substitution cipher. With this type of cipher, you might use the letter C to represent the letter A; C is 2 more than A. So what letter would we use to represent the letter B? You got it: the letter D, which is 2 more than B. A loop than runs from 0 to 25 lets us cover all the possibilities in the English language, with 0 meaning that our message hasn’t been encoded after all (in which case A means A) all the way to 25 (in which case Z means A).
Anyway, that’s why our loop runs from 0 to 25.
After initializing a pair of variables (intNonWords and strText) we then use this line of code to set up a For Each loop that runs through the collection of all the words found in the document:
For Each objWord In ActiveDocument.Words
As it turns out, all the items found in the Words collection include any blank spaces that follow those words. Because of that, we use this block of code to delete the blank spaces that follow each word (and to bypass any items that consist solely of a blank space):
If Len(objWord.Text) > 1 Then
strWord = ""
strTestWord = Trim(objWord.Text)
After we remove the blank spaces from our word we then execute this block of code:
For Z = 1 To Len(strTestWord)
strLetter = Mid(objWord.Text, Z, 1)
intLetterValue = Asc(strLetter)
intLetterValue = intLetterValue + i
If intLetterValue > 122 Then
intLetterValue = intLetterValue - 26
End If
strNewLetter = Chr(intLetterValue)
strWord = strWord & strNewLetter
Next
What we’re doing here is setting up a For Next loop; this loops runs from 1 to the total number of characters found in the word. (We can determine the total number of characters by using the Len function.) We use the Mid function to extract the first letter, then use the Asc function to convert that character to its ASCII value (for example, the lowercase a has an ASCII value of 97). After calculating the ASCII value of the first letter we then use this line of code to add the value of the counter variable i to that ASCII value:
intLetterValue = intLetterValue + i
Why do we do that? Well, suppose in our cipher the letter b represents the letter a. Suppose, too that the counter variable has a value of 1. If we add 1 to 97 we get 98. And 98 is – tah-dah! – the ASCII value of the letter b. If we make this same substitution for all the letters in all the words, we’ll decode the message.
But what if our letter substitution is, say, 10, and the first letter in our message is a z? The lowercase z has an ASCII value of 122. If we add 10 to 122 we get – well, we don’t get a letter; in fact, no letter has an ASCII value of 132. (The lowercase z has an ASCII value of 122, which is as high as we can go and still have a letter.) In a case like that, we need to loop around and start again with the letter a (ASCII value of 97). This block of code checks to see if our letter value is more than 122 and, if so, corrects for that by subtracting 26 from the value (26 representing the number of letters in the English alphabet):
If intLetterValue > 122 Then
intLetterValue = intLetterValue - 26
End If
Note. If you don’t see how this works, trying drawing it out for yourself. That should make the logic a little more clear.
After we calculate the new ASCII value we then use the Chr function to turn this number back into a letter. We tack this letter on to a variable named strWord, then go back to the top of the loop and repeat the process with the next letter in the encoded word.
What does that do for us? Well, the first “word” in our encoded message is this:
ndj
If we’re simply adding 1 to each letter then our new word will be equal to this:
oek
So is oek a real word? There’s only one way to find out: we need to check the list of words we loaded in from our text file. That’s what we use these two lines of code for:
strCharacters = " " & LCase(strWord)
intWordFound = InStr(strWordList, strCharacters)
In line 1, we simply prepend a blank space to our word (remember, the words in strWordList are separated by blank spaces) and then use the LCase function to convert all the letters in the word to their lowercase equivalents. We then use the InStr function to search strWordList for our decoded word.
So what’s next? This:
If intWordFound Then
strText = strText & strWord & " "
Else
intNonWords = 1
End If
If intWordFound is not equal to 0 that means that we found the word; consequently we add this decoded word (and a blank space) to the variable strText; we’ll use strText to keep track of the decoded phrase. If intWordFound is equal to 0 that means that the word wasn’t found; as a result, we set the value of the variable intNonWords to 1.
Oh, and guess what: if intNonWords is equal to 1 that means we haven’t decoded the message after all; remember, all the words in the message must be found in the text file. Because of that, there’s no sense in using this same cipher value to check the next word; it’s not the right one. Therefore, we use this block of code to exit our For statement:
If intNonWords = 0 Then
Exit For
End If
That takes us back to the top of the loop, where we try again with the next substitution value.
No wonder it’s so difficult to find good spies these days; this is hard work, isn’t it?
Eventually, of course, we will decode the message; all the decoded words will be found in the text file. When that happens, we run these three lines of code:
Selection.EndKey Unit:=wdStory
ActiveDocument.ActiveWindow.Selection.TypeParagraph
ActiveDocument.ActiveWindow.Selection.TypeText LCase(strText)
Here we’re using the Selection.EndKey method to move to the end of our document (hence the constant wdStory). We use the TypeParagraph method to add a blank line to the document, then use the TypeText method to type the decoded message. The net result? An encoded message that started out looking like this:
ndj wpkt hdakts Iwt rwpaatcvt
Will end up looking like this:
you have solved the challenge
And there you have it: you’ve not only solved Challenge 2 in the Experienced Division, but you now have all the skills you need to become an international spy. Say hi to James Bond for us!