Tag cloud in Excel with VBA

A word cloud is a very important visualization tool because it allows us to graphically show the importance of each word using the size of the font. A word of very high importance will have a larger font size than those of lesser importance.

This behavior is very common on Web sites where, through a word cloud, you can view the topics that have been written most frequently within the Web site.

Word cloud in Excel

This time we will test our programming knowledge to create a word cloud in Excel using the VBA language. This will be an excellent exercise for learning about macro concepts.

Data for Word Cloud in Excel

The objective of our exercise will be to generate a word cloud based on a data table that contains only two columns, the first column contains names of countries and the second column has the percentage of inhabitants of each country based on the total population of the world. .

How to make a word cloud in Excel

The logic of the macro that we are going to create is simple: the values ​​of both columns are read and 2 arrays are created. The first array will have the words from the first column and the second array will have the numeric values. We will select an empty cell and insert the texts of the words by modifying the font size for each one of them. The result of the macro on the sample data is as follows:

Macro to make a word cloud in Excel

Below you will find the macro code that will help us achieve this goal.

Macro for word cloud in Excel

It is important to mention that this macro runs on a previously selected range of cells. If this selection is not made, an error message will be sent, which is indicated on line 55 and informs the user of the need to select a data table. Another necessary condition for the macro to work correctly is that the data table is two-column as seen in the sample data.

The number of rows that the data table contains does not matter, it will only be necessary to select the cells that we want to include in the creation of the word cloud. Once we have made this clear, I present the code of the macro and below a more detailed analysis of the code.

Sub crearNubeEtiquetas()
On Error GoTo MensajeError

Dim tamano As Integer
Dim celda As Integer
Dim indice As Integer
Dim etiquetas() As String
Dim importancia()
Dim maxImportancia
Dim minImportancia
tamano = Selection.Count / 2
ReDim etiquetas(1 To tamano) As String
ReDim importancia(1 To tamano)

celda = 1
indice = 1

For Each cell In Excel.Selection
If celda Mod 2 = 1 Then
listaEtiquetas = listaEtiquetas & cell.Value & ", "
etiquetas(indice) = cell.Value
importancia(indice) = Val(cell.Value)

If importancia(indice) > maxImportancia Then
maxImportancia = importancia(indice)
End If

If importancia(indice) < minImportancia Then
minImportancia = importancia(indice)
End If

indice = indice + 1
End If

celda = celda + 1
Next cell

ActiveCell.Value = listaEtiquetas
ActiveCell.Font.Size = 8
inicio = 1

For i = 1 To tamano
With ActiveCell.Characters(Start:=inicio, Length:=Len(etiquetas(i))).Font
.Size = 6 + Math.Round((importancia(i) - minImportancia) / (maxImportancia - minImportancia) * 14, 0)
End With

inicio = inicio + Len(etiquetas(i)) + 2
Next i

Exit Sub

MsgBox "Necesita seleccionar una tabla de datos.", vbCritical + vbOKOnly
End Sub

We start with the declaration of all the variables using the Dim instruction from line 4. The variable will store the result of dividing the number of selected cells by two and the statement helps us to establish the elements that the arrangement will have and the arrangement based on the value just obtained in the variable .

Later we assign the value 1 to the variables and . The variable will help us to know if we are in a cell in the left column or in the right column and the variable It will help us to know the row in which we are and to be able to assign the corresponding values ​​to the arrangements.

On line 18 we use a loop to cycle through each of the cells in the previously selected range. The instruction that has the condition Mod 2 = 1 is useful to know if we are in a cell of the left column in which case we store the value of the word in two places: in the variable and within the arrangement .

On the contrary, if we are in a cell of the second column, we will store the value in the array . If we take into account the data in our example, the first element of the array will have the floor and the first element of the array will have the value 19.50, the second element of the array will have the floor and the second element of the array it will have the value 17.30 and so on.

Before finishing the block , we compare the numerical value of the second column with the values ​​that have been previously stored to know if it is the maximum value or the minimum value, which will be very useful later to properly determine the font size for each word.

In line 39 we select cell D2 of our sheet since in this cell we will create the tag cloud. If you wish, you can choose a different cell by modifying this line of code. In this cell we will place the value of the variable , which is a list of all the words separated by a comma. We also set the size 8 for the font in cell D2 so that at that moment all the words will be the same size.

Finally on line 44 you will find the loop responsible for changing the font size of each word contained in cell D2. This loop will run once for each word and will determine its font size based on its value in the second column and the maximum value recorded in the table.

You can see that the modified property is but the font size change is not done over the entire cell but is applied only to the characters defined by the method which is responsible for selecting a single word at a time for each cycle of the loop . This is how at the end of this loop the word cloud will have been created within cell D2.

I invite you to download working file that I used in creating this article for you to test the macro for yourself. The best way to increase your programming skills is to practice over and over again, so I recommend downloading the file and practicing creating word clouds in Excel with VBA.

Leave a Comment