Creating a Timeline Chart in Excel with VBA

A Timeline Chart is a visual representation of events over time, often used for project management, historical data analysis, or tracking milestones. In Excel, you can create a Timeline Chart using a Scatter Plot with data labels.

Steps to Create a Timeline Chart Using VBA

1. Prepare Data: The timeline consists of two columns: Dates (X-axis) and Events (Y-axis).
2. Insert a Scatter Chart: Use VBA to create a scatter plot.
3. Format the Chart: Adjust markers, add labels, and set the axes properly.
4. Enhance Visualization: Customize colors, gridlines, and labels.

VBA Code for Timeline Chart

Below is the complete VBA code to generate a Timeline Chart dynamically.

Sub CreateTimelineChart()
    Dim ws As Worksheet
    Dim ch As ChartObject
    Dim rngX As Range, rngY As Range
    Dim lastRow As Long
    ' Define the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
     ' Define the data range (Dates in column A, Events in column B)
    Set rngX = ws.Range("A2:A" & lastRow)
    Set rngY = ws.Range("B2:B" & lastRow)
    ' Delete any existing chart
    For Each ch In ws.ChartObjects
        ch.Delete
    Next ch
    ' Add a new chart
    Set ch = ws.ChartObjects.Add(Left:=100, Width:=600, Top:=50, Height:=40
    ' Set chart type to Scatter Plot
    With ch.Chart
        .ChartType = xlXYScatter
        .SetSourceData Source:=Union(rngX, rngY)
        ' Format axes 
        With .Axes(xlCategory)
            .HasTitle = True
            .AxisTitle.Text = "Date"
            .TickLabels.Orientation = 45 ' Rotate labels for better readability
        End With
        With .Axes(xlValue)
            .HasTitle = True
            .AxisTitle.Text = "Events"
            .MajorGridlines.Delete ' Remove gridlines for clarity 
        End With
 ' Add Data Labels
        Dim i As Integer
        For i = 1 To .SeriesCollection(1).Points.Count
            With .SeriesCollection(1).Points(i)
                .ApplyDataLabels xlDataLabelsShowValue
            End With
        Next i         ' Customize chart appearance
        .HasTitle = True
        .ChartTitle.Text = "Project Timeline"
        .Legend.Delete
    End With
    ' Clean up
    Set ws = Nothing
    Set ch = Nothing
    Set rngX = Nothing
    Set rngY = Nothing
End Sub

Detailed Explanation of VBA Code

1. Identify the Worksheet and Data Range

Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name

• Defines the worksheet where the data is stored.

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

• Finds the last row in column A to dynamically adjust the data range.

Set rngX = ws.Range("A2:A" & lastRow)
Set rngY = ws.Range("B2:B" & lastRow)

• Sets up the X-axis (dates) and Y-axis (events) range.

2. Remove Any Existing Chart

For Each ch In ws.ChartObjects
ch.Delete
Next ch

• Deletes any existing chart on the worksheet to prevent duplicates.

3. Insert a New Chart

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

• Creates a new chart at a specified position.

.ChartType = xlXYScatter
.SetSourceData Source:=Union(rngX, rngY)

• Sets the chart type to Scatter Plot and assigns the data source.

4. Format Axes

With .Axes(xlCategory)
    .HasTitle = True
    .AxisTitle.Text = "Date"
    .TickLabels.Orientation = 45 ' Rotate labels
End With

• Labels the X-axis as « Date » and rotates labels for better readability.

With .Axes(xlValue)
    .HasTitle = True
    .AxisTitle.Text = "Events"
    .MajorGridlines.Delete ' Remove gridlines
End With

• Labels the Y-axis as « Events » and removes major gridlines.

5. Add Data Labels

Dim i As Integer
For i = 1 To .SeriesCollection(1).Points.Count
    With .SeriesCollection(1).Points(i)
        .ApplyDataLabels xlDataLabelsShowValue
    End With
Next i

• Loops through each data point and adds labels to display event names.

6. Customize Chart Appearance

.HasTitle = True
.ChartTitle.Text = "Project Timeline"
.Legend.Delete

• Sets the chart title as « Project Timeline » and removes the legend.

How to Use the VBA Code

1. Enter Data in Sheet1 (or change the sheet name in the code):

2. | A (Date)    |   B (Event)   |
3. |—————|————–|
4. | 01/01/2024  |  Project Start  |
5. | 15/02/2024  |  Phase 1 Done  |
6. | 10/04/2024  |  Testing Begins  |
7. | 20/06/2024  |  Final Review  |

1. Open the VBA Editor (ALT + F11).
2. Insert a New Module (Right-click on a module > Insert > Module).
3. Paste the VBA Code and run CreateTimelineChart.

Customizations

• Change Colors: Modify the marker styles using .MarkerStyle and .MarkerBackgroundColor.
• Event Labels: Use DataLabels.Position = xlLabelPositionAbove for better positioning.
• Dynamic Sheet Selection: Add an InputBox to let users select the sheet.

Conclusion

This VBA code efficiently generates a Timeline Chart in Excel. It dynamically reads dates and events, creates a scatter plot, and formats the chart for better readability