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:
Please follow the below steps to create a US Thermal Map:
Requirement: Visual Basic Knowledge
File to download: US Thermal Map.xlsm
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”.
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
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)
StateAbbr = ActiveCell.Value
StateValue = ActiveCell.Offset(0, 1).Value
Application.ScreenUpdating = True
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
'Debug.Print "StateColor", StateColor
.Fill.ForeColor.RGB = StateColor
This is the result of color mapping.