Creating custom Map Charts using shapes and VBA
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 Dashboard 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.
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.
The World Map Template is available 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.
Whilst I try to create safe and reliable templates, I can (and often do) make mistakes. Please backup copies of your files before opening this template or running any VBA code. If you do find any bugs or errors, please let me know using mycontact page.
By using this or any other template from the site, you agree that I will not be held liable for any type of damages, and use is entirely at your own risk.
Click here to download the World Map Template.
I must say Thank You to allowing me to use and distribute their World Map images within the World Map Template. 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.
Changing the values in Column Q, or changing the colored banding in Cells T2 T8 will change the formatting of the countries on 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 on 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.
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.
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.
Get a map with each country/region as a separate shape.
Rename all the shapes to usable names.
Get a final list all the shapes (with usable names) 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.
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 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.
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.
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.
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:
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).
Get the name of the sheet which contains the map
Loop through each shape on the sheet which contains the map
Change the name from its existing name to a new name
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).
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.
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.
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):
Set default value of the function to Not Found
Loop through each shape on the worksheet containing the map
If the shape and countryName have the same name then
Loop through each cell in the colored banding range
If the value is less than or equal to the colored banding then
Apply the fill color of the colored banding
countryShape.Fill.ForeColor.RGB = _ lor, 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
Formatting applied, stop looping through
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.
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.
Excel Off the Grid has reached its first birthday. 12 months ago, when I started the site, I had no idea what I was doing (to be honest, I still dont), but its been fun. There have been over 100,000
From stock parts to holiday villas, from employees to logos, there are many reasons you may want to automatically change an image based on a cell value. In this post, well explore 3 different methods to achieve this task: Named
My first experience of Bullet Charts came from reading Information Dashboard Design by Stephen Few in 2007. This was the year I started to get serious with dashboards. Initially, to create Bullet Charts I used the 3 stacked bar/column charts
The code below is intended to be a basic reference to anybody trying to use VBA code for Powerpoint. Its certainly not complete, but it is code Ive used for various reasons. I will try to add to and improve
Want to Learn VBA & Macros?If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are: