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.
Bookmarks