XL: Visual Basic Module to Create Gantt Chart

The information in this article applies to:

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel, you can create Gantt Charts that show task status for project planning and control. The example in this article shows the steps necessary to generate this type of charts using a Visual Basic, Applications Edition, procedure.

MORE INFORMATION

The following Visual Basic macro creates a Gantt Chart.

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. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

Enter the following in a Visual Basic module:

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

Example of How to Use The Macro

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 date format you want to use on the chart.

3. Highlight A2:D8. To run the macro, choose Macro from the Tools menu, and select the gantt_chart macro. In version 97, click Macro on the Tools menu, then click Macro and select the gantt_chart macro.

4. Enter the chart title when prompted.

For additional information on creating a Gantt, please see the following article(s) in the Microsoft Knowledge Base:

   ARTICLE-ID: Q73281
   TITLE     : Excel: Creating Gantt Charts

REFERENCES

"User s Guide," version 5.0, chapters 15, 17, 18