Microsoft Excel is, perhaps, the most commonly used Windows-based spreadsheet program. If it's possible to produce a Gantt chart using Lotus 1-2-3, (See Lotus 1-2-3 ) then it should certainly be possible to do something similar in Excel.
The following Visual Basic "script" will produce a simple Gantt chart. It isn't any more elegant than the one produced with Lotus 1-2-3, just more colorful.
Sub gantt_chart() Dim rge As Variant Dim mn As Variant Dim shtname As Variant 'defines the variables rge = Selection.Address() 'get the cell address mn = Selection.Offset(1, 1) 'return the min value for the scale Title = InputBox("Please enter the title") 'Asks the user for title shtname = ActiveSheet.Name 'retains the name of current sheet Application.ScreenUpdating=False 'Turns screen updating off Charts.Add 'Create a paper model chart ActiveChart.ChartWizard Source:=Sheets(shtname).Range(rge), _ Gallery:=xlBar, Format:=3, PlotBy:=xlColumns, CategoryLabels _ :=1, SeriesLabels:=1, HasLegend:=1, Title:=Title, _ CategoryTitle:="", ValueTitle:="", _ ExtraTitle:="" ' Basic chart definition ActiveChart.Legend.Delete 'deletes the legend ActiveChart.SeriesCollection(1).Select 'activates series 1 With Selection.Border .Weight = xlThin .LineStyle = xlNone End With 'definition for the border for series 1 Selection.InvertIfNegative = False 'turns Invert if negative to false Selection.Interior.ColorIndex = xlNone 'indicates that the area is set to none ActiveChart.PlotArea.Select 'select the chart plot area ActiveChart.Axes(xlCategory).Select 'select axis(1) With ActiveChart.Axes(xlCategory) .ReversePlotOrder = True .TickLabelSpacing = 1 .TickMarkSpacing = 1 .AxisBetweenCategories = True End With 'axis 1 definition ActiveChart.Axes(xlValue).Select 'select axis(2) With ActiveChart.Axes(xlValue) .MinimumScale = mn .MaximumScaleIsAuto = True .MinorUnitIsAuto = True .MajorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = False .HasMajorGridlines = True .HasMinorGridlines = False End With ' Axis(2) definition End Sub
1. Open a new worksheet and enter the following values:
A1: B1: START C1: DAYS D1: DAYS A2: TASK B2: DATE C2: COMPLETED D2: REMAINING A3: TASK-1 B3: 1/1/91 C3: 150 D3: 15 A4: TASK-2 B4: 5/1/91 C4: 21 D4: 31 A5: TASK-3 B5: 7/1/91 C5: 0 D5: 114 A6: TASK-4 B6: 10/1/91 C6: 0 D6: 4 A7: TASK-5 B7: 10/15/91 C7: 0 D7: 31 A8: TASK-6 B8: 11/1/91 C8: 0 D8: 2
2. Select cell A2 and format it with the month/day/year format you want to use on the chart.
3. Highlight A2:D8. To activate the macro, choose Macro from the Tools menu, and select the gantt_chart macro.
4. Enter the chart title when prompted.
The Gantt Chart should now be displayed on the screen. Additional formatting can be added as needed.
The above VB program was written by the programmers at Microsoft. They add the following disclaimer and copyright.
Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations.REFERENCES "User s Guide," version 5.0, chapters 15, 17, 18 Copyright 1993 Microsoft Corporation. All rights reserved.