Create Tag Cloud In VBA Possible?

**sweetrevelation** · 04-22-2009, 06:58 PM

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.

**Andy Pope** · 04-23-2009, 08:05 AM

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.

**sweetrevelation** · 04-23-2009, 09:35 PM

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.

**williajc** · 11-21-2010, 06:29 PM

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

**shg** · 11-21-2010, 06:54 PM

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

Thanks.

**simoran1** · 02-09-2011, 05:22 AM

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!!

**VTHokie11** · 11-10-2011, 04:22 PM

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

**brucemcpherson** · 03-06-2012, 04:35 AM

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