+ Reply to Thread
Results 1 to 8 of 8

Create Tag Cloud In VBA Possible?

  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:

    Please Login or Register  to view this content.
    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.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Create Tag Cloud In VBA Possible?

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

    Please Login or Register  to view this content.
    You can then select the 2 columns and run chandoo's code.
    Cheers
    Andy
    www.andypope.info

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

    Re: Create Tag Cloud In VBA Possible?

    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.
    Last edited by sweetrevelation; 04-23-2009 at 10:08 PM.

  4. #4
    Registered User
    Join Date
    12-01-2008
    Location
    Ohio
    Posts
    16

    Re: Create Tag Cloud In VBA Possible?

    Hi Everyone,

    I know this is an older post but it is exactly what I am looking to do with some research I have completed. I have an open ended response located in column B which I would like to do the word count on and then create a word cloud.

    My problem stems from my long absence from coding and I can't seem to get everything to work.so my question is; how do I get the second piece of code to run on the set of cells (column b) and then have the chandoo code run?

    I have attached a snippet of the responses from the survey.

    any help would be greatly appreciated

    Thank you
    Attached Files Attached Files

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Create Tag Cloud In VBA Possible?

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

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    02-09-2011
    Location
    england
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Create Tag Cloud In VBA Possible?

    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!!
    Last edited by simoran1; 02-09-2011 at 05:26 AM.

  7. #7
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Create Tag Cloud In VBA Possible?

    Does anyone know a way to control where the table of unique words and their frequencies appear? I have a worksheet with multiple 10-15+ columns of qualitative info I'd love to be able to summarize any column using these codes (CreateUniqueWords as well as Chandoo's tag cloud generation code).

    I believe it would be best if it would be possible to output the table of unique words to another worksheet then create the tagcloud based off of the table and place it on this sheet. Basically I just want to keep my analysis separate from the worksheet with the original data.

    I'll be honest I had a little trouble following the following portion of the CreateUniqueWords macro. Thanks a bunch in advance!

    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

  8. #8
    Registered User
    Join Date
    08-09-2011
    Location
    london,england
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Create Tag Cloud In VBA Possible?

    Heres a spin on tag clouds in excel, where you can set up a list of noise words, and set a threshold of how many occurrences are needed before it is included.
    http://ramblings.mcpher.com/Home/exc.../json/tagcloud

    I use similar code to quickly sum up groups of emails in outlook
    http://ramblings.mcpher.com/Home/exc...son/tagoutlook

    Finally, it uses a heatmap color ramp To colorise the results
    http://ramblings.mcpher.com/Home/exc...map/rampcharts


    Bruce

+ Reply to Thread

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