I was thinking about passwords recently. How I got from thinking about passwords to this post is a long and boring story, so I’ll spare you the mental chain. The end of that chain was this thought: I can take the letters F and R and put a vowel in between them and make a word for every vowel in the alphabet. I wonder how many other letter pairs exist with this property.
No doubt your first thought is that FER is not a word. You’re right. I very conveniently forgot that vowel. Had I more diligently tested F and R, though I may not have wondered what the other letter pairs are and I wouldn’t have wrote some code to find out. To wit
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Sub MakeWords() Dim i As Long, j As Long, k As Long Dim vaVowels As Variant Dim sWord As String vaVowels = Array("a", "e", "i", "o", "u") '97 is the ASCII code for lower case 'a' For i = 97 To 97 + 25 For j = 97 To 97 + 25 For k = 1 To 5 'should have used 0 to 4 here sWord = Chr$(i) & vaVowels(k - 1) & Chr$(j) If Application.CheckSpelling(sWord) Then Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = sWord End If Next k Next j Next i End Sub |
VBA Note: I can convince myself that i, j, and k are good enough variable names because this is one-off code that I’ll never use again. But there’s no excuse for not remembering that Array produces a zero-based array. Once I got my run-time error, I went with the vaVowels(k-1) fix.
I got my list of words and wrote a couple of formulas.
To determine if the first and last letters of the current row matched those of the row above, I wrote
=AND(LEFT(A2)=LEFT(A1),RIGHT(A2)=RIGHT(A1))
Trivia Note: Did you know that the second arguments of LEFT and RIGHT are optional and default to 1? Did you know that they’re not optional in Access? Strange but true.
If I got four TRUEs in a row, I’d know that I’d got every vowel, so I wrote the array formula
{=AND(B2:B5)}
There are definitely less cryptic ways to write that formula, but it’s the first to come to mind. B2:B5 resolves to an array of TRUEs and FALSEs and the AND worksheet function will return TRUE only if all of them are TRUE. I applied an Autofilter to get the TRUEs in the last column to get my list
Fewer than I thought. Here are the words I never use:
- pap: worthless ideas (hmmm, this seems oddly familiar)
- pip: a disease of poultry
- sep: this isn’t really a word. It’s an abbreviation of September, an acronym for search engine placement, an acronym for self employed pension, and an acronym for somatosensory evoked response.
In British English a ‘pip’ is the seed of certain fruit, most notably the apple. It has several other slightly obscure meanings too. http://www.chambersharrap.co.uk/chambers/features/chref/chref.py/main?query=pip&title=21st
In American English, a ‘Pip’ is a member of Gladys Knight’s group of backing singers.
This is a great post. I’m a complete word-nerd, so this kind of thing is really interesing (in a sad, geeky kind of way).
There’s one other similar conundrum that has always intrigued me:
What 3-letter suffix can be added to the most letters of the alphabet to make valid 4-letter words?
ie -All
Ball
Call
Fall
Gall
Hall
Mall
Pall
Tall
Wall
Not bad, but is there a better one?
Immediate thought – ang Makes 12
bang
dang
fang
gang
hang
lang
pang
rang
sang
tang
wang
yang
A couple of notes regarding these two lines from your posted code…
vaVowels = Array(“a”, “e”, “i”, “o”, “u”)
…..
For k = 1 To 5 ‘should have used 0 to 4 here[
and this line from our VB Note…
But there’s no excuse for not remembering that Array produces a zero-based array.
To have avoided the problem you encountered with the For statement, you could have used this instead…
For k = LBound(vaVowels) To UBound(vaVowels)
so that you wouldn’t have had to worry about the bounds of the array produced by the Array function. And that is what you should have used because the Array function does **not** always produce a zero-based array… the lower bound is dependent on the Option Base statement. The default for the Option Base statement (for when it is not explicitly specified) is 0; however, if you use this…
Option Base 1
at the top of your code window; then *almost* all arrays created in VB where the lower bound is not explicitly set to something other than 1 would have a lower bound of 1… this includes arrays produced by the Array function. So using 0 to 4, and/or your workaround of using vaVowels(k-1) with specified bounds of 1 to 5 only work for programmers who use an Option Base of 0 (whether explicitly specified or not). I’m guessing you are wondering why I highlighted the word “almost” a moment ago, right? That is because there is an exception to this rule… the Split function **always** produces a zero-based array no matter what the Option Base is set to. So, you could have used this to produce the vaVowels…
vaVowels = Split(“a e i o u”) ‘when a delimiter is not specified, Split uses a space
and then using this “For k = 0 To 4? and “vaVowels(k)” would always work.
Excel doesn’t like lang or wang, so it only comes out to 10. Excel isn’t quite the OED though. I have one at 13 and several at 12. I’ll post the code tomorrow.
“-all” was the first example I’ve come up with. I’ve certainly hit 13 once or twice (while on long car journeys!) but can’t remember what the suffixes were.
Excel’s spell checker certainly isn’t the OED, but there are some Scrabble word lists online
(http://www.scrabble.org.au/words/fours.htm)
Dick, if you can do this in code, you’ll answer a question that’s plagued me for sometime, and a virtual beer will head your way.
Andy –
-ill has 12 (13 counting the proper name Jill)
Bill, Dill, Fill, Gill, Hill, Kill, Mill, Pill, Rill, Sill, Till, Will
Dick
Ah – indeed: it seems to list Lang and Wang as Proper nouns – the Scots might disagree.
Some “words” Excel accepts are clearly nonsense – such as “zzzz” – but they are for whatever reason in the dictionary.
In English English (UK) the longest Excel accepts (all lower case) is -ays (13) therefore quite rightly excluding Kays (the set of women called Kay), Mays (The month of May in more than one year), Tays (a surname).
However, since “kay” is the 11th letter of the alphabet according to the OED I might have expected to be able to count all the “kays” …
M
@Mpemba
Good work, although you’re straying into the world where pedants might argue you’re cheating! Making any word a plural simply because one can always ask “How many _Septembers_ are there in this sentence?” goes against the spirit a little.
:-)
-uck has only ten. But some of them are of a substantially higher quality.
Actually, FER is a legitimate Scrabble word (and so are FAR, FIR, FOR, and FUR). Therefore, your “first thought” was correct.
Suggeted code change
Replace
For i = 97 To 97 + 25
with
Thanks Jamie. Fewer characters and infinitely more readable.
I’m surprised nobody brought this up but a “pip” is a single spot on a die. As in the number 6 on a six sided die is composed of 6 pips.