Parsing the Bible

An AOL-user asks J-Walk:

An AOL User: What five letter word appears in the KJV only four times? It was made before Adam and named by Adam. Had no legs, arms or soul. Was given a soul and it was taken back.

Using J-Walk’s Bible in Excel, I listed all of the five letter words that appear only four times. Here’s the code:

Sub FindWord()
    Dim sh As Worksheet
    Dim rCell As Range
    Dim colWords As Collection
    Dim vaWords As Variant
    Dim i As Long
    Dim sText As String
    Dim vaRemove As Variant
    Const lLEN As Long = 5
    Const lFREQ As Long = 4
    Set colWords = New Collection
    vaRemove = Array(“,”, “.”, “:”, “;”, “!”, “?”, “(“, “)”)
    For Each sh In ThisWorkbook.Worksheets
        For Each rCell In Intersect(sh.Columns(2), sh.UsedRange).Cells
            sText = rCell.Text
            For i = LBound(vaRemove) To UBound(vaRemove)
                sText = Replace(sText, vaRemove(i), “”)
            Next i
            vaWords = Split(sText, ” “)
            For i = LBound(vaWords) To UBound(vaWords)
                If Len(vaWords(i)) = lLEN Then
                    On Error Resume Next
                        colWords.Add vaWords(i), CStr(vaWords(i))
                    On Error GoTo 0
                End If
            Next i
        Next rCell
    Next sh
    For i = 1 To colWords.Count
        Sheet1.Range(“G2”).Value = colWords(i)
        If Application.WorksheetFunction.Sum(Sheet1.Range(“G5:G70”)) = lFREQ Then
            Debug.Print colWords(i)
        End If
    Next i
End Sub

It uses the “Stats” page in the workbook to compute the number of times each word appears. This is not a good way to do it, because each word causes an expensive recalc. But I didn’t want to spend time writing my own algorithm. I started the macro and came back 20 minutes later.

Posted in Uncategorized

