Creating custom Map Charts using shapes and VBA

Custom Map Charts Thumb

Custom Map Charts

Whenever I see promotional images for expensive BI Dashboard software, they always show the same thing – a map with countries or regions highlighted in different colors.  It sends the subtle message that maps are the pinnacle of Business Intelligence (whether they are or not is a different matter altogether).  Until Excel 2016, these Map Charts were not available natively in Excel.  As a result, the BI Dashbaord software stood out as being something which was more advanced, it had the “WOW” factor.

As you will see in this post, with a few images downloaded from the internet and some lines of VBA we can create our own custom Maps Charts in Excel.  Our Excel dashboards can now have that “WOW” factor too.  This solution should work in any version of Excel which can run Macros.

Even if you have Excel 2016 with Maps Charts built in, you may still want to follow this article.  The built-in Map Charts are amazing, but at the same time have various limitations, so this approach may better meet your requirements.

In this post, you will find the World Map Template, which is available to download for free, but also instructions on how to create your own Map Charts.  My goal for this post is to give you the tools, skills and knowledge to construct your own custom Map Charts.

 

Introducing the World Map Template

I want to share with you the World Map Template file.  This template includes a world map, in which the countries change color when the values associated with those countries change.  The functionality within this template can be applied to any map, or any shapes.  Whether your map is of states, school districts or by geographical factors, this template can be adapted to any scenario.

 

Download the World Map Template

The World Map Template is available for newsletter subscribers to download for free.

Please note, you do not need to download the file to create your own Map Chart, as all the instructions and VBA code are in the sections below.  But it will be much easier to follow along if you have the World Map Template downloaded.

Existing newsletter subscribers can enter their name and e-mail address into the form below for instant access to the download.  Or, you can enter your name and e-mail address below to become a subscriber.







 

Using the World Map Template

Map Chart - World Map

I must say Thank You to www.freeworldmaps.com for allowing me to use and distribute their World Map images within the World MapTemplate.  This is also a great site to get your hands on other maps.

Try out the template for yourself.  The countries in the map are formatted according to the names and values in Columns Q and R.  The formatting applied is based on the fill color and left border color in Cells T2 – T8.

Map Chart - Formatting and Countries

Changing the values in Column Q, or changing the colored banding in Cells T2 – T8 will change the formatting of the countries in the map.  The ColorToShape function (which I created using VBA) in Column S is the trigger which drives the functionality.

For the functionality to work, the values in column Q must be identical to the names of the shapes within the map.  If the ColorToShape function is applied to a name which is not also the name of a shape, there will be no effect.  But, if the shape name exists, then its color and border will change.

Clicking on one of the countries will reveal the name of the shape.

Map Chart - Image Selection and name

As some countries, such as Indonesia are constructed of multiple shapes, each shape is named “Indonesia”.  Each shape does not need to have a unique name.  Naming the shapes in this way ensures the formatting is applied to all shapes associated with that country.

 

Creating your own Map Chart

This section covers the steps to create your own Map Chart, which will work in exactly the same way as the World Map Template.  The specific VBA code for each step is provided.

 

Overview

The basic steps are:

  • Get a map with each country/region as a separate shape.
  • List the names of all the shapes.
  • Rename all the shapes to usable names.
  • Get a final list all the shapes (with usable name) and the value related to each country/region.
  • Define the colored banding for formatting each country/region.
  • Use the ColorToShape User Defined Function to format the shapes.

 

Get a map in the right format

The easiest way to obtain new maps is to search the internet for maps embedded into PowerPoint files.  You need to find maps where each country/region is a different shape.  Simply copy the map (i.e all of the shapes) from PowerPoint into Excel.  A good place to start is www.freeworldmaps.net, this is where I obtained the images for this post.

You could draw the shapes yourself straight into Excel, it would be quite time consuming, but an option all the same.

 

List all the shape names

To get a list of all the shape names, select a cell and run the ListAllCountries Macro.  This Macro will list the names of all the shapes on the active worksheet, starting with the selected cell.

Sub ListAllCountries()

Dim countryName As Shape
Dim i As Long

'Loop through each shape and list its name
For Each countryName In ActiveSheet.Shapes

    Selection.Offset(i, 0).Value = countryName.Name
     i = i + 1

Next countryName

End Sub

 

Rename the shapes

On most maps you get from the internet the names of the shapes are not particularly helpful, for example, the shape may be called “Freeform 102”, rather than “Australia”.  In these circumstances, it is necessary to rename the shapes to something usable.  This can be most painful and time-consuming part of the process.

The easiest way to rename a shape is to select the shape, change the name in the element name box then press Enter to confirm the change.

Map Chart - Change name of Country

However, using this method, it is not possible to give multiple shapes the same name, which is fine for some circumstances but not others (such as the Indonesia example stated above).

Another option, is to rename the shapes using a User Defined Function which I created called NameCountries (see the code below).  This function takes the following form:

=NameCountries(oldName, newName, targetSheetCell)

The NameCountries function takes 3 arguments;

  • oldName = the current name of the shape – this can be a single cell reference or text string
  • newName = the name you wish the shape to be called – this can be a single cell reference or a text string
  • targetSheetCell = the reference to any cell on the sheet which contains the map

