Results 1 to 8 of 8

Create Tag Cloud In VBA Possible?

Threaded View

  1. #1
    Registered User
    Join Date
    04-21-2009
    Location
    Puyallup, WA
    MS-Off Ver
    Excel 2010
    Posts
    55

    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.
    Attached Files Attached Files
    Last edited by sweetrevelation; 05-09-2009 at 06:09 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1