Experienced Challenge 6: How Do You Spell That Again?

Office Programs Required	Microsoft Word 2007
Goldfish Awarded	5
Deadline for Submission	Monday, April 27, 2009 (8:00 AM Pacific Daylight Time)
Files Required	Experienced_challenge_6.docm Soundex.accdb in the OfficePalooza Challenge Pack.

So if Microsoft Word already includes a spellchecker then why would you want to create your own spellchecker for Microsoft Word? Well, for one thing, you’d – well, no, come to think of it, that doesn’t make much sense, does it? On the other hand, you might want to – no, that’s an even dumber idea. Still, you might – well, let’s put it this way: if you want to get credit for successfully completing How Do You Spell That Again? then you’ll want to create your own spellchecker for Microsoft Word.

Of course, you can’t just create any spellchecker; instead, you must create a spellchecker based on the Soundex algorithm originally developed by Robert Russell and Margaret Odell. The so-called Russell Soundex consists of a letter followed by three digits (for example, G620). The letter represents the first letter of the word, and the three digits represent the remaining consonants in the word. (Vowels are ignored in this algorithm, unless the first letter in the word is a vowel. The same is true of the letters H, W, and Y.)

To create the three digits in the Soundex do the following:

1. Replace any consonants in the word (except for the first letter, which always stay as-is) with the following values:

Consonants	Value
B F P V	1
C G J K Q S X Z	2
D T	3
L	4
M N	5
R	6

 

2. If there are any identical digits located next to each other, replace those digits with a single digit of the same value. For example, the word STUNNED has a Soundex value of S353. That’s because the letters U and E are ignored, and the back-to-back value 55 (the two N’s) are compressed to the single value 5:

 

3. Remove any non-digits after the first letter. This includes the vowels A, E, I, O, U and the consonants H, W, and Y, none of which get assigned a numeric value.
4. Return the starting letter and the first three digits. If there are less than 3 digits then append enough 0’s to make 3 digits.

Note. Actually, this is a simplified version of the Soundex algorithm. But hey, we didn’t want to make this too complicated for all of you.

So what are you supposed to do with the Soundex algorithm? Well, the event works like this. To begin with, you need to open the file Experienced_challenge_6.docm, a file (found in the OfficePalooza Challenge Pack ) that looks like this:

 

As you can see, we have a single word in this document. Is that word spelled correctly? To tell you the truth, we don’t know. (Well, to tell you the truth, we do know, but we don’t want to spoil the surprise.) What you need to do is modify the macro CheckSpelling and write some Visual Basic for Applications (VBA) code to determine whether or not this word is spelled correctly. And how will your VBA subroutine know whether or not the word is spelled correctly? Well, also in the Challenge Pack is a database file named Soundex.accdb. This file includes a table named SoundexValues. If the word appears in the table then we assume it’s a correctly spelled word. If the word doesn’t appear in the table then it is not a correctly spelled word.

And if the word isn’t correctly spelled? Well, in that case you need to do two things. First, you need to calculate the Soundex value for the word in question. After you’ve done that, you then need to retrieve all the words that have a matching Soundex value. For example, suppose you calculate that the word in the document has a Soundex value of C215. In that case you would retrieve and then – beneath the incorrectly-spelled word – display those words. In other words:

mispell

cheekbone

cacophony

cosponsor

Why this? Because – believe it or not – cheekbone, cacophony, and cosponsor all have a Soundex value of C215.

Note. This is just an example. We’re going to go out on a limb here and suggest that maybe the word mispell doesn’t have a Soundex value of C215.

That’s all you have to do. Incidentally, we’d recommend keeping the spellchecker built into Microsoft Word. But if you want to toss it out in favor of your VBA macro, well, who are we to judge?

Submitting Your Entry

The OfficePalooza sweepstakes is over, but you’re welcome to try the challenges and learn on your own. Good luck!