キーワードオカレンスリストの生成（Microsoft Excel）

Sub KeywordList()

Dim dTally As Dictionary     Dim rSource As Range     Dim c As Range     Dim d As Variant     Dim aKeys() As String     Dim J As Integer     Dim sTemp As String

Set dTally = New Dictionary     Set rSource = Selection     For Each c In rSource         ' Put all keywords in an array         aKeys = Split(c, " ")

For J = LBound(aKeys) To UBound(aKeys)

sTemp = LCase(Trim(aKeys(J)))

If Len(sTemp) > 0 Then                 If dTally.Exists(sTemp) Then                     ' Increment existing keyword                     dTally(sTemp) = dTally(sTemp) + 1                 Else                     ' Add new keyword and count                     dTally.Add sTemp, 1                 End If             End If         Next J         Erase aKeys     Next c

' Now have all the keywords in a dictionary object     ' Add a new worksheet to store the list     Worksheets.Add     Cells(1, 1) = "Keyword"

Cells(1, 2) = "Count"



J = 1     For Each d In dTally.Keys         J = J + 1         Cells(J, 1) = d         Cells(J, 2) = dTally(d)

Next d End Sub