13 thoughts on “Parsing the Bible

  1. Dick, there is actually quite a simple way to do the word count. You first set up an array alongside the collection, to hold the count, and another array, to hold the matching list of words, like so
    Dim wordCount(100000) As Long
    Dim Words(100000) As String

    Then where you add to the collection, you do it like this…
    colWords.Add colWords.Count + 1, CStr(vaWords(i))
    ..this will give you the correct location in your arrays to store each word and increment the count
    This line stores the word if it hasn’t been seen before (immediately after the line above)
    If Err.Number = 0 Then Words(colWords.Count) = vaWords(i)

    and after you close the error trap, you increment the word count
    On Error GoTo 0 ‘from your code
    j = colWords(CStr(vaWords(i))) ‘look up the array location in the collection
    wordCount(j) = wordCount(j) + 1

    Then it’s simple to run through the arrays when you’ve finished, looking for those with a count of 4

    This technique of putting an incrementing counter in a collection is very useful where you want to store data for each of the unique items in a collection, and I’ve used it many times.

  2. use a dictionary iso a collection, and keep count while you’re finding the words. also use an array of string iso a variant for a split’s result. set the dic’s .CompareMode = TextCompare to avoid missing words due to capitalization.

     For w = 0 To UBound(asWords)
        If Len(asWords(w)) = 5 Then
          If dWord.Exists(asWords(w)) Then
             dWord(asWords(w)) = dWord(asWords(w)) + 1
             dWord.Add asWords(w), 1&
          End If
        End If

    Top 6:

    Shall and shaltin the top6… maybe that’s why I’m no bible fan(atic). Unique words used to wrote the bible: 13018..

    cheerz from amsterdam

  3. Q: “What five letter word appears in the KJV only four times?”
    A: abase, Abiah, adder, Ahiah, Alpha, alter, amiss, Annas, apply, Arpad, Ashan, Attai, Azgad, baked, barns, bells, BIBLE, blame, blown, brown, chide, climb, cloak, crush, cured, darts, deeps, Derbe, Deuel, dowry, drops, Dumah, edges, Epher, Etham, extol, fears, filth, fitly, flute, fresh, Gaash, Golan, gross, heels, hoary, Hosea, Italy, Jabez, Jebus, knock, laded, lothe, lover, Mesha, Micha, navel, nests, Oboth, Omega, outer, owest, pains, Pallu, piped, print, privy, quake, ranks, ruddy, Salem, Salma, seize, sever, shaft, shear, shorn, sores, stank, stays, steel, stoop, stout, stump, swell, swept, taxed, Timna, title, Uriel, Uzzah, viper, whips, wiped, woven, wrung, yokes, Zohar

    Using a regular expressions object with a dictionary object (cheers, Dave):
    Finished in 4.7033593749984 seconds

    Sub KJV5LetterWordCount()
    Dim Tmr As Double ‘runtime timer
    Tmr = Timer

     ‘found copy at
    Dim vFF As Long, EntireBible As String
     vFF = FreeFile
     Open “C:kjv12.txt” For Binary As #vFF
     EntireBible = Space$(LOF(vFF))
     Get #vFF, , EntireBible
     Close #vFF
     ‘make regex collection of 5 letter words, put uniques into dictionary object
    Dim RegEx As Object, RegC As Object, RegM As Object, Dict As Dictionary
     Set Dict = CreateObject(“scripting.dictionary”)
     Dict.CompareMode = 1 ‘1=TextCompare
    Set RegEx = CreateObject(“vbscript.regexp”)
     With RegEx
      .Global = True
      .MultiLine = True
      .Pattern = “[A-Za-z]{5}” ‘5 letter word
    End With
     If RegEx.Test(EntireBible) Then
      Set RegC = RegEx.Execute(EntireBible)
      For Each RegM In RegC
       If Dict.Exists(RegM.Value) Then
        Dict(RegM.Value) = Dict(RegM.Value) + 1
        Dict.Add RegM.Value, 1
       End If
     End If
     Set RegEx = Nothing
     Set RegC = Nothing
     Set RegM = Nothing
     ‘Put 5 letter words and their associated count onto new one-sheet workbook
    Dim AWord As Variant, TheWords() As Variant
     Application.ScreenUpdating = False
     With Workbooks.Add(-4167).Sheets(1)
      .Name = “5 letter words”
      .Range(“A1:B1”).Value = Array(“5 Letter Word”, “Word Count”)
      TheWords = Dict.Keys
      For Each AWord In TheWords
       If Dict(AWord) = 4 Then ‘”What five letter word appears in the KJV only four times?”
       .Range(“A1”).Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 2).Value = _
         Array(AWord, Dict(AWord))
       End If
     End With
     Application.ScreenUpdating = True
     Set Dict = Nothing

    Debug.Print “Finished in “ & Timer – Tmr & ” seconds”
    End Sub

  4. Shoot.. one slight change for late-binding:

    ‘Dim RegEx As Object, RegC As Object, RegM As Object, Dict As Dictionary
    Dim RegEx As Object, RegC As Object, RegM As Object, Dict As Object
  5. I am surprised that you didn’t return some thing like “Paste”, “Chart”, “Table” and “Query”. This is my preferred Good Book by King John W.

    (ok I am sure they appear far more in your book)

  6. Don’t know if you are interestedin finding the word or an answer to a riddle.

    The whale had no soul but was given a soul in the form of Janah which it swallowed. Then Joanh was taken from the whale.

  7. The Bible does not mention a whale in Jonah’s story (or at all). Just says “a great fish”… neither does it say it was an Apple that Adam and Eve ate at the Garden. Very interesting nontheless…

  8. Quoted from Visitor: 27 June, 2007 12:39 pm
    “The Bible does not mention a whale in Jonah’s story (or at all). Just says “a great fish”… neither does it say it was an Apple that Adam and Eve ate at the Garden. Very interesting nontheless…”

    Actually the Bible does mention the word whale in the King James Version. But in the story of Jonah, it is called a big fish. The word whale is mentioned in Gen. 1:21, Job 7:12, Eze. 32:2 and Matt. 12:40. And Matthew is the one that mentions the story of Jonah. “For as Jonas was three days and three nights in the whale’s belly…” So if you are using the KJV of the Bible, then the word whale is mentioned and is very much the answer to the riddle that apparently started this whole thread.

  9. Sarah-in Ezekiel it doesnt say “whale” it says “monster” and the kjv doesnt say anything about its soul being taken away or given or anything of the sort

  10. Daniel-You are correct. The New KJV uses “monster” but the old KJV used “whale”. Sarah points out that the soul was that of Jonah whom the fish swallowed and then spit out. Thank you all for participating. Being a computer programmer myself, I was challenged to keep an open mind. The Genesis passage contained the five letter word hidden within the six letter word “whales”.

  11. I am looking for an answer to a riddle. there is a lot more to the riddle than a five letter word mentioned 4 times in the king James versions. The word whale would fit migth fit.

  12. Idon’t know ya’ll whale to me doesn’t fit the riddle. Well the one (riddle) I have any way.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.