Create a thermal map of Europe

Created this file based on work from Andy Pope (Showing US map and using data ranges) and Peltier Tech (also showing US map) to show a European map in colour based on the values entered.  There’s a little bit of VBA, not much and of course you need to have the shapes in excel, but I though it was interesting, so I’ve posted it here to view.  The idea is to be able to see by colour how good or bad each country is performing.  Nice visual statistics.  I’ve seen a few websites that will do this for you, but I liked the idea of doing it in Excel and since it was relatively painless, here it is.  (You can of course also use this technique for other things, not just maps!)

In the attached file, you have two sheets, once with the map (MainMap) and another for the data (Control)

Each individual country on the MainMap tab is a freeform object.  You can either draw these in excel yourself (tricky), or you can go to OpenClipArt.org and download the appropriate svg file, for example world map, french map etc.  Then use an svg – wmf converter (or emf converter).  Once the file has been converted, import the wmf or emf drawing into excel.  If you use inkscpae you can open the svg directly and save as emf, nice and easy!  Ungroup the wmf / emf picture on the excel sheet and excel will convert each object into a freeform shape.  Give each freeform a name (the same as in the list of countries on the Control sheet.  You can do this using the selection and visibility pane or by typing it in the name box next to the function bar.  Obviously if you want to add more countries, you need to add and name more countries on the map as well as on the control tab.

Then go to the “Control” sheet.

A2:B9 is a named range called “STATES”, note the use of capitals!  If you’ve added extra countries, use the name manager on the formulas tab of the ribbon to edit the range.

E2:E9 is also a named range “STATE_COLOURS”, but the macro assigned to the button actually looks at the formatting of the colours in the adjacent cells to this range i.e. F2:F9.

How it works is that when you change the values in B2:B9, and press the macro button, the colours on the MainMap page will change accordingly.

The vba to do this is below.  I’m sure there are other ways of doing this, but vba is not my strong suit.  Please note the diagonal colouring with 2 colours only works in Excel 2003, not 2010.   Also this works only for exact values and not ranges (which is what I wanted), but the vba can easily be altered to look at ranges of values.  Any comments or suggestions, please post below!  Enjoy.

Sub ColourStates()

‘ Using the values from named range STATE

‘ And the colours from named range STATE_COLOURS

‘ re colour the map on sheet MainMap

‘

Dim intState As Integer

Dim strStateName As String

Dim intStateValue As Integer

Dim intColourLookup As Integer

Dim rngStates As Range

Dim rngColours As Range

Set rngStates = Range(ThisWorkbook.Names(“STATES”).RefersTo)

Set rngColours = Range(ThisWorkbook.Names(“STATE_COLOURS”).RefersTo)

With Worksheets(“MainMap”)

For intState = 1 To rngStates.Rows.Count

strStateName = rngStates.Cells(intState, 1).Text

intStateValue = rngStates.Cells(intState, 2).Value

If intStateValue > 9 Then

‘ stripped

With .Shapes(strStateName)

intColourLookup = Application.WorksheetFunction.Match(CInt(Left(CStr(intStateValue), 1)), Range(“STATE_COLOURS”), True)

.Fill.Patterned msoPatternWideUpwardDiagonal

.Fill.ForeColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color

intColourLookup = Application.WorksheetFunction.Match(CInt(Right(CStr(intStateValue), 1)), Range(“STATE_COLOURS”), True)

.Fill.BackColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color

End With

Else

‘ single colour

intColourLookup = Application.WorksheetFunction.Match(intStateValue, Range(“STATE_COLOURS”), True)

With .Shapes(strStateName)

.Fill.Solid

.Fill.ForeColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color

End With

End If

Next

End With

End Sub

Once again, you can find the Excel file here.  Enjoy

Alesandra Blakeston

Advertisements