Create dynamic chart axis labels in Excel VBA

Objective

We want to dynamically update the X-axis labels of a chart based on a range of values that may change over time. This is useful when working with data that expands or contracts, such as sales trends, stock prices, or other time-series data.

VBA Code for Dynamic Chart Axis Labels

This VBA macro will:

1. Create a dynamic named range for axis labels.
2. Assign the named range to the X-axis of a chart.
3. Automatically update the chart whenever data changes.

Sub CreateDynamicChartAxisLabels()
    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim rngLabels As Range
    Dim rngValues As Range
    Dim lastRow As Long
    Dim chartName As String
    Dim namedRangeX As String
    Dim namedRangeY As String
    ' Set worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name  
    ' Find last row with data in column A (Labels) and column B (Values)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Define dynamic ranges
    Set rngLabels = ws.Range("A2:A" & lastRow) ' X-axis labels
    Set rngValues = ws.Range("B2:B" & lastRow) ' Y-axis values
    ' Define named ranges dynamically
    namedRangeX = "DynamicLabels"
    namedRangeY = "DynamicValues"
    ' Delete named ranges if they already exist
    On Error Resume Next
    ThisWorkbook.Names(namedRangeX).Delete
    ThisWorkbook.Names(namedRangeY).Delete
    On Error GoTo 0
    ' Create new named ranges
    ThisWorkbook.Names.Add Name:=namedRangeX, RefersTo:=rngLabels
    ThisWorkbook.Names.Add Name:=namedRangeY, RefersTo:=rngValues
    ' Check if chart exists, else create it
    chartName = "DynamicChart"
    On Error Resume Next
    Set cht = ws.ChartObjects(chartName)
    On Error GoTo 0
    If cht Is Nothing Then
        ' Create chart if it does not exist
        Set cht = ws.ChartObjects.Add(Left:=100, Top:=50, Width:=400, Height:=300)
        cht.Name = chartName
        cht.Chart.ChartType = xlLine ' Change to desired chart type
    End If
    ' Set chart data source dynamically
    With cht.Chart
        .SetSourceData Source:=rngValues
        .SeriesCollection(1).XValues = "=" & ws.Name & "!" & namedRangeX
        .SeriesCollection(1).Values = "=" & ws.Name & "!" & namedRangeY
        .HasTitle = True
        .ChartTitle.Text = "Dynamic Chart with VBA"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "X-Axis Labels"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Y-Axis Values"
    End With
    ' Refresh the chart
    cht.Chart.Refresh
    ' Notify user
    MsgBox "Dynamic chart updated successfully!", vbInformation, "VBA Chart Update"
End Sub

Detailed Explanation of the Code

Step 1: Define the Worksheet and Data Range

Set ws = ThisWorkbook.Sheets(« Sheet1 »)

• This sets the target worksheet where the data and chart exist. You can change « Sheet1 » to the correct sheet name.

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

• This finds the last non-empty row in column A (Labels) to determine the range dynamically.

Set rngLabels = ws.Range(« A2:A » & lastRow)

Set rngValues = ws.Range(« B2:B » & lastRow)

• These lines define the dynamic ranges for the X-axis labels and Y-axis values.

Step 2: Create Named Ranges

namedRangeX = « DynamicLabels »

namedRangeY = « DynamicValues »

• These are the names assigned to the ranges.

ThisWorkbook.Names(namedRangeX).Delete

ThisWorkbook.Names(namedRangeY).Delete

• If the named ranges already exist, they are deleted to avoid conflicts.

ThisWorkbook.Names.Add Name:=namedRangeX, RefersTo:=rngLabels

ThisWorkbook.Names.Add Name:=namedRangeY, RefersTo:=rngValues

• These lines create new named ranges dynamically, which adjust as data changes.

Step 3: Create or Update the Chart

chartName = « DynamicChart »

Set cht = ws.ChartObjects(chartName)

• This checks if the chart already exists. If it doesn’t, it creates a new chart.

Set cht = ws.ChartObjects.Add(Left:=100, Top:=50, Width:=400, Height:=300)

• If the chart does not exist, this creates one.

cht.Name = chartName

cht.Chart.ChartType = xlLine

• This sets the chart name and type (you can change xlLine to another type like xlColumn).

Step 4: Set the Chart Data Source

.SetSourceData Source:=rngValues

.SeriesCollection(1).XValues = « = » & ws.Name & « ! » & namedRangeX

.SeriesCollection(1).Values = « = » & ws.Name & « ! » & namedRangeY

• This assigns the named ranges to the X-axis and Y-axis of the chart.

Step 5: Customize Chart Appearance

.HasTitle = True

.ChartTitle.Text = « Dynamic Chart with VBA »

• Adds a title to the chart.

.Axes(xlCategory).HasTitle = True

.Axes(xlCategory).AxisTitle.Text = « X-Axis Labels »

• Sets the X-axis title.

.Axes(xlValue).HasTitle = True

.Axes(xlValue).AxisTitle.Text = « Y-Axis Values »

• Sets the Y-axis title.

Step 6: Refresh the Chart and Notify the User

cht.Chart.Refresh

• Refreshes the chart to ensure updates take effect.

MsgBox « Dynamic chart updated successfully! », vbInformation, « VBA Chart Update »

• Displays a message confirming the chart update.

How to Use This Macro

1. Prepare Data
   • Column A: X-axis labels (e.g., Dates, Categories).
   • Column B: Y-axis values (e.g., Sales, Counts).
2. Run the Macro
   • Open Visual Basic for Applications (VBA) (ALT + F11).
   • Insert a New Module.
   • Copy-paste the code into the module.
   • Run the macro CreateDynamicChartAxisLabels.
3. Chart Updates Automatically
   • Whenever data changes, re-run the macro to update the axis labels dynamically.

Conclusion

This VBA solution ensures that your chart remains dynamically updated with changing data. It is especially useful for dashboards, automated reports, and interactive Excel applications.