US Thermal Map
Thermal Map is also called Choropleth Map or Heat Map. The US Thermal Map is one of the most complicated programs to write. It involves creating a map by slicing out each individual state and then put the states back together to form the US map. Each state will be named with a code (state abbreviation code). Then a Visual Basic model is written to control the color of each state based on the value associated with it. Usually the darker the color, the higher the value associated with such state.
The thermal map is significantly more complicated to build than the other maps that I created in this website (e.g. US Map with Smiley Faces, World Map with Bar Charts, and World Map with Population Indicators). However, I consider that the thermal map is inferior to other maps with indicators. The thermal map has the following weaknesses besides the difficulties to build:
- The color may look similar for the ranges close to each other, especially if you want to have more categories. For example, a map with 5 color differentials may have one color looked quite different from another. A map with 10 color differentials may have one color looked somehow similar to its neighboring range of colors.
- You can consider to using totally different colors for each category, for example, use blue for lower range of values, purple with middle range, and red with higher range. Then the readers would have to frequently refer to the legend to make sure they read the map correctly. In fact, using a range of similar colors is easier for readers to read, for example, very light red for lower range of values and very dark red for higher range of values.
- The choice of color is difficult to model with the use of color identification system in Microsoft. Microsoft assigns each color a specific code which ranges from 7 to 8 digits. Unless you have such a list of color codes, it will become a process of trial and error to get the colors you desire.
- The Visual Basic coding is more complicated compared with the other maps using indicators because of the color coding.
- It is difficult to read if the state is too small. For example, the original 13 states of the US are generally small and the readers have to look carefully to distinguish them. Washington DC is another problem because it is just a “dot” on the map. It has to be singled out to express it some other way.
Please follow the below steps to create a US Thermal Map:
Requirement: Visual Basic Knowledge
File to download: US Thermal Map.xlsm
Related articles: World Map with Population Indicators, World Map with Bar Charts, US Map with Smiley Faces
Step 1: Create the data. Please note that I need the column C (State Abbreviation) because I named the states by the state abbreviations. If you name the states by the full name of the State, you don’t need column C.
Step 2: Create a US map. This step is quite complicated and I don’t want to go into the details because it involves using some advanced graphic software to slice out the state and make each state to become an individual entity so that you can change the properties of such individual state. For example, you see that Texas (TX) State is actually an individual entity (object) with its own properties that are totally separated from other states. The object of Texas State is named as “TX” using the state abbreviation codes.
You can use the map that I included in the “File to download:” (US Thermal Map.xlsm) so you don’t have to spend a lot of time to create one yourself.
Step 3: Choose your color scale and find out their color codes. I chose 10 color differentials for my example.
Step 4: Write the Visual Basic codes.
I used the dynamic referencing for the color range. The color of the state depends on the value of such state relative to the state with the highest value. In my example, Nebraska (NE) has the highest value ($4,935) among all states. So the color range will be classified into 10 categories from $0 to $4,935.
The formula to be used is StatePercent = StateValue / MaxDataValue * 100 where “* 100” is to change the number from percentage to general value (e.g. from 90% to 90).
Since there are 50 states in the US (ignoring Washington DC, Puerto Rico, and Virgin Island), the VB code will be looped for 50 times. The looping will call on a subroutine called “ChooseColor” to find the right color category associates with the values of such state and then returns the color category of the state back to the main module (“MapColor”). The color property of the state object will be changed by the Visual Basic module when you run it.
Please note that I created an additional subroutine program called “LegendColor”. It plots the color to the legend in the “Map” worksheet (cell S14 to S23). This subroutine is not a mandatory program to run this report. It does help me to plot the legend if I want to change the colors of the color categories. When you need to run the VB module, just click the “Color Mapping” on the “Map” worksheet. When you click the “Color Mapping” button, it will not initiate the “LegendColor” module.
Below are the codes of the VB modules. They include the main module (“MapColor”) and the subroutine (“ChooseColor”), but exclude the “LegendColor”.
Sub MapColor()
Dim StateRange As Range
Dim MaxDataValue As Double, StateValue As Double, StatePercent As Double
Dim StateCount As Integer
Dim StateAbbr As String
Application.ScreenUpdating = False
Set StateRange = Range(“MapData”)
MaxDataValue = Application.WorksheetFunction.Max(StateRange)
If MaxDataValue = 0 Then
MaxDataValue = 0.01 ‘to avoid division by zero error
End If
Sheets(“Data”).Select
Range(“FirstData”).Select
StateAbbr = Range(“FirstData”).Value
StateValue = ActiveCell.Offset(0, 1).Value
StateCount = 1
For StateCount = 1 To 50
StatePercent = StateValue / MaxDataValue * 100
Call ChooseColor(StatePercent, StateAbbr)
Sheets(“Data”).Select
ActiveCell.Offset(1, 0).Select
StateAbbr = ActiveCell.Value
StateValue = ActiveCell.Offset(0, 1).Value
Next StateCount
Sheets(“Map”).Select
Application.ScreenUpdating = True
End Sub
Sub ChooseColor(StatePercent, StateAbbr)
Dim StateColor As Long
Select Case StatePercent
Case 0 To 10
StateColor = 16777215
Case 10 To 20
StateColor = 13097981
Case 20 To 30
StateColor = 8562164
Case 30 To 40
StateColor = 8225247
Case 40 To 50
StateColor = 5071062
Case 50 To 60
StateColor = 5193429
Case 60 To 70
StateColor = 3947716
Case 70 To 80
StateColor = 2824370
Case 80 To 90
StateColor = 2428045
Case 90 To 100
StateColor = 2031719
End Select
‘Debug.Print “StateColor”, StateColor
Sheets(“Map”).Select
With ActiveSheet.Shapes(StateAbbr)
.Fill.ForeColor.RGB = StateColor
End With
End Sub
This is the result of color mapping.
