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
Join Our WhatsApp Group
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.
