Gantt Chart with VBA

[u473]

Can you put me on the right track to generate this Gant Chart with VBA
I do not want to chart it thru Excel Charts tools.
Thank you for your help.
----------------------------------------------
Data Source Structure :
A1 : Rpt Report Date
Col A2:A Item Item#
Col B2:B sStart Scheduled Start Date
Col C2:C sFinish Scheduled Finish Date
Col D2 aStart Actual Start Date
Col E2:E aFinish Actual Finish Date
Col F2:F fStart Forecast Start Date
Col G2:G fFinish Forecast Finish Date
Col H2:H Prog Item % Progress
-----------------------------------------------
Sequence Logic :
PlotRow = 1
For Item = 2 to LastRow
Draw Blue Bar sStart to sFinish
PlotRow = PlotRow+1
'
Select Case Item(Prog)
'
Case 100 ' Item is Complete
Draw Red Bar aStart to aFinish
'
Case 0 ' Item has not started
Draw Yellow Bar fStart to fFinish
'
Case Else ' Item is in progress
Draw Red Bar aStart to Rpt
Draw Yellow Bar Rpt to fFinish
'
End Select
PlotRow = PlotRow + 2
Next Item

 

[Jim Cone]

Record a macro while you build the chart manually.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"u473"
wrote in message
Can you put me on the right track to generate this Gant Chart with VBA
I do not want to chart it thru Excel Charts tools.
Thank you for your help.
----------------------------------------------
Data Source Structure :
A1 : Rpt Report Date
Col A2:A Item Item#
Col B2:B sStart Scheduled Start Date
Col C2:C sFinish Scheduled Finish Date
Col D2 aStart Actual Start Date
Col E2:E aFinish Actual Finish Date
Col F2:F fStart Forecast Start Date
Col G2:G fFinish Forecast Finish Date
Col H2:H Prog Item % Progress
-----------------------------------------------
Sequence Logic :
PlotRow = 1
For Item = 2 to LastRow
Draw Blue Bar sStart to sFinish
PlotRow = PlotRow+1
'
Select Case Item(Prog)
'
Case 100 ' Item is Complete
Draw Red Bar aStart to aFinish
'
Case 0 ' Item has not started
Draw Yellow Bar fStart to fFinish
'
Case Else ' Item is in progress
Draw Red Bar aStart to Rpt
Draw Yellow Bar Rpt to fFinish
'
End Select
PlotRow = PlotRow + 2
Next Item

 

[John Mansfield]

The Microsoft Knowledge Base link below provides the VBA code for creating a
Gnatt chart . . .

http://support.microsoft.com/kb/213447

 

[u473]

Thank you for the answer, but all the samples I have seen including
with Excel Charts
are about one bar per task. My case is two bars per task, on top of
each other.
My Gantt Chart type, Scheduled versus Actual is a classic recurring
case.
This time I am going to take the bull by the horns because I am trying
to understand & adapt KB213447.
This graph is referred as a floating bar clusters,
After study, It seems my adaptation would concern the "With .
SeriesCollection(1)" section,
This KB213447 is a good sample but I do not think I can adapt it to
fit my case.
Suggestion ???

 

[Jon Peltier]

I've done exactly what you want, using regular Excel floating bar charts.

I think that (a) you don't want to use VBA, (b) you don't want to rule out
Excel's charting infrastructure, and (c) you have to try the examples in the
first link with regular bar charts, then convert them to floating bar
charts, which is what you use to construct Excel Gantt charts (shown in the
second and third links):

Clustered Stacked Column Charts (works for horizontal bar charts as well):
http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html

Excel Gantt Charts (real Excel charts):
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343

Advanced Excel Gantt Charts:
http://peltiertech.com/Excel/Charts/GanttChart.html

- Jon

 

[u473]

Thank you for your answer.
I had started recording a macro from Excel Floating Bars and was
successful
in displaying 2 bars on the same line, one for Scheduled, one for
Actual.
However I could not make bars to overlap,
The clue is probably to plot the second serie on a secondary axis.
In any case, I am going to study your material in depth and return to
regular bar charts.

 

[Jon Peltier]

Primary axis only. Stagger the data:


[blenk] Start Duration Start Duration
Task 1 Predicted 01/01/07 120 [blank] [blank]
Task 1 Actual [blank] [blank] 01/05/07 135
[blank line for spacing]
Task 2 Predicted 02/01/07 90 [blank] [blank]
Task 2 Actual [blank] [blank] 01/25/07 85
[blank line for spacing]
Task 3 Predicted 02/15/07 60 [blank] [blank]
Task 3 Actual [blank] [blank] 02/01/07 65

Make a stacked bar chart.

- Jon