Project VBAを掌握する極限の知見：リソース作業時間の週次集計とExcel自動生成のアーキテクチャ
Microsoft Project（以下、MS Project）におけるリソース管理は、プロジェクトマネジメントの生命線である。しかし、プロジェクト計画（.mpp）の深淵に潜むAssignment（アサインメント）データを抽出し、実用に足る週次レポートへと昇華させるプロセスは、多くのシニアエンジニアやシステム管理者にとって頭痛の種であり続けている。
MS ProjectのCOMオブジェクトモデルは、Excelのそれとは比較にならないほど複雑なライフサイクルとメモリ管理の罠を孕んでいる。本稿では、MS Projectの内部構造を熟知したチーフアーキテクトの視点から、Assignmentデータを極限まで高速に集計し、Excelへシームレスに出力するVBAアーキテクチャの全貌を解説する。
—
1. MS Project VBAにおける「見えない罠」とメモリ最適化
MS Projectのオブジェクトモデルを操作する際、最大のボトルネックとなるのは「COM境界を跨ぐ不要なプロパティアクセス」と「暗黙的なオブジェクトの残存」である。
特に `Assignment` や `Resource` オブジェクトをループ処理する際、`.Task`, `.Resource`, `.Project` といった親オブジェクトへの逆参照を安易に繰り返すと、COMのラッパーオブジェクトがヒープ上に乱立し、最終的にVBAランタイムのメモリリークや、最悪の場合はProjectプロセスのクラッシュを招く。
究極のメモリ管理原則
1. オブジェクト変数への確実な代入と解放: ループ内で生成される子オブジェクトは必ず明示的な変数で受け、イテレーションの都度 `Nothing` を代入して参照カウントを即座にデクリメントする。
2. 画面描画とイベントの完全停止: `Application.ScreenUpdating` だけでなく、Project特有のバックグラウンド計算を抑制する。
—
2. アーキテクチャ設計：週次集計エンジン
今回構築するシステムは、MS Project側からアクティブなプロジェクトの全アサインメントを走査し、タイムスケールデータ（TimeScaleData）を用いて週次の実作業時間を抽出し、Excelの所定のフォーマットへ高速転記する。
レガシーなExcel連携でよく見られる「セルを一つずつ叩く」手法は論外である。2次元配列（Variant Array）へのメモリ上でのデータ構築を行い、最後の一撃でExcelのシートへバルク転記する。
実装コード：実戦投入可能な週次集計・Excel出力マクロ
以下のコードは、エラーハンドリング、メモリの明示的解放、そしてパフォーマンスを極限まで高めた実用コードである。ProjectのVBAエディタ（`ThisProject` または標準モジュール）に配置し、実行する。
Option Explicit
‘ ==============================================================================
‘ 処理名: Projectリソース週次作業時間集計・Excel自動生成エンジン
‘ アーキテクチャノート:
‘ – COMオブジェクトのライフサイクルを厳密に管理
‘ – TimeScaleDataを活用した効率的な時系列集計
‘ – 2次元配列によるExcelへの一括バルク転記（I/Oボトルネックの排除）
‘ ==============================================================================
Sub ExportWeeklyResourceWorkload()
‘ — 1. 変数宣言と初期化 —
Dim prj As Project
Set prj = ActiveProject
Dim t As Task
Dim asn As Assignment
Dim rsc As Resource
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim dictData As Object ‘ キー: “リソース名_YYYY-Wxx”, 値: 作業時間(H)
Set dictData = CreateObject(“Scripting.Dictionary”)
Dim lngTaskCount As Long
Dim lngAsnCount As Long
‘ パフォーマンス最適化の極限：Project側の描画・計算停止
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.Calculation = pjManual
‘ — 2. データの収集と週次集計 (TimeScaleDataの活用) —
‘ プロジェクトの開始日と終了日を基準に週次バケットを切る
Dim projStart As Date
Dim projEnd As Date
projStart = prj.ProjectStart
projEnd = prj.ProjectFinish
‘ 各アサインメントを走査
For Each t In prj.Tasks
If Not t Is Nothing Then
If Not t.Summary And t.Active Then
For Each asn In t.Assignments
If Not asn Is Nothing Then
‘ 実作業時間を週次（pjTimescaleWeeks）で取得
Dim tsValues As TimeScaleValues
‘ 引数: Type, Start, End, Unit
Set tsValues = asn.TimeScaleData(projStart, projEnd, pjTimescaleWeeks, pjWorkCumulative)
Dim tsv As TimeScaleValue
For Each tsv In tsValues
If tsv.Value <> “” Then
Dim resourceName As String
resourceName = asn.ResourceName
‘ 週の識別子（例: 2023-W42）を作成
Dim weekKey As String
weekKey = resourceName & “_” & Format(tsv.StartDate, “yyyy”) & “-W” & Format(DatePart(“ww”, tsv.StartDate, vbMonday, vbFirstFourDays), “00”)
‘ Dictionaryに作業時間（分単位から時間に変換：Projectの内部単位は分の場合があるため要確認だが、通常TimeScaleDataはWorkingTimeを返す）
‘ ※PJの作業時間設定に依存するため、必要に応じて / 60 等の調整を入れる
Dim workHours As Double
workHours = Val(tsv.Value) / 60 ‘ 分単位を時間に換算（環境により異なるため注意）
If dictData.Exists(weekKey) Then
dictData(weekKey) = dictData(weekKey) + workHours
Else
dictData.Add weekKey, workHours
End If
End If
Set tsv = Nothing
Next tsv
Set tsValues = Nothing
End If
Set asn = Nothing
Next asn
End If
End If
Set t = Nothing
Next t
‘ — 3. Excelプロセスの生成と出力処理 —
Set xlApp = CreateObject(“Excel.Application”)
xlApp.Visible = False
xlApp.ScreenUpdating = False
xlApp.DisplayAlerts = False
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Sheets(1)
xlWs.Name = “Weekly_Workload”
‘ ヘッダーの構築
xlWs.Cells(1, 1).Value = “リソース名”
xlWs.Cells(1, 2).Value = “週（Week）”
xlWs.Cells(1, 3).Value = “実作業時間 (H)”
‘ Dictionaryから配列への展開準備
Dim keys As Variant
keys = dictData.Keys
Dim i As Long
Dim outputArray() As Variant
If dictData.Count > 0 Then
ReDim outputArray(1 To dictData.Count, 1 To 3)
For i = 0 To dictData.Count – 1
Dim splitKey() As String
splitKey = Split(keys(i), “_”)
outputArray(i + 1, 1) = splitKey(0) ‘ リソース名
outputArray(i + 1, 2) = splitKey(1) ‘ 週
outputArray(i + 1, 3) = dictData(keys(i)) ‘ 作業時間
Next i
‘ 一括バルク転記（極限のI/O最適化）
xlWs.Range(xlWs.Cells(2, 1), xlWs.Cells(dictData.Count + 1, 3)).Value = outputArray
End If
‘ 簡易フォーマット適用
With xlWs.Range(“A1:C1”)
.Font.Bold = True
.Interior.Color = RGB(220, 220, 220)
End With
xlWs.Columns(“A:C”).AutoFit
‘ 保存ダイアログの表示または特定パスへの保存
Dim savePath As String
savePath = Environ(“USERPROFILE”) & “\Desktop\Resource_Weekly_Report_” & Format(Now, “yyyymmdd_hhnnss”) & “.xlsx”
xlWb.SaveAs savePath
MsgBox “レポートの生成が完了しました。” & vbCrLf & “保存先: ” & savePath, vbInformation, “完了”
CleanUp:
‘ — 4. 厳密なオブジェクト解放 (Memory Cleanup) —
On Error Resume Next
Application.ScreenUpdating = True
Application.Calculation = pjAutomatic
If Not xlWb Is Nothing Then xlWb.Close False
If Not xlApp Is Nothing Then
xlApp.ScreenUpdating = True
xlApp.Quit
End If
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
Set dictData = Nothing
Exit Sub
ErrorHandler:
MsgBox “予期せぬエラーが発生しました。” & vbCrLf & _
“Error Number: ” & Err.Number & vbCrLf & _
“Description: ” & Err.Description, vbCritical, “致命的エラー”
Resume CleanUp
End Sub
—
3. チーフアーキテクトが解説するコードの急所
上記のコードベースにおいて、シニアエンジニアとして注目すべき技術的ポイントをいくつか解説する。
1. `TimeScaleData` の正確なハンドリング
MS Projectのタスクやアサインメントの工数は、単一のプロパティ（`Work` や `ActualWork`）では時系列の変動を捉えられない。`TimeScaleData` メソッドを用いることで、指定した期間（`pjTimescaleWeeks`）ごとのマトリクスデータを取得できる。
ここで重要なのは、取得した `TimeScaleValues` コレクションの要素（`TimeScaleValue`）をループする際も、必ずループ内で個別のオブジェクト変数を `Nothing` にクリアすることである。これを怠ると、巨大なプロジェクトファイルでは数万個のCOMオブジェクトがメモリ上に残り続け、VBAのメモリ上限に到達する。
2. バルク転記（Bulk Transfer）の哲学
`For` ループの中で `xlWs.Cells(i, j).Value = …` を実行するアプローチは、COMの境界を何万回も跨ぐことになり、実行時間が数分〜数十分へと爆発的に悪化する。
本コードでは、一度 `Variant` 型の2次元配列 `outputArray()` に全データをメモリ上で構築し、`Range.Value = Array` によって1回のメモリーコピーでExcelへ流し込んでいる。これにより、処理時間は一瞬（数ミリ秒オーダー）で完了する。
3. 環境依存の安全なクリーンアップ
エラー発生時であっても、MS Project側の `ScreenUpdating` や `Calculation`（計算モード）が `pjManual` のまま取り残されると、ユーザーのその後の手動操作に致命的な支障をきたす。
`CleanUp` ラベルを設け、`On Error Resume Next` と組み合わせることで、いかなる例外が発生しようとも確実にアプリケーションの状態を原状復帰させる堅牢な設計としている。
—
総括
Project VBAによるシステム連携は、単なる「マクロの記録」の延長線上には存在しない。COMのメモリモデル、オブジェクトのライフサイクル、そしてプロセス間のI/Oコストを完全に支配して初めて、エンタープライズの現場で耐えうる堅牢なツールが完成する。
今回紹介したアーキテクチャをベースに、自社の要件に応じたフィルタリングやカスタムフィールドの取得ロジックを拡張してほしい。限界を超えた自動化の果実を手にするのは、いつの時代も細部に神を宿したエンジニアだけである。
