Option Explicit
Const lng開始行 As Long = 3
Dim var全体開始日 As Variant
Const txt全体開始日アドレス As String = "B1"
Const cnst休日シート名 As String = "休日"
Const cnst担当者シート名 As String = "担当者"
Const idx処理中日付 As Long = 1
Const idx工数 As Long = 2
Const idx1日あたり工数 As Long = 3
Const idx休日リスト As Long = 4
Dim txt休日リスト() As String
Dim lngID列, lng先行タスクID列, lng同一タスクID列, lng優先度列, lngタスク列, lng工数列, lng担当者列 As Long
Dim lng指定開始日列, lng指定終了日列, lng予定開始日列, lng予定終了日列 As Long
Dim lng最終行 As Long
Dim lng最終列 As Long
Dim rngタスク一覧 As Range
Dim obj担当者 As Object
Sub スケジュール算出()
var全体開始日 = Range(txt全体開始日アドレス)
lng最終行 = 最終行取得(ActiveSheet)
lng最終列 = 最終列取得(ActiveSheet)
With ActiveSheet
Set rngタスク一覧 = .Range(.Cells(lng開始行, 1), .Cells(lng最終行, lng最終列))
End With
Call 優先度順に並び替え
Dim obj休日シート As Worksheet: Set obj休日シート = ActiveWorkbook.Sheets(cnst休日シート名)
Dim var休日リスト As Variant
With obj休日シート
var休日リスト = .Range(.Cells(2, 1), .Cells(最終行取得(obj休日シート), 1))
ReDim txt休日リスト(LBound(var休日リスト, 1) To UBound(var休日リスト, 1))
Dim i As Long
For i = LBound(var休日リスト) To UBound(var休日リスト)
txt休日リスト(i) = var休日リスト(i, 1)
Next
End With
Dim obj担当者シート As Worksheet: Set obj担当者シート = ActiveWorkbook.Sheets(cnst担当者シート名)
Dim var担当者リスト As Variant
With obj担当者シート
var担当者リスト = .Range(.Cells(2, 1), _
.Cells(最終行取得(obj担当者シート), 最終列取得(obj担当者シート)))
Set obj担当者 = CreateObject("Scripting.Dictionary")
Dim j As Long
For j = LBound(var担当者リスト) To UBound(var担当者リスト)
Dim var担当者休日リスト As Variant: var担当者休日リスト = txt休日リスト
If var担当者リスト(j, 3) <> "" Then
Dim k As Long
Dim tmp担当者休日リスト As Variant: tmp担当者休日リスト = Split(var担当者リスト(j, 3), ",")
ReDim Preserve var担当者休日リスト(UBound(txt休日リスト) + UBound(tmp担当者休日リスト))
For k = LBound(tmp担当者休日リスト) To UBound(tmp担当者休日リスト)
var担当者休日リスト(UBound(txt休日リスト) + k) = tmp担当者休日リスト(k)
Next
End If
Dim var担当者詳細リスト(1 To 4) As Variant
var担当者詳細リスト(idx処理中日付) = var全体開始日
var担当者詳細リスト(idx工数) = 0
var担当者詳細リスト(idx1日あたり工数) = var担当者リスト(j, 2)
var担当者詳細リスト(idx休日リスト) = var担当者休日リスト
obj担当者.Add var担当者リスト(j, 1), var担当者詳細リスト
Next
End With
Call 予定開始終了日設定
End Sub
Function 優先度順に並び替え()
rngタスク一覧.Select
ActiveWorkbook.Worksheets("タスク").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("タスク").Sort.SortFields.Add Key:=Range("D4:D20"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("タスク").Sort.SortFields.Add Key:=Range("G4:G20"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("タスク").Sort.SortFields.Add Key:=Range("H4:H20"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("タスク").Sort.SortFields.Add Key:=Range("I4:I20"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("タスク").Sort
.SetRange rngタスク一覧
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Function
Function タスク一覧取得() As Variant
Dim varタスク一覧 As Variant
With ActiveSheet
varタスク一覧 = .Range(.Cells(lng開始行, 1), .Cells(lng最終行, lng最終列))
End With
Dim i As Long
For i = LBound(varタスク一覧, 2) To UBound(varタスク一覧, 2)
Dim txtセルの値 As String: txtセルの値 = varタスク一覧(1, i)
Select Case txtセルの値
Case "ID"
lngID列 = i
Case "先行タスクID"
lng先行タスクID列 = i
Case "同一タスクID"
lng同一タスクID列 = i
Case "優先度"
lng優先度列 = i
Case "タスク"
lngタスク列 = i
Case "工数"
lng工数列 = i
Case "担当者"
lng担当者列 = i
Case "指定開始日"
lng指定開始日列 = i
Case "指定終了日"
lng指定終了日列 = i
Case "予定開始日"
lng予定開始日列 = i
Case "予定終了日"
lng予定終了日列 = i
End Select
Next
タスク一覧取得 = varタスク一覧
End Function
Sub 予定開始終了日設定()
Dim varタスク一覧 As Variant: varタスク一覧 = タスク一覧取得
Dim i As Long
For i = LBound(varタスク一覧) + 1 To UBound(varタスク一覧)
Dim lng工数 As Long: lng工数 = varタスク一覧(i, lng工数列)
Dim txt担当者 As String: txt担当者 = varタスク一覧(i, lng担当者列)
If obj担当者.Exists(txt担当者) Then
Dim var担当者情報 As Variant: var担当者情報 = obj担当者.Item(txt担当者)
Dim lng1日あたりの工数 As Long: lng1日あたりの工数 = var担当者情報(idx1日あたり工数)
ActiveSheet.Cells(i + lng開始行 - 1, lng予定開始日列) = var担当者情報(idx処理中日付)
Do While (lng工数 > 0)
Debug.Print (var担当者情報(idx処理中日付) & " " & var担当者情報(idx工数) & " " & lng工数)
If (lng工数 + var担当者情報(idx工数)) >= lng1日あたりの工数 Then
If (lng工数 + var担当者情報(idx工数)) = lng1日あたりの工数 Then
ActiveSheet.Cells(i + lng開始行 - 1, lng予定終了日列) = var担当者情報(idx処理中日付)
End If
lng工数 = lng工数 - (lng1日あたりの工数 - var担当者情報(idx工数))
var担当者情報(idx処理中日付) = 日付を進める(var担当者情報(idx処理中日付), var担当者情報(idx休日リスト))
var担当者情報(idx工数) = 0
Else
var担当者情報(idx工数) = var担当者情報(idx工数) + lng工数
lng工数 = 0
ActiveSheet.Cells(i + lng開始行 - 1, lng予定終了日列) = var担当者情報(idx処理中日付)
End If
Loop
obj担当者.Item(txt担当者) = var担当者情報
End If
Next
Dim obj開始表 As Object
Set obj開始表 = CreateObject("Scripting.Dictionary")
End Sub
Function 日付を進める(ByVal prm日付 As Variant, ByVal prm休日リスト As Variant)
Dim var進めた後の日付 As Variant: var進めた後の日付 = prm日付 + 1
Do While True
If Weekday(var進めた後の日付) <= 1 Or 7 <= Weekday(var進めた後の日付) Then
var進めた後の日付 = var進めた後の日付 + 1
ElseIf 配列に存在するかチェック(var進めた後の日付, prm休日リスト) Then
var進めた後の日付 = var進めた後の日付 + 1
Else
日付を進める = var進めた後の日付
Exit Function
End If
Loop
End Function
Function 配列に存在するかチェック(ByVal prm対象文字列 As Variant, ByVal prm日付配列 As Variant) As Boolean
Dim i As Long
For i = LBound(prm日付配列) To UBound(prm日付配列)
If CDate(prm対象文字列) = CDate(prm日付配列(i)) Then
配列に存在するかチェック = True
Exit Function
End If
Next
配列に存在するかチェック = False
End Function
Option Explicit
Public Const TITLE_NAME_PREFIX = "▼"
Dim var開始時刻 As Variant
Sub log(ByVal strメッセージ As String)
Debug.Print Format(Now(), "HH:mm:ss ") & strメッセージ
End Sub
Sub init開始時刻()
var開始時刻 = Now()
End Sub
Function get開始時刻()
get開始時刻 = var開始時刻
End Function
Function get処理時刻()
get処理時刻 = Format(Now() - var開始時刻, "HH:mm:ss")
End Function
Sub setステータスバー(ByVal strメッセージ As String)
If IsEmpty(var開始時刻) Then
var開始時刻 = Now()
End If
Application.StatusBar = get処理時刻() & " " & strメッセージ
End Sub
Function 不要シート削除(対象ブック情報 As Workbook, ByVal 残すシート名 As String)
Dim 前状態 As Boolean
前状態 = Application.DisplayAlerts
Application.DisplayAlerts = False
Dim ws As Worksheet
For Each ws In 対象ブック情報.Worksheets
If ws.Name <> 残すシート名 Then
Worksheets(ws.Name).Delete
End If
Next ws
Application.DisplayAlerts = 前状態
End Function
Function 処理続行判断(message As String)
Dim rc As VbMsgBoxResult
rc = MsgBox(message + Chr(10) + "処理を続行しますか?", vbYesNo, vbQuestion)
If rc = vbYes Then
MsgBox "処理を続けます", vbInformation
Else
MsgBox "処理を中止しました。", vbCritical
End
End If
End Function
Function getShapesProperty(ByRef targetSheet As Worksheet, Optional ByVal objType As Long = -999, Optional ByVal formCtlType As Long = -999) As Variant
Dim ret As Variant
Dim i As Long
Dim obj As Variant
i = 0
For Each obj In targetSheet.Shapes
If obj.Type = objType Then
If obj.FormControlType = formCtlType Then
i = i + 1
End If
ElseIf objType = -999 Or obj.Type = objType Then
i = i + 1
End If
Next
If 0 <> i Then
ReDim ret(i - 1, 12)
i = 0
For Each obj In targetSheet.Shapes
If obj.Type = objType Then
If obj.FormControlType = formCtlType Then
ret(i, 0) = obj.Type
ret(i, 1) = obj.AlternativeText
On Error Resume Next
ret(i, 2) = obj.ControlFormat.Value
ret(i, 3) = obj.Left
ret(i, 4) = obj.Top
ret(i, 5) = obj.Width
ret(i, 6) = obj.Height
ret(i, 7) = obj.TopLeftCell.Address(False, False)
ret(i, 8) = obj.TopLeftCell.Row
ret(i, 9) = obj.TopLeftCell.Column
ret(i, 10) = obj.Left.BottomRightCell.Address(False, False)
ret(i, 11) = obj.Left.BottomRightCell.Row
ret(i, 12) = obj.Left.BottomRightCell.Column
i = i + 1
End If
ElseIf objType = -999 Or obj.Type = objType Then
ret(i, 0) = obj.Type
ret(i, 1) = obj.AlternativeText
On Error Resume Next
ret(i, 2) = obj.TextFrame.Characters.Text
ret(i, 3) = obj.Left
ret(i, 4) = obj.Top
ret(i, 5) = obj.Width
ret(i, 6) = obj.Height
ret(i, 7) = obj.TopLeftCell.Address(False, False)
ret(i, 8) = obj.TopLeftCell.Row
ret(i, 9) = obj.TopLeftCell.Column
ret(i, 10) = obj.Left.BottomRightCell.Address(False, False)
ret(i, 11) = obj.Left.BottomRightCell.Row
ret(i, 12) = obj.Left.BottomRightCell.Column
i = i + 1
End If
Next
End If
getShapesProperty = ret
End Function
Public Function IsArrayEx(varArray As Variant) As Long
On Error GoTo ERROR_
If IsArray(varArray) Then
IsArrayEx = IIf(UBound(varArray) >= 0, 1, 0)
Else
IsArrayEx = -1
End If
Exit Function
ERROR_:
If Err.Number = 9 Then
IsArrayEx = 0
End If
End Function
Function reDimResult(ByVal topLevelElementSize As Integer, ByRef results() As Variant)
Select Case IsArrayEx(results)
Case 1
ReDim Preserve results(topLevelElementSize, UBound(results, 2) + 1)
Case 0
ReDim Preserve results(topLevelElementSize, 0)
End Select
End Function
Function 一次配列に値を追加(ByRef valueList As Variant, ByVal 追加設定値 As String)
Select Case IsArrayEx(valueList)
Case 1
ReDim Preserve valueList(UBound(valueList) + 1)
Case 0
ReDim Preserve valueList(0)
End Select
valueList(UBound(valueList)) = 追加設定値
End Function
Function 二次元配列行列逆転(ByRef var二次元配列 As Variant)
Dim var逆転後配列 As Variant
ReDim var逆転後配列( _
LBound(var二次元配列, 2) To UBound(var二次元配列, 2), _
LBound(var二次元配列) To UBound(var二次元配列))
Dim i, j As Long
For i = LBound(var二次元配列) To UBound(var二次元配列, 2)
For j = LBound(var二次元配列) To UBound(var二次元配列)
var逆転後配列(i, j) = var二次元配列(j, i)
Next
Next
二次元配列行列逆転 = var逆転後配列
End Function
Public Function editHYPERLINK数式( _
ByVal strフォルダ名 As String, _
ByVal strファイル名 As String, _
ByVal strシート名 As String, _
ByVal str座標 As String) As String
editHYPERLINK数式 = _
"=HYPERLINK(""[" & strフォルダ名 & "\" & strファイル名 & "]" & _
strシート名 & "!" & str座標 & """,""" & str座標 & """)"
End Function
Function 検索該当文字の赤太文字化(prmRange As Range, prmTargetString As String)
Dim txt As String
Dim i, m As Integer
Dim targetRange As Range
If prmTargetString = "" Then
Exit Function
End If
For Each targetRange In prmRange
txt = targetRange.Value
m = Len(prmTargetString)
i = InStr(1, txt, prmTargetString)
Do Until i = 0
With prmRange.Characters(i, m)
.Font.Bold = True
.Font.ColorIndex = 3
End With
i = InStr(i + 1, txt, prmTargetString)
Loop
Next
Set targetRange = Nothing
End Function
Function タイトル名指定でリスト値を取得(titleName As String, targetSheet As Worksheet) As Variant
Dim targetRangeList As Range
Dim targetVariantList As Variant
Set targetRangeList = タイトル名指定でリスト値のRange情報を取得(titleName, targetSheet)
If targetRangeList.Count = 1 Then
targetVariantList = Array(targetRangeList.Item(1).Value)
Else
targetVariantList = targetRangeList.Value
End If
タイトル名指定でリスト値を取得 = targetVariantList
End Function
Function タイトル名指定でリスト値のRange情報を取得(titleName As String, targetSheet As Worksheet) As Range
Dim matchCount As Long
Dim checkValue As String
matchCount = WorksheetFunction.CountIf(targetSheet.UsedRange, titleName)
If 1 <> matchCount Then
MsgBox "タイトル「" & titleName & "」が複数見つかったため、処理を中断しました。"
End
End If
Dim FoundCell As Range
Set FoundCell = targetSheet.UsedRange.Find(what:=titleName, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=False, MatchByte:=False)
Dim i, MaxRow, MaxCol As Long
With targetSheet
With .Range(.Cells(FoundCell.Row, FoundCell.Column), .Cells(Rows.Count, FoundCell.Column))
MaxRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
MaxCol = .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
End With
For i = 1 To (MaxRow - FoundCell.Row)
checkValue = .Cells(FoundCell.Row + i, MaxCol).Value
If "" = checkValue Or InStr(1, checkValue, TITLE_NAME_PREFIX) > 0 Then
If 1 = i Then
Call 処理続行判断("タイトル名「" + titleName + "」に対するリスト値が設定されていません。")
MaxRow = FoundCell.Row + 1
Else
MaxRow = FoundCell.Row + i - 1
End If
Exit For
End If
Next
Set タイトル名指定でリスト値のRange情報を取得 = _
targetSheet.Range(.Cells((FoundCell.Row + 1), FoundCell.Column), .Cells(MaxRow, MaxCol))
End With
End Function
Function 最終行取得(ws対象シート As Worksheet) As Long
最終行取得 = ws対象シート.UsedRange.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
End Function
Function 最終列取得(ws対象シート As Worksheet) As Long
最終列取得 = ws対象シート.UsedRange.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
End Function