Create Interactive Heat Maps For Data Visualization with Excel VBA

To create interactive heat maps for data visualization using Excel VBA, follow these steps. I’ll guide you through the process, explaining the code in detail.

Step 1: Preparing Data

Assume that your data is in a range (e.g., A1:D10), where each cell represents a data point. The goal is to use color coding to represent values in this range, with higher values being displayed in a more intense color and lower values in a lighter color.

Step 2: Define the VBA Code

Below is a detailed VBA code that will generate an interactive heat map based on the values in a specified range. It uses conditional formatting to apply colors based on the value in each cell.

Sub CreateHeatMap()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim minValue As Double
    Dim maxValue As Double
    Dim cell As Range
    Dim colorScale As ColorScale   
    ' Define the worksheet and the data range
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Change "Sheet1" to your actual sheet name
    Set dataRange = ws.Range("A1:D10")     ' Adjust the range according to your data   
    ' Find the minimum and maximum values in the range
    minValue = Application.WorksheetFunction.Min(dataRange)
    maxValue = Application.WorksheetFunction.Max(dataRange)   
    ' Clear any previous conditional formats
    dataRange.FormatConditions.Delete   
    ' Apply a 3-color scale conditional format
    With dataRange.FormatConditions.AddColorScale(3)
        ' Set the color for the lowest value (min)
        With .ColorScaleCriteria(1)
            .Type = xlConditionValueNumber
            .Value = minValue
            .FormatColor.Color = RGB(255, 255, 255)  ' Light color (white)
        End With       
        ' Set the color for the middle value (mid)
        With .ColorScaleCriteria(2)
            .Type = xlConditionValueNumber
            .Value = (minValue + maxValue) / 2
            .FormatColor.Color = RGB(255, 255, 0)  ' Yellow (mid range)
        End With       
        ' Set the color for the highest value (max)
        With .ColorScaleCriteria(3)
            .Type = xlConditionValueNumber
            .Value = maxValue
            .FormatColor.Color = RGB(255, 0, 0)  ' Red (high value)
        End With
    End With   
    ' Create Interactivity: Add a drop-down to change color scale dynamically
    Call AddInteractivity(ws, dataRange)
End Sub

Sub AddInteractivity(ws As Worksheet, dataRange As Range)
    ' Create a ComboBox for selecting color scale type
    Dim comboBox As Object
    Set comboBox = ws.Shapes.AddFormControl(xlDropDown, 10, 10, 150, 20) ' Position and size
    With comboBox.ControlFormat
        .AddItem "3-Color Scale"
        .AddItem "2-Color Scale"
        .AddItem "No Color Scale"
        .ListIndex = 1 ' Default to 3-Color Scale
    End With   
    ' Add event handler for ComboBox change
    ws.OnCalculate = "ChangeColorScale"
End Sub

Sub ChangeColorScale()
    Dim comboBox As Object
    Set comboBox = ActiveSheet.Shapes(1).ControlFormat   
    ' Get selected color scale option
    Dim selection As Integer
    selection = comboBox.ListIndex   
    ' Reapply the corresponding color scale based on selection
    Select Case selection
        Case 1 ' 3-Color Scale
            Call CreateHeatMap
        Case 2 ' 2-Color Scale (simplified version)
            Call ApplyTwoColorScale
        Case 3 ' No Color Scale
            Call RemoveColorScale
    End Select
End Sub

Sub ApplyTwoColorScale()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim minValue As Double
    Dim maxValue As Double   
    ' Define the worksheet and the data range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set dataRange = ws.Range("A1:D10")
    ' Find the minimum and maximum values
    minValue = Application.WorksheetFunction.Min(dataRange)
    maxValue = Application.WorksheetFunction.Max(dataRange)   
    ' Clear any previous conditional formats
    dataRange.FormatConditions.Delete   
    ' Apply a 2-color scale conditional format
    With dataRange.FormatConditions.AddColorScale(2)
        ' Set the color for the lowest value (min)
        With .ColorScaleCriteria(1)
            .Type = xlConditionValueNumber
            .Value = minValue
            .FormatColor.Color = RGB(255, 255, 255)  ' White (low value)
        End With       
        ' Set the color for the highest value (max)
        With .ColorScaleCriteria(2)
            .Type = xlConditionValueNumber
            .Value = maxValue
            .FormatColor.Color = RGB(255, 0, 0)  ' Red (high value)
        End With
    End With
End Sub

Sub RemoveColorScale()
    Dim ws As Worksheet
    Dim dataRange As Range   
    ' Define the worksheet and the data range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set dataRange = ws.Range("A1:D10")   
    ' Clear any previous conditional formats
    dataRange.FormatConditions.Delete
End Sub

Step 3: Explanation of the Code

1. Main Function – CreateHeatMap

• Worksheet and Range Setup: The worksheet (ws) and data range (dataRange) are defined, targeting the specific cells with your data.
• Finding Min and Max Values: The minimum and maximum values in the data range are determined to set the boundaries for the color scale.
• Conditional Formatting: The FormatConditions.AddColorScale method is used to apply a color scale. The code applies a 3-color scale with:
  • White for the minimum value.
  • Yellow for the middle value.
  • Red for the maximum value.

2. Interactivity with ComboBox – AddInteractivity

• A ComboBox is added to the worksheet to allow the user to select between different color scale options.
• The ComboBox provides three options:
  1. 3-Color Scale (default).
  2. 2-Color Scale (simplified).
  3. No Color Scale (removes color formatting).
• The OnCalculate event triggers when a change is made to the ComboBox, calling the ChangeColorScale procedure.

3. ChangeColorScale Procedure

• This procedure checks which option the user has selected from the ComboBox and applies the corresponding color scale (or removes it).

4. ApplyTwoColorScale & RemoveColorScale

• The ApplyTwoColorScale function simplifies the color scale to only two colors: white for the minimum value and red for the maximum value.
• The RemoveColorScale function deletes any existing color formatting.

Step 4: Running the Code

• Run the CreateHeatMap procedure to initialize the heat map with interactive options.
• The user can choose between different color scales through the ComboBox, providing an interactive experience for data visualization.

Conclusion:

This code enables the creation of an interactive heat map with Excel VBA, allowing users to dynamically change the color scale of data visualization based on their preferences. By using conditional formatting and VBA interactivity, you can enhance your data presentation and allow for a customizable view of the data range.