Create Heatmap Chart with Excel VBA

A heatmap in Excel is often created using conditional formatting or a color scale, but you can also do it programmatically with VBA. Below, I’ll guide you through the steps and provide a detailed explanation.

VBA Code to Create a Heatmap Chart

This code assumes that you have data in a worksheet and want to create a heatmap based on that data.

Sub CreateHeatmap()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim chartObj As ChartObject
    Dim heatmapRange As Range   
    ' Set the worksheet and data range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Adjust sheet name as necessary
    Set dataRange = ws.Range("A1:D10") ' Adjust your data range here   
    ' Create a new chart
    Set chartObj = ws.ChartObjects.Add(Left:=300, Width:=500, Top:=50, Height:=300)   
    ' Set chart data
    chartObj.Chart.SetSourceData Source:=dataRange   
    ' Set chart type to "Column Clustered" (you can adjust this type if needed)
    chartObj.Chart.ChartType = xlColumnClustered   
    ' Apply Heatmap Colors using conditional formatting (color scale)
    Set heatmapRange = dataRange ' The range where the heatmap will apply   
    ' Clear any previous formatting
    heatmapRange.FormatConditions.Delete   
    ' Add color scale (Green - Yellow - Red)
    With heatmapRange.FormatConditions.AddColorScale(ColorScaleType:=3)
        ' Set color scale properties
        With .ColorScaleCriteria(1)
            .Type = xlConditionValueLowestValue
            .FormatColor.Color = RGB(0, 255, 0) ' Green for lowest
        End With
        With .ColorScaleCriteria(2)
            .Type = xlConditionValuePercentile
            .Formula = "=50" ' Middle percent is yellow
            .FormatColor.Color = RGB(255, 255, 0) ' Yellow for middle
        End With
        With .ColorScaleCriteria(3)
            .Type = xlConditionValueHighestValue
            .FormatColor.Color = RGB(255, 0, 0) ' Red for highest
        End With
    End With   
    ' Adjust the chart formatting if needed
    With chartObj.Chart
        .HasTitle = True
        .ChartTitle.Text = "Heatmap of Data"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Categories"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Values"
    End With
End Sub

Explanation of the Code

1. Setting up the Worksheet and Data Range:
   • The ws variable represents the worksheet where the data is stored. You can adjust the sheet name as necessary.
   • The dataRange represents the data range to be visualized (for example, from A1:D10).
2. Creating a Chart:
   • A chart is created on the worksheet using the ChartObjects.Add method. The chart is positioned on the worksheet at a specific location, which is set by the Left, Width, Top, and Height parameters.
   • The SetSourceData method is used to link the chart with the data range you specified.
3. Setting Chart Type:
   • The chart type is set to xlColumnClustered, which means you will get a clustered column chart. You can change this to other chart types (like xlLine or xlBar) if you prefer a different visualization.
4. Adding Conditional Formatting (Heatmap Colors):
   • The FormatConditions.AddColorScale method is used to apply a color scale to the range.
   • This color scale has three levels:
     • The first level applies to the lowest values and uses the color green (RGB(0, 255, 0)).
     • The second level applies to the middle 50th percentile values and uses yellow (RGB(255, 255, 0)).
     • The third level applies to the highest values and uses red (RGB(255, 0, 0)).
5. Chart Formatting:
   • After applying the heatmap, the code adjusts the chart’s formatting to give it a title, and label the axes as « Categories » and « Values. »
   • You can adjust these titles according to your needs.

Key Points:

• The code uses conditional formatting to apply color scales to the data range, which is the foundation of the heatmap effect.
• The chart is set to a column chart, but you can customize this to your preferred chart type.
• Conditional formatting applies colors based on data values, helping to visualize patterns and outliers.

Customization:

• Data Range: Adjust the dataRange to match your data.
• Chart Type: Change the chart type by modifying the ChartType property. For example, use xlLine for a line chart.
• Colors: You can modify the RGB values to use different colors for the heatmap (e.g., blue for low, yellow for middle, red for high).