Excel Macro to Create Gantt Chart

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
• Microsoft Excel for OS/2, versions 2.2, 2.21 and 3.0

NOTE: The following article is provided as information only.

      Because of legalities, this article should only be
      transmitted via FAX to customers, not read over the phone.  When
      sending out this article, a disclaimer should be attached.

SUMMARY

You can use Microsoft Excel to create a Gantt Chart showing task status for project planning and control. The macro listed below creates a basic Gantt Chart. Instructions on how to use the macro follow.

Enter the following in a macro sheet:

   A1: Gantt_Chart
   A2:
   A3:  =ECHO(FALSE)
   A4:  min=OFFSET(SELECTION(),1,1,1,1)
   A5:  title_text=INPUT("Please Enter the Title for this Chart.",2)
   A6:  =NEW(2,2)
   A7:  =GALLERY.BAR(3,TRUE)
   A8:  =SELECT("S1")
   A9:  =PATTERNS(2,1,1,1,,2,1,3,2,FALSE,FALSE)
   A10: =SELECT("Axis 2")
   A11: =SCALE(1,1,1,TRUE,TRUE,FALSE)
   A12: =SELECT("Axis 1")
   A13: =SCALE(min,TRUE,TRUE,31,TRUE,FALSE,FALSE,FALSE)
   A14: =FORMAT.TEXT(,,2)
   A15: =FORMAT.FONT(0,1,FALSE,"Helv",8,FALSE,FALSE,FALSE,FALSE)
   A16: =GRIDLINES(FALSE,FALSE,TRUE,FALSE)
   A17: =ATTACH.TEXT(1)
   A18: =FORMAT.FONT(0,1,FALSE,"Helv",12,TRUE,FALSE,FALSE,FALSE)
   A19: =FORMULA("="""&title_text&"""")
   A20: =SEND.KEYS("{ESC}")
   A21: =RETURN()

MORE INFORMATION

To make this macro work properly in Microsoft Excel versions 2.x, modify the macro above with the following changes:

   A6:  =NEW(2)
   A8:  =SELECT("S1P1")
   A20: =SELECT("")

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

3. Highlight A2:D8 and activate the macro by choosing the CTRL key combination you defined for Gantt_Chart.

4. Enter the chart title when prompted.

REFERENCES

"Microsoft Excel User's Guide, Book 1." Version 4.0, chapter 14.

"Microsoft Excel User's Guide, Book 2." Version 4.0, chapter 7.

"Microsoft Excel User's Guide." Version 3.0, chapters 13 and 19.

"PC Magazine," Feb 26,1991, "Spreadsheets," page 410. Craig Stinson.