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