By using the NameCountires User Defined Function it is possible to give multiple shapes the same name.

The VBA code for the NameCountries function is here (Note – this code must be entered into a Module).

Public Function NameCountries(oldName As String, newName As String, targetSheetCell As Range)

Dim countryName As Shape
Dim targetSheetName As String

'Get the name of the sheet which contains the map
targetSheetName = targetSheetCell.Parent.Name

'Loop through each shape on the sheet which contains the map
For Each countryName In Sheets(targetSheetName).Shapes

    'Change the name from its existing name to a new name
    If oldName = countryName.Name Then
 
        countryName.Name = newName
 
    End If

Next countryName

End Function

 

List all the shape names . . . again (with the values)

Keep running the ListAllCountries Macro and the NameCountries function until you are happy with all the names.

Next, give each shape a value based on the data you wish to represent, (e.g. temperature, income, life expectancy, school grades, or whatever else you want to use the map for).

 

Create the colored banding and values

Next, create the ranges which will be applied to the countries.  The fill color and left border color will be applied to each of the shapes.

Map Chart - Colored Banding

For the purposes of the ColorToShape User Defined Function (see below) it is necessary for the banding to go from smallest value at the top of the list to largest value at the bottom of the list.

The map shapes do not have a ‘default color’, they will only change when the forced to by the ColorToShape function (see below).  To ensure the formatting is correct, the values for each country must be between the lowest and highest value in the colored banding range.

 

ColorToShape function

The Macros and User Defined Functions so far have been purely to set-up the map template in the right way.  It is the ColorToShape function which drives the working template.

The ColorToShape function takes 4 arguments:

=ColorToShape(countryName, lookupValue, lookupRange, targetSheetCell)
  • countryName = the name of the shape, can be a string or a single  cell reference to a string.
  • lookupValue = the value associated with the country/region/shape, can be a value or a single cell reference to a value.
  • lookupRange = the range of cells containing the values and colored banding to be applied to the countries/regions/shapes.
  • targetSheetCell = the reference to any cell on the sheet which contains the map

When this function is recalculated, it will change the color of the shapes based on the lookupValue and lookupRange.

The VBA code for the ColorToShape function is: (Note – this code must be entered into a Module):

Public Function ColorToShape(countryName As String, lookupValue As Double, _
lookupRange As Range, targetSheetCell as Range)

'Force recalculation
Application.Volatile

Dim countryShape As Shape
Dim lookupCell As Range
Dim targetSheetName As String

targetSheetName = targetSheetCell.Parent.Name

'Set default value of the function to "Not Found"
ColorToShape = "Not Found"

'Loop through each shape on the worksheet containing the map
For Each countryShape In Sheets(targetSheetName).Shapes

    'If the shape and countryName have the same name then
    If countryShape.Name = countryName Then
 
        'Loop through each cell in the colored banding range
        For Each lookupCell In lookupRange
 
            'If the value is less than or equal to the colored banding then
            If lookupCell.Value >= lookupValue Then
 
                'Apply the fill color of the colored banding
                countryShape.Fill.ForeColor.RGB = _
                RGB(Color2RGB(lookupCell.Interior.Color, "R"), _
                Color2RGB(lookupCell.Interior.Color, "G"), _
                Color2RGB(lookupCell.Interior.Color, "B"))
                
                'Apply the left border of the colored banding
                countryShape.Line.ForeColor.RGB = _
                RGB(Color2RGB(lookupCell.Borders(xlEdgeLeft).Color, "R"), _
                Color2RGB(lookupCell.Borders(xlEdgeLeft).Color, "G"), _
                Color2RGB(lookupCell.Borders(xlEdgeLeft).Color, "B"))
 
                'Change the result of the UDF to "Formatted"
                ColorToShape = "Formatted"
                
                'Formatting applied, stop looping through
                Exit For
 
            End If
 
        Next lookupCell
 
    End If

Next countryShape

End Function

The ColorToShape function calls another function to obtain the correct RGB color reference.  Include the following code within the same Module as the ColorToShape function.

Private Function Color2RGB(Color As Long, ColorSelect As String) As Long
Dim Red As Long
Dim Green As Long
Dim Blue As Long

Red = Color Mod 256
Green = Color \ 256 Mod 256
Blue = Color \ 65536 Mod 256

Select Case ColorSelect
 
    Case "R"
        Color2RGB = Red
    Case "G"
        Color2RGB = Green
    Case "B"
        Color2RGB = Blue
 
End Select
 
End Function

 

Conclusion

Hopefully you can see the subtle message that Excel is not a good Business Intelligence tool is flawed.  A bit of Excel/VBA trickery is all that is required to turn Excel into a tool which can create Map Charts.  The expensive BI tools will be much easier to set-up, but will be limited in their options.  Using this method, Excel can be turned into anything you wish, for example, if you want a map of Middle Earth from the Lord of the Rings, it can be achieved with Excel.

 

What next?

Get Excel news, tips & tricks straight to you inbox.  Helping you to save time and achieve more with Excel.

You will also receive the Tab Hopper Add-in for FREE.

Tab Hopper Thumb







Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Leave a Reply

Your email address will not be published. Required fields are marked *