Create Gantt Chart with Excel VBA

Creating a Gantt chart in Excel using VBA involves several steps to ensure that the chart is dynamic and can easily be updated based on input data. Below is a detailed VBA code along with explanations on how to create a Gantt chart.

Steps for Gantt Chart Creation:

1. Prepare Data: You will need a table that includes tasks, start dates, and end dates.
2. Initialize the Chart: Create a blank chart or use a bar chart to represent the Gantt chart.
3. Define the Chart Ranges: Set the data ranges for tasks, start dates, and end dates.
4. Draw the Gantt Bars: Use stacked bar charts to represent each task’s duration.
5. Customize the Chart: Apply colors, labels, and adjust the axis for better visualization.

Sample Data Table:

Let’s assume your data is structured as follows (in Excel):

VBA Code for Gantt Chart:

Sub CreateGanttChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim rng As Range
    Dim taskStartRange As Range
    Dim taskEndRange As Range
    Dim chartData As Range
    Dim numRows As Integer
    Dim startDate As Date
    Dim endDate As Date
    Dim taskCount As Integer
    Dim i As Integer   
    ' Set the worksheet object
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Find the number of rows of tasks in the data
    taskCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row - 1   
    ' Set the ranges for the data
    Set taskStartRange = ws.Range("B2:B" & taskCount + 1) ' Start Date
    Set taskEndRange = ws.Range("C2:C" & taskCount + 1) ' End Date   
    ' Create a new chart object
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=600, Top:=50, Height:=300)
    With chartObj.Chart
        .ChartType = xlBarStacked      
        ' Set the data for the chart
        Set chartData = ws.Range("A1:C" & taskCount + 1)       
        ' Add the chart data
        .SetSourceData Source:=chartData
        ' Format the chart
        .HasTitle = True
        .ChartTitle.Text = "Gantt Chart"       
        ' Set the axis titles and format the axes
        .Axes(xlCategory).CategoryNames = taskStartRange
        .Axes(xlCategory).TickLabels.Orientation = xlUpward       
        ' Format the series
        For i = 1 To .SeriesCollection.Count
            .SeriesCollection(i).Format.Fill.Solid
            .SeriesCollection(i).Format.Fill.ForeColor.RGB = RGB(255, 102, 102) ' Set color for bars
        Next i       
        ' Add custom formatting to make the chart look more like a Gantt chart
        .Axes(xlValue).MinimumScale = DateValue("01/01/2025") ' Set the start date for the Gantt chart
        .Axes(xlValue).MaximumScale = DateValue("01/20/2025") ' Set the end date for the Gantt chart
        .Axes(xlValue).MajorUnit = 1 ' Set the unit of the axis (1 day)
        .Axes(xlValue).MinorUnit = 1
    End With   
    ' Create a dynamic range for the task duration calculation
    For i = 2 To taskCount + 1
        startDate = ws.Cells(i, 2).Value
        endDate = ws.Cells(i, 3).Value       
        ' Create helper columns for start and end date differences
        ws.Cells(i, 4).Value = DateDiff("d", startDate, endDate) ' Duration in days
    Next i   
    MsgBox "Gantt Chart Created Successfully!", vbInformation
End Sub

Explanation of the Code:

1. Worksheet Setup: The code starts by setting the worksheet where the Gantt chart will be created (ws = ThisWorkbook.Sheets(« Sheet1 »)).
2. Data Ranges: It defines the ranges for the task start dates and end dates (taskStartRange, taskEndRange).
3. Creating the Chart:
   • A ChartObject is created on the sheet.
   • The chart type is set to xlBarStacked (stacked bar chart), which will allow the task duration to be displayed as bars.
4. Chart Data Source: The data range is set to cover the task names, start dates, and end dates (chartData = ws.Range(« A1:C » & taskCount + 1)).
5. Formatting the Chart:
   • The title of the chart is set.
   • The category axis (tasks) is customized with upward text.
   • The value axis (dates) is formatted to show days, with the scale adjusted to the project start and end dates.
6. Helper Column for Task Duration: A helper column calculates the duration of each task in days (difference between start and end dates).
7. Bar Formatting: Each bar in the Gantt chart is colored, and you can modify this to match your preference.

Customizing the Chart:

• Task Colors: Modify the .SeriesCollection(i).Format.Fill.ForeColor.RGB line to change the color of the task bars.
• Date Range: You can adjust the .Axes(xlValue).MinimumScale and .Axes(xlValue).MaximumScale to reflect the actual project timeline.
• Units: The .Axes(xlValue).MajorUnit controls the unit scale (e.g., set to 1 for days, 7 for weeks).

Conclusion:

This VBA code helps automate the creation of a Gantt chart in Excel, allowing you to visualize tasks and their timelines dynamically. By adjusting the start and end dates, you can quickly generate an updated Gantt chart for different projects.