应用excel,实现项目管理甘特图
演示
操作
- 设置根本信息
- 填入需要及对应工期
- 抉择需要,跟进人,启动日期,将主动生成甘特图
波及的VB代码
Public Function GetArrayLength(arr As Variant) As Integer If IsEmpty(arr) Then GetArrayLength = 0 Else GetArrayLength = UBound(arr) - LBound(arr) + 1 End IfEnd FunctionPublic Function IsNull(val As Variant) As Boolean Dim result If val = "" Then result = True ElseIf IsEmpty(val) Then result = True Else result = False End If IsNull = resultEnd FunctionPublic Function IsWeekend(InputDate As Variant) As Boolean temp = CDate(InputDate) Select Case Weekday(temp) Case vbSaturday, vbSunday IsWeekend = True Case Else IsWeekend = False End SelectEnd FunctionPublic Function GetExceptCollection(r1 As range, r2 As range, Optional offset$ = 0) As Variant Dim arr1, arr2, arr3(), i&, row row = Application.ThisCell.row - offset arr1 = Application.Transpose(r1) arr2 = Application.Transpose(r2) Dim dict As Object Set dict = CreateObject("scripting.dictionary") For Each a In arr2 If Not IsNull(a) Then dict(a) = 1 End If Next ReDim arr3(GetArrayLength(arr1)) For Each a In arr1 If Not IsNull(a) And Not dict.exists(a) Then i = i + 1 arr3(i) = a End If Next If Not IsNull(arr3(row)) Then GetExceptCollection = arr3(row) Else GetExceptCollection = "" End IfEnd FunctionPublic Function GetEndDateByDuration(startDateV As Variant, durationI As Integer, holidaysR As range, workdaysR As range) As Date If IsNull(startDateV) Then GetEndDateByDuration = "" End If startDateD = CDate(startDateV) If Not IsDate(startDateD) Then GetEndDateByDuration = "" End If Dim holidaysA, workdaysA, holidaysD As Object, workdaysD As Object holidaysA = Application.Transpose(holidaysR) workdaysA = Application.Transpose(workdaysR) Set holidaysD = CreateObject("scripting.dictionary") For Each a In holidaysA temp = CDate(a) If Not IsNull(temp) Then holidaysD(temp) = 1 End If Next Set workdaysD = CreateObject("scripting.dictionary") For Each a In workdaysA temp = CDate(a) If Not IsNull(temp) Then workdaysD(temp) = 1 End If Next Dim actualDurationI As Integer, index As Integer Do While index < durationI temp = startDateD + actualDurationI If workdaysD.exists(temp) Then index = index + 1 ElseIf holidaysD.exists(temp) Then ElseIf IsWeekend(temp) Then Else index = index + 1 End If actualDurationI = actualDurationI + 1 Loop GetEndDateByDuration = startDateD + actualDurationI - 1 End Function
Excel文件
https://github.com/chencaize/fileRepo/blob/main/%E7%94%98%E7%89%B9%E5%9B%BE.xlsm