How to create a tag cloud

Although most of the discussion about How to use cJobject is focused on being able to make and read jSon in Excel, cJobject can also be used as an 'on demand' object with properties, heirarchy and structure. Here is an example of how to implement a tag cloud class and how cJobject helps this.

What to download

What is a tag cloud

This is a way of visualizing the most common terms in a set of data, simply by changing the font size depending on how often a particular term appears.

Here is an example of a tag cloud based on a sample of 100 tweets on the topic of "Mitt Romney" made by the Rest to Excel library, and created by the cTagCloud class.

cTagcloud also will do a colorful version as below taken at a later time, using the heatmap scale referenced in Create a heatmap in Excel.

It's pretty easy to see from this layout what the main topic is, especially if you remove the noise - which the cTagCloud class will do for you too. Here is the code for creating the above using the cTagCloud class, Data Manipulation Classes and How to use cJobject. Assuming you have the cDataSet.xlsm workbook downloaded, that's all there is to it.

Public Sub testTag()

Dim ds As cDataSet, dr As cDataRow, tg As cTagCloud

Dim r As Range, jo As cJobject

Set ds = New cDataSet

Set tg = New cTagCloud

tg.init , , 3, " "

With ds

.populateData wholeSheet("tweetsentimentdetails"), , , , , , True

For Each dr In .Rows

tg.collect dr.Cell("text").toString

Next dr

End With

tg.results Sheets("tagout").Range("a1")

End Sub

Walkthrough

Initialize the tagCloud - minimum number of mentions is 3 to be included at all, and terms are separated by a space.

tg.init , , 3, " "

Here are the defaults for the .init method

Public Function init(Optional sName As String = "tagcloud", _

Optional bDump As Boolean = True, _

Optional lMinCountToShow As Long = 1, _

Optional sep As String = ",", _

Optional sNoiseString As String = vbNullString, _

Optional bColorful As Boolean = True, _

Optional dSmallest As Double = 8, _

Optional dBiggest As Double = 40) As cTagCloud

Get the input dataset from the tweetsentiment details sheet, and collect all the data in the "text" column for tag cloud processing.

With ds

.populateData wholeSheet("tweetsentimentdetails"), , , , , , True

For Each dr In .Rows

tg.collect dr.Cell("text").toString

Next dr

End With

Output the results to cell A1 on the tagout worksheet and adjust the relative font sizes, taking the default largest and smallest font to allow.

tg.results Sheets("tagout").Range("a1")

Here is the call for for the .results method

Public Function results(rOut As Range) As cTagCloud

Using cJobject as an 'on demand' class

Inside the tagcloud class, cJobject is used to store properties about each tag, with these properties.

?jo.Serialize (True)

{

"mitt":{

"count":"80",

"scale":"1",

"size":"40",

"show":"True",

"color":"255"

}

}

Aside from not needing to bother creating another class, or use arrays, or multiple collections or custom types, being able to directly serialize the data for each item makes debugging really simple!