Create Tag Cloud In VBA Possible?

I found this really cool code that creates a tag cloud in Excel using VBA. It's far too complex for my understanding but I was hoping I could get some of the experts opinions on here about modification possibilities.

I have attached the macro-enabled 2007 workbook. What I am trying to do is search through a list of keywords and determine each keyword's density within a list. The list for example could look like this:

slow windows xp
windows xp running slow
windows xp computer
[slow windows xp]
[windows xp running slow]
[windows xp computer]
[computer running slow windows xp]
"computer running slow windows xp"
"slow windows xp"
"windows xp running slow"
"windows xp computer"

You'll notice the characters " [ ] of which I would like to ignore when the cloud builds. The cloud would list the most dense keywords first, and gradually decrease to the least dense keywords.

The macro code is inside the workbook but I'll list it here to:

Sub createCloud()
' this subroutine creates a tag cloud based on the list format tagname, tag importance
' the tag importance can have any value, it will be normalized to a value between 8 and 20

On Error GoTo tackle_this

Dim size As Integer

size = Selection.Count / 2

Dim tags() As String
Dim importance()

ReDim tags(1 To size) As String
ReDim importance(1 To size)

Dim minImp As Integer
Dim maxImp As Integer

cntr = 1
i = 1

For Each cell In Excel.Selection

    If cntr Mod 2 = 1 Then
        taglist = taglist & cell.Value & ", "
        tags(i) = cell.Value
    Else
        importance(i) = Val(cell.Value)
        If importance(i) > maxImp Then
            maxImp = importance(i)
        End If
        If importance(i) < minImp Then
            minImp = importance(i)
        End If
        i = i + 1
    End If
    cntr = cntr + 1
Next cell

' paste values in cell e10
Range("e26").Select
ActiveCell.Value = taglist
ActiveCell.Font.size = 8

strt = 1

For i = 1 To size
    
    With ActiveCell.Characters(Start:=strt, Length:=Len(tags(i))).Font
        .size = 6 + Math.Round((importance(i) - minImp) / (maxImp - minImp) * 14, 0)
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    strt = strt + Len(tags(i)) + 2
Next i



Exit Sub
tackle_this:
' errors handled here
'MsgBox "You need to select a table so that I can create a tag cloud", vbCritical + vbOKOnly, "Wow, looks like there is an error!"
End Sub

Basically, I'm interested in knowing which keywords in my list are the most frequent and put them in order from most frequent to least frequent.

Thanks for any help.

This will create a list of unique words and there counts.

Sub CreateUniqueWords()
    Dim rngData As Range
    Dim rngCell As Range
    Dim colWords As Collection
    Dim vntWord As Variant
    
    On Error Resume Next
    
    Set colWords = New Collection
    Set rngData = Range("B30:B86")
    For Each rngCell In rngData.Cells
        For Each vntWord In Split(Replace(Replace(Replace(rngCell.Value, """", ""), "]", ""), "[", ""), " ")
            colWords.Add colWords.Count + 1, vntWord
            With Cells(29 + colWords(vntWord), 3)
                .Value = vntWord
                .Offset(0, 1) = .Offset(0, 1) + 1
            End With
        Next
    Next
    
    With Range("C30", Cells(Rows.Count, 3).End(xlUp)).Resize(, 2)
        .Sort .Cells(1, 2), xlDescending
    End With
    
End Sub

You can then select the 2 columns and run chandoo's code.

Incredible Andy Pope, pure genius! That's exactly what I was looking for. Thank you for taking to time to help with this. I really appreciate. There was absolutely no code on creating keyword cloud tags for Excel - until now.

I think a lot of people will find this highly useful. I can't thank you enough.

*Reputation given and gave you an incoming link from my site to help your SEO efforts.

Please start your own thread. You can include a link to this one if you like.

Thanks.

A bit of a blast from the past but Andy - is it possible to amend the code to only pull out words that are greater than three characters?

Edit: - also is it possibel to randomise the order of the results (currently its ranked by volume - this produces a very linear word cloud using the chanddo code..)

Thanks for your help!!