Create Cool Tag Clouds in Excel using VBA

Posted on April 22nd, 2008 in Charts and Graphs , hacks , ideas , Learn Excel , technology - 33 comments

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:
tag clouds in excel how to?


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 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("e10").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

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 :)

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

33 Responses to “Create Cool Tag Clouds in Excel using VBA”

  1. kashif says:

    Hey this really is cool . Smart play Chandoo ! I wish I could use this on my blog :D

  2. Chandoo says:

    @Kashif – welcome to PHD. Stay tuned for more cool stuff ;)

    • Vara says:

      Hi Chandoo.
      Its realy helping me in my work..
      Thanks!
      I have added color also in the same code and its working fine.
      Now I have challenge in gettin getting the ouput like:
      Color has to be fixed based on the percentages.
      For example:
      A 30%
      B 60%
      C 20%
      I require in word cloud with
      A as font 20 with Red
      B as font 15 with Blue
      C as font 10 with Green
      Please can you help me on this.
      vara.stat@gmail.com

  3. Kevan says:

    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

  4. Chandoo says:

    @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 :)

  5. JP says:

    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

  6. Abdul says:

    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.

  7. Abdul says:

    Never mind.

    It worked.

  8. Chandoo says:

    @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 :)

  9. Steve says:

    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!

  10. Adey says:

    It worked but only on percentages not on values. Also if the percentages are all low it doesn’t do the size relatively across the set; you just get one size text.

    apart from that very good work.

  11. Chandoo says:

    @Steve: Thanks :)
    @Adey: thanks. You are right, it doesnt scale well when the range is small.

    I guess the culprit could be
    Dim minImp As Integer
    Dim maxImp As Integer

    can you try redefining them as numbers or floats? Let me know if it still doesnt work.

    • Vara Prasad says:

      Hi Chandoo.
      Its realy helping me in my work..
      Thanks!
      I have added color also in the same code and its working fine.
      Now I have challenge in gettin getting the ouput like:
      Color has to be fixed based on the percentages.
      For example:
      A 30%
      B 60%
      C 20%
      I require in word cloud with
      A as font 20 with Red
      B as font 15 with Blue
      C as font 10 with Green
      Please can you help me on this.
      vara.stat@gmail.com

  12. [...] is a tag cloud (excel tag cloud tutorial & templates). The fonts are sized based on their relative market share [...]

  13. retrolaser says:

    Great job! Thanks!

  14. Sri says:

    It works perfectly (tried it with values too)! This is awesome thanks! I am new visitor to your blog and it totally rocks.

  15. Social comments and analytics for this post…

    This post was mentioned on Twitter by Srividya: Psyched about a piece of VBA code to create word/tag clouds in Excel. Thanks to @r1c1 http://bit.ly/5U4QA

  16. Tasha says:

    Hey,

    I like the idea of the code, but I’m having a problem with your variables. The following variables are not defined, an easy work around that I’ve now defined :)
    cntr = 1
    i = 1

    But for the following code:
    For Each cell In Excel.Selection
    It says ‘cell’ is a variable not defined (i have selected the range first). I’ve looked in the object explorer and can’t find ‘cell’, should I dim ‘cell’ as a range?

    Thanks

    Tasha

  17. Tasha says:

    P.S. I love your open-source attitude – may it long prevail! :) Thanks

  18. Chandoo says:

    @Tasha… Welcome to PHD. thanks for asking a question.

    you could just add dim cell to your code.

    I have not defined variables as it is not mandatory to define them in VBA. But may be your workbook settings mandate that variables be defined.

    PS: Thank you. We love to keep things simple and open at Chandoo.org

  19. Tasha says:

    Hi,

    I got round the problem and thought I’d share. I had Option Explicit turned on and it would not accept the variable ‘cell’. I turned it off and hey presto it worked :)

    I also added the following – to allow for multiple use:
    Dim inputcell As Variant
    ‘ paste values in cell …
    inputcell = InputBox(“Which Cell do you want to paste the data into?”)
    Range(inputcell).Select
    ActiveCell.Value = taglist
    ActiveCell.Font.size = 8

    I am getting the programme to work, but alas it’s not changing the font size for the highest frequency word :( Maybe someone has some feedback on that to share?
    e.g. One result gives me this:
    Facebook,facebook and myspace,Have seen, don’t use,,,,,,,Myspace,,,
    or this:
    ,,Have seen, don’t use,,,,,,Delicious,Delicious,,,

    Cheers

  20. [...] for or talking about — anywhere that they are entering free-form text. I found an article on chandoo.org about how you could create a tag cloud in Microsoft Excel, and adapted it for use with Next [...]

  21. Casey says:

    Is there anything you can’t do with Excel? I’m saving this one… I will make a use for it!

  22. This is very cool and *almost* what I need. What I *actually* need is what’s displayed here: http://www.youtube.com/watch?v=BYkVoyakkms – but http://www.ExcelVisualBasics.com seems to have disappeared. Any ideas whether there’s another version of that around anywhere?

  23. DaveG says:

    How would I reverse the two columns? – that is, I want to have the count in the first column and the tags in the second column.
    FYI: there are a number of widely used SEO analysis sheets that use that arrangement, and this would be an awesome addition to them with that column reversal.
    Thanks in advance for all you do!

  24. Werner says:

    Thanks a lot!

  25. John Knox says:

    I love this.  I do have a quick question.  When using this document, I need to be able to input numbers in the hundreds of thousands.  When putting numbers, in column C, that are high, it doesn’t seem to be working. How would I modify the code to build a cloud using high number values?
    I appreciate your help.
     

  26. okl says:

    Hi,

    How can i change the selection to a pref-defined 2 column data rather than by user selection.

    So i can automate it.

    Thanks

       

  27. Mike says:

    Out of curiosity why is minImp always 0? I’ve ran it with tons of different values percentages, integers, etc. and every time minImp comes back as “0″. Was curious as to whether this was by design? 

  28. prasad says:

    I need the code  vba in excel to display the tag values for the selected date.
    ex:Private Sub CommandButton1_Click()
     
    Sheet1.Range(“b1″) = “sinusoid”
    Sheet1.Range(“b2″) = “10/1/2012″
    Sheet1.Range(“b3″) = “10/31/2012″
    Sheet1.Range(“b4″) = “10h”
     Sheet1.Range(“E8:F8″) = “=PISampDat(“”sinusoid”",”"9/1/2012″”,”"9/30/2012″”,”"45m”",1,”"PI”")”
      Sheet1.Range(“E8:F8″).Select
    Call dlresize
    End Sub

    ……………..
    this code is not working can u help regarding this code

    thanks ! 

  29. Leo Gante says:

    Hi Chandoo,

    I am using Excel 2010 and I cannot seem to make it work.

    Is there something I have to tweak to make it work?

    Thanks,
    Leo       

    • Hui says:

      @Leo
      Without any guidance it could be anything!
      Can you specify what isn’t working?
       
      I’d suggest going through the code and retype all the ” marks as often WordPress screws them up.
       

  30. David says:

    Thanks for this, i urged me to make my own. Although there are a few Excel based solutions, for me they were too boring. I’m almost there but my solution is to use WordArt.
    Unlike some other languages, you cannot determine if objects/shapes (wordart) are overlapping with each other, so to get around this i have made the columns on a worksheet 0.08 and the rows 0.75 and used the TopLeftCell and BottomRightCell properties to see if they overlap, and then move them if they do by randomly generating co-ordinates.
    It works quite nice and you can also randomly select wordart styles/colours for each word.Another existing function that i couldn’t find was to include phrases in the word-count

Leave a Reply