Gantt Chart with VBA
 

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:D 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

Gantt Chart with VBA
 

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:D 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

Gantt Chart with VBA
 

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

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

--
John Mansfield
http://cellmatrix.net





"u473" wrote:

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:D 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

Gantt Chart with VBA
 

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 ???

Gantt Chart with VBA
 

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/ChartsH...sterStack.html

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

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"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:D 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

Gantt Chart with VBA
 

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.

Gantt Chart with VBA
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"u473" wrote in message
...
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.