VBA Code to Create a Gantt Chart in Excel

VBA Code:

' VBA Code to Create a Gantt Chart in Excel
' This code creates a basic Gantt chart using Excel shapes and formatting

Sub CreateGanttChart()
    Dim chartSheet As Worksheet
    Dim chartRange As Range
    Dim chartShape As Shape
    Dim taskRow As Range
    Dim taskName As String
    Dim startDate As Date
    Dim endDate As Date
    Dim barLeft As Double
    Dim barTop As Double
    Dim barWidth As Double
    Dim barHeight As Double
    ' Set the chart sheet where the Gantt chart will be created
    Set chartSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    chartSheet.Name = "Gantt Chart"
    ' Set the range of tasks and dates
    Set chartRange = ThisWorkbook.Sheets("Sheet1").Range("A2:C6") ' Replace with your range
    ' Set the initial position for the Gantt chart bars
    barLeft = 100
    barTop = 100
    barHeight = 20
    ' Loop through each task and create a Gantt chart bar
    For Each taskRow In chartRange.Rows
        ' Get the task details from the range
        taskName = taskRow.Cells(1).Value
        startDate = taskRow.Cells(2).Value
        endDate = taskRow.Cells(3).Value
        ' Calculate the width of the Gantt chart bar based on the start and end dates
        barWidth = (endDate - startDate + 1) * 20 ' Adjust the value as needed
        ' Create the Gantt chart bar as a rectangle shape
        Set chartShape = chartSheet.Shapes.AddShape(msoShapeRectangle, barLeft, barTop, barWidth, barHeight)
        ' Format the Gantt chart bar
        chartShape.Fill.ForeColor.RGB = RGB(0, 176, 240) ' Replace with your desired fill color
        chartShape.Line.ForeColor.RGB = RGB(0, 112, 192) ' Replace with your desired line color
        chartShape.TextFrame2.TextRange.Characters.Text = taskName
        ' Increment the top position for the next Gantt chart bar
        barTop = barTop + barHeight + 10 ' Adjust the value as needed
    Next taskRow
End Sub

Check All VBA Codes

Join Our Telegram Group techguruplus telegram group Join Our WhatsApp Group techguruplus whatsapp group
Nazim Khan - Author Image

Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]

Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.

Leave a Comment