Generating a Keyword Occurrence List

Assuming the text with keywords is in cells B2:B100, here's another way using Excel 365 formulas. (Earlier versions of Excel might not include all of these functions.)
Put this formula in cell C2:
=SORT(TEXTSPLIT(TEXTJOIN(" ",TRUE,B2:B100),," ",TRUE))
Put this formula in cell D2:
=UNIQUE($C$2#)
Notice $C$2# represents the spilled range (array) starting at cell $C$2.
Put this formula in cell E2:
=COUNTIF($C$2#,"="&D2)
Notice $C$2 is absolute and D2 is relative.
Click the bottom-right corner of cell E2 and drag down to the last non-blank row of column D to duplicate the formula in cell E2, so each Keyword in column D has a Count in column E.
(see Figure 1 below)

It is easy to convert the KeywordList macro described in my previous comment into a ListKeywords dynamic array function for use with Excel 365 or 2021+.

Function ListKeywords(Target As Range)
Dim Tally As Collection, T As Variant, C As Range
Dim sKeys() As String, sTemp As String, n As Integer
Dim bExists As Boolean
Dim A() As Variant
Set Tally = New Collection
For Each C In Target
...
Next C
ReDim A(1 To Tally.Count, 1 To 2)
n = 0
For Each T In Tally
n = n + 1
A(n, 1) = T(0)
A(n, 2) = T(1)
Next T
ListKeywords = A
End Function

Notes:
1. Code represented by the ellipsis (...) is unchanged from the KeywordList macro.
2. The function's result does not include a header and is not sorted.
3. Here are some example cell formulas:
=ListKeywords(B2:B100)
=ListKeywords(Sheet2!B2:B100)
=ListKeywords((Sheet2!B2:B100,Sheet2!D2:D100))
=SORT(ListKeywords((Sheet2!B2:B100,Sheet2!D2:D100)))

excellent uses of dictionary and collection.

This version of the Tip's macro uses Collection instead of Dictionary, therefore, it does not require a special library and should work on a Mac.

Sub KeywordList()
Dim Tally As Collection, T As Variant, c As Range
Dim sKeys() As String, sTemp As String, n As Integer
Dim bExists As Boolean
Set Tally = New Collection
For Each c In Selection
    sTemp = LCase(WorksheetFunction.Trim(c))
    sKeys = Split(sTemp, " ")
    For n = LBound(sKeys) To UBound(sKeys)
        sTemp = sKeys(n)
        On Error Resume Next
            T = Tally(sTemp)
            bExists = (Err = 0)
        On Error GoTo 0
        If bExists Then
            T(1) = T(1) + 1
            Tally.Remove sTemp
            Tally.Add T, sTemp
        Else
            Tally.Add VBA.Array(sTemp, 1), sTemp
        End If
    Next n
Next c
Sheets.Add After:=ActiveSheet
Cells(1, 1) = "Keyword"
Cells(1, 2) = "Count"
n = 1
For Each T In Tally
    n = n + 1
    Cells(n, 1) = T(0)
    Cells(n, 2) = T(1)
Next T
[A:B].Sort [A1], Header:=xlYes
End Sub

Notes:
1. WorksheetFunction.Trim(Text) and VBA.Trim(Text) produce different results when Text has multiple spaces between words.
2. A Collection can have unique text Keys like a Dictionary, but it does not have a method to determine if a Key exists in the Collection.
3. Dictionary items can be manipulated like array elements, but Collection items cannot.
4. VBA.Array is always base 0 (unaffected by Option Base).
5. The macro's result is sorted alphabetically by keyword.

For Mac users, by definition the "Dictionary" concept is NOT recognised in VBA. There are some work-arounds for this available online using Class definitions which mimic the behaviour of a Dictionary, but they are not for the faint-hearted.

Check out https://www.ozgrid.com/forum/index.php?thread/102697-office-2011-for-mac-dictionary-replacement/&postID=1226536#post1226536