title: VBA: Word Frequency Array
body: <p>I'm attempting to compute the frequency of terms within a collection of variable length strings.  The context is descriptions in an Access database.  Would prefer to keep the solution in VBA.  Delimiter is " " (space) character.  Special chars will be removed in advance.</p>
  
  <p>My first pass feels a bit brute force; I suspect a much more efficient approach is possible.  </p>
  
  <p>Here is what I am doing currently for a SINGLE string, which is providing the result I expect:</p>
  
  <pre><code>Private Sub Command0_Click()
  
      Dim myCol As Collection
      Dim myArray() As String
      Dim strArray As Variant
      Dim strDescr, strTerm, strMsg As String
      Dim i, j As Integer
  
      Set myCol = New Collection
  
      strDescr = "This is just a test only a test test test"
  
      myArray = Split(strDescr, " ")
  
      For Each strArray In myArray
          On Error Resume Next
          myCol.Add strArray, CStr(strArray)
      Next strArray
  
      For i = 1 To myCol.Count
          strTerm = myCol(i)
          j = 0
          For Each strArray In myArray
              If strArray = strTerm Then j = j + 1
          Next strArray
          'placeholder        
          strMsg = strMsg &amp; strTerm &amp; " | " &amp; j &amp; Chr(10) &amp; Chr(13)
      Next i
  
      'placeholder
      'save results into a table
      MsgBox strMsg
  
  End Sub 
  </code></pre>
  
  <p>Sample Result:</p>
  
  <p><a href="https://i.stack.imgur.com/G8Vk1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/G8Vk1.png" alt="sample result"></a></p>
  
  <p>Next, I need to expand this approach to step thru a recordset, to keep appending words to the array and collection.  Recordsets can be large, 30K records, descr are 30-60 chars.</p>
  
  <p>Questions:  </p>
  
  <ol>
  <li>How do I append to an array for each row in a recordset?</li>
  <li>Will this approach fail as I scale the number of records?</li>
  <li>A better approach?</li>
  </ol>
  
  <p>Thanks!</p>