US Thermal Map

 

US Thermal Map

usmapcolor
msofficeshare logo

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:

  1. 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.
  2. 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.
  3. 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.
  4. The Visual Basic coding is more complicated compared with the other maps using indicators because of the color coding.
  5. 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.

pic1 us thermal map
pic2 us thermal map

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.

pic3 us thermal map

Step 3: Choose your color scale and find out their color codes. I chose 10 color differentials for my example.

pic4 us thermal map

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”.

pic5 us thermal map

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.

More Posts

Excel Vlookup Function

The VLOOKUP function in Excel is a powerful tool that allows you to search for a specific value in a table and return a corresponding