Heatmap Circles part 1

This technique is more like a raw data visualisation technique. But it allows you to extend excel to create a heatmap, where you could represent size and colour on different dimensions.

At it’s simplest, it lets you make pretty circles on a page. And this is what I’m going to start with today

First, you start with a table layout, with the key values in the cells.


Running the following macro over these, gives the final result.


Sub makeCircles()

Dim leftPos As Long
 Dim topPos As String
 Dim cell As Range
 Dim circleSize As Long
 Dim circColor As Long
 Dim myCirc As Shape
 Dim leftMargin As Long
 Dim myRange As Range

Set myRange = selection

For Each cell In myRange
 circleSize = cell.Height * 0.75
 leftPos = cell.left + (cell.Width / 2) - (circleSize / 2)
 topPos = cell.Top + (cell.Height / 2) - (circleSize / 2)
 leftMargin = 5
 Set myCirc = ActiveSheet.Shapes.AddShape(msoShapeOval, leftPos, topPos, circleSize, circleSize)
 myCirc.TextFrame2.TextRange.Characters.Text = cell.Value
 With myCirc.TextFrame2.TextRange.Characters(1, 1).Font.Fill
 .Visible = msoTrue
 .ForeColor.ObjectThemeColor = msoThemeColorBackground1
 .ForeColor.TintAndShade = 0
 .ForeColor.Brightness = 0
 .Transparency = 0
 End With
 myCirc.TextFrame2.MarginLeft = leftMargin
 myCirc.TextFrame2.MarginTop = 0
 myCirc.TextFrame2.MarginBottom = 0
 myCirc.TextFrame2.MarginRight = 0
 With myCirc.TextFrame2.TextRange.Characters(1, 1). _
 .FirstLineIndent = 0
 .Alignment = msoAlignLeft
 End With
 With myCirc.TextFrame2.TextRange.Characters(1, 1).Font
 .Bold = msoTrue
 .NameComplexScript = "+mn-cs"
 .NameFarEast = "+mn-ea"
 .Fill.Visible = msoTrue
 .Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground1
 .Fill.ForeColor.TintAndShade = 0
 .Fill.ForeColor.Brightness = 0
 .Fill.Transparency = 0
 .Size = 32
 .Name = "Century Gothic"
 End With
 Select Case cell.Value
 Case "R"
 circColor = RGB(215, 21, 58)
 Case "G"
 circColor = RGB(166, 206, 57)
 Case "A"
 circColor = RGB(251, 176, 76)
 End Select

With myCirc.Fill
 .Visible = msoTrue
 .ForeColor.RGB = circColor
 .Transparency = 0
 End With
 myCirc.Line.Visible = msoFalse

End Sub

So, what’s going on in this code. Well, a few things. Firstly, it’s important to setup the range correctly, in this case I use a manual selection, as it was necessary to do this in a number of different templates.

set myRange=selection

such a simple line, but so powerful. Takes whatever is selected (on the active sheet) and puts it in a variable, the selection object, returns a range, which inside is secretly much like an array. We can then loop over the array using the ‘For Each … in …’ command.

Inside the foreach loop, all we need to do, is get the value of the cell, make a circle, position it relative to the cell, and format it.

for each cell in myRange

For Each works by taking an object from within an array, say myRange, and looping over that array, and assigning the current object to a temporary variable, in this case cell. This is probably a bad name for an object, as it’s used in excel to represent cells. Because you don’t necessarily know what the variable is going to be it should be created as variant.

Make a circle:

Need to know the position of the circle, and the size. Everything in excel is done from Top and Left. So, you can find the top and left property of the existing cell using cell.Top, and cell.Left (somewhat unsurprisingly, right!). Then you need to put the circle in the right place.

In this case, you need to know the size of the circle, the size of the cell, and the logic here, is a bit ‘circular’

circleSize = cell.Height * 0.75
leftPos = cell.left + (cell.Width / 2) - (circleSize / 2)
topPos = cell.Top + (cell.Height / 2) - (circleSize / 2)

Because everything is top and left, and you want the circle in the middle of the cell, you need to work out where the top left corner will be. The following diagram may help my logic here.


Also note, that my logic isn’t perfect, as while the same can be done for the topPosition, it probably shouldn’t have been, as the top position could have been done more simply.

After assigning the shape to a variable, we then proceed to format it

Set myCirc = ActiveSheet.Shapes.AddShape(msoShapeOval, leftPos, topPos, circleSize, circleSize)

There’s nothing special about the formatting, I’ve put in rules for Red Amber Green Colouring.

In part 2, I hope to take this example further and add numbers, size and colour scales.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: