Create Gauge Chart with Excel VBA

Creating a gauge chart in Excel using VBA requires a combination of pie charts and doughnut charts to simulate the gauge effect. In this detailed explanation, I will walk you through the process of creating a gauge chart dynamically using VBA.

Steps to Create a Gauge Chart with VBA

1. Understanding the Gauge Chart Structure:
   • The gauge chart consists of three parts:
     • Background (Outer ring): Represents the full range of values (e.g., 0 to 100).
     • Filled section (Inner doughnut): Represents the actual value (e.g., 70 out of 100).
     • Needle (Optional): A line or pointer that indicates the current value.
2. Setting Up the Data:
   • You’ll need a data set that contains the range of values (e.g., minimum, actual value, and maximum).
   • Example:

• | Category | Value |
• |——————|——-|
• | Min Value |   0   |
• | Actual Value |  70   |
• | Max Value | 100   |
• | Remaining Value |  30   |

3. VBA Code to Create the Gauge Chart: Here’s the VBA code to create a dynamic gauge chart in Excel. This code assumes you have data in cells A1 to B4, where the Value column contains the actual values for the chart.

Sub CreateGaugeChart()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name   
    ' Create a new chart
    Dim chartObj As ChartObject
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    With chartObj.Chart
        ' Set chart type to Pie
        .ChartType = xlDoughnut      
        ' Define the data source
        .SetSourceData Source:=ws.Range("A1:B4")       
        ' Set the chart title
        .HasTitle = True
        .ChartTitle.Text = "Gauge Chart"       
        ' Add a series to the chart for the gauge segments
        With .SeriesCollection.NewSeries
            .XValues = ws.Range("A1:A4") ' Categories
            .Values = ws.Range("B1:B4") ' Values
            .Name = "Gauge"
        End With       
        ' Format the series to create the gauge effect
        With .SeriesCollection(1)
            ' Make the first section (Min Value) invisible (Background)
            .Points(1).Format.Fill.Transparency = 1           
            ' Make the second section (Actual Value) visible (Gauge)
            .Points(2).Format.Fill.ForeColor.RGB = RGB(0, 176, 80) ' Green color for the gauge
            .Points(2).Format.Fill.Transparency = 0           
            ' Make the third section (Remaining Value) visible (Background)
            .Points(3).Format.Fill.ForeColor.RGB = RGB(255, 0, 0) ' Red color for remaining value
            .Points(3).Format.Fill.Transparency = 0
        End With       
        ' Add the needle (optional)
        Dim needle As Shape
        Set needle = ws.Shapes.AddLine(BeginX:=250, BeginY:=150, EndX:=250, EndY:=100)
        needle.Line.ForeColor.RGB = RGB(255, 255, 255) ' White color for the needle
        needle.Line.Weight = 2       
        ' Adjust the angle of the needle based on the value
        Dim angle As Double
        angle = (ws.Range("B2").Value / ws.Range("B3").Value) * 180 ' Calculate angle based on actual value
        needle.Rotation = 90 - angle ' Adjust rotation to position the needle      
    End With
End Sub

Detailed Explanation:

1. Creating the Chart:
   • The ChartObjects.Add method creates a new chart on the worksheet. The Left, Width, Top, and Height properties determine where the chart will be placed.
   • The ChartType is set to xlDoughnut to create a doughnut-shaped chart. This allows us to create the background and filled sections.
2. Setting the Data:
   • The SetSourceData method defines the data range for the chart. Here, it refers to cells A1:B4.
   • The series data is set with categories (Min, Actual, Max, Remaining) and their corresponding values.
3. Formatting the Chart:
   • Each section of the doughnut chart is formatted individually using the .Points(n).Format.Fill properties.
   • The transparency of the first section (Min Value) is set to 100%, making it invisible.
   • The second section (Actual Value) is given a green color, and the remaining section (Remaining Value) is set to red.
4. Needle (Optional):
   • A line is drawn to act as the needle. The Shapes.AddLine method creates a line at the center of the doughnut chart.
   • The angle of the needle is calculated based on the actual value relative to the total range (max value). This angle is used to rotate the needle.

Customization:

• You can change the colors of the gauge by modifying the RGB values in the .Format.Fill.ForeColor.RGB lines.
• The needle can be styled by adjusting the Line.Weight and Line.ForeColor.
• The size and position of the chart can be modified by adjusting the parameters in ChartObjects.Add.

Conclusion:

This VBA code allows you to dynamically create a gauge chart in Excel. By adjusting the data in cells A1:B4, you can change the appearance of the chart to reflect different values. You can also customize the colors, styles, and sizes as per your requirements.