Create Cool Tag Clouds in Excel using VBA
By Chandoo at 22 April, 2008, 8:52 pm
I was toying with the idea of creating a tag cloud in excel - as a form of new visualization, this could be useful when you have medium amounts of data (eg: 50-300 rows) and you want to emphasize on what is important and what is not. I would imagine using a tag-cloud,
- When you are listing features of your software
- When you are listing your sales figures across top 1000 cities of your country
- When you are analyzing visitor data to your web start up
My goal is to generate a tag cloud from a selected data table (with just 2 columns, one with text to display, the other with any number on it) as shown below:
Download and play with a sample tag cloud visualization I have created
Since anything related to changing cell formats is not possible using functions, I had to write a VBA Macro (a subroutine that you would write in your excel sheet to achieve a task). The logic is simple:
1. Read the selected table and create 2 arrays, 1 with tags and another with the numeric data
2. Select an empty cell in the work book (I choose E10)
3. For each item in tags array:
- Add text to the selected cell
- set its font size based on normalized value between 6 and 20
The code is shown below:
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 20On 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 Integercntr = 1
i = 1For 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("e10").Select
ActiveCell.Value = taglist
ActiveCell.Font.size = 8strt = 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
This code is totally reusable. Just right click on the sheet name at bottom & select “view code”. In the VBA Editor create a new module (Menu > Insert > module) and Paste the above code there. Go back to your excel sheet and select a 2 columned data table and run the createCloud macro. The cloud will be created and pasted in cell E10. You can change this by modifying the line Range(”e10″).Select.
Download the above code and an example in an excel
Happy charting
Tagged with: charting | cool | Excel Tips | howto | ideas | macro | microsoft | MS | tag cloud | technology | tips | tricks | VBA | visualiztion
Did you enjoy reading this post? If so, give me some love
Consider subscribing to my newsletter every weekday I will send you one email with hot excel tips, technology tidbits or business insights that can make you more productive. Each mail will have unsubscribe link so you can stop receiving the mails at any time you wish.
Comments
RSS feed for comments on this post. TrackBack URI
Hey this really is cool . Smart play Chandoo ! I wish I could use this on my blog
@Kashif - welcome to PHD. Stay tuned for more cool stuff
This is absolutely brilliant, but I get an error:
Compile Error: Syntax Error.
The comment lines underneath Sub createCLOUD () are red, as is
taglist = taglist & cell.Value & “, ”
and after that error message, Sub createCLOUD () is highlighted.
Running Excel 2003, SP2
@Kevan… thanks for your comments. I think when you pasted the code you should change the quotes from ” to normal double quotes. Just remove any of the double quotes from the code and replace them by keying in double quotes from your keyboard. Alternatively download the attached excel and use the code in it instead. Let me know if it still fails.
Welcome to PHD
Cute code, looks like something you could use instead of just sorting or listing values. But I wonder if most people understand blog tag clouds.
Thx,
JP
Hey,
I couldn’t get it to work. I got the following error message:
ou may encounter this error for the following reasons:
Macro security is set to:
Very High and the application encounters a signed macro, but the macro was automatically disabled. Use the following procedure to enable the macro:
Select the Tools menu option and then select Macro and Security. In the resulting Security dialog, set the security level to High by clicking the High radio button.
Close the file and any other instances of the application currently running on the computer (close all applications that also use the application you are currently running).
Open the file again and examine the certificate of trust details and set the Always trust macros from this publisher box if you trust the certificate issued by the publisher.
Click the Enable button to allow the macro to run.
High and the application encounters a signed macro, but you selected Disable when prompted by the macro warning dialog when opening the file. Use the following procedure to enable the macro:
Close the file and any other instances of the application currently running on the computer (close all applications that also use the application you are currently running).
Open the file again and examine the certificate of trust details and set the Always trust macros from this publisher box if you trust the certificate issued by the publisher.
Click the Enable button to allow the macro to run.
High or Very High and the macro was not signed and was automatically disabled by the application. Use the following procedure to enable the macro, if you have verified you can trust the source of the unsigned macro:
Select the Tools menu option and then select Macro and Security. In the resulting Security dialog, set the security level to Medium by clicking the Medium radio button.
Close the file and any other instances of the application currently running on the computer.
Open the file again and click the Enable button when prompted to allow for the unsigned macro to run.
When you have accepted the macro, return the macro security level to its previous setting.
Another instance of your application has a lock on the security settings and is disallowing any changes to security settings.
It is recommended to close all Office related applications or other instances of the same application when you attempt to make changes to your security settings.
Visual Basic for Applications (VBA) is not installed on your computer.
If VBA is not installed, it is not possible to accept certificates of trust attached to VBA macros. Therefore, any VBA macro attempting to run, even with an attached certificate of trust, cannot be run because the necessary macro interpreter is not installed on the system. Two scenarios can cause this error:
Your administrator chose not to install VBA
The version of Office you are running does not install VBA by default
If you are running an instance of Office that does not provide VBA as an installable feature, you will need to upgrade your version of Office to run VBA macros.
The certificate of trust is invalid - (when this occurs, you cannot select the Enable button).
The certificate is no longer trusted, was revoked by the issuing authority, or is damaged. There are several other possibilities which you may need to explore as to why the certificate is no longer recognized as valid by your system. It is recommended that you not trust or run a macro that has an invalid certificate.
A troubleshooter help topic is available online regarding this issue.
More information about this error message online.
Never mind.
It worked.
@Abdul… welcome to PHD… and moreover, welcome to working with Macros in Excel, you have to adjust the security settings to “mild” or something to let this code run in your computer.. Looks like you have already figured that out. Let me know if you have any further difficulties
Neat! Took me a bit to figure out what was going on (cell E10 formatting to wrap text helped… as did a change of values in the cells…) but once I saw it, I was quite impressed!! Nice job!