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 .Solid 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). _ ParagraphFormat .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 .Fill.Solid .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 .Solid End With myCirc.Line.Visible = msoFalse Next 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.
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.