【Project VBAを掌握する極限の知見】リソース稼働率の週次スナップショット自動化と履歴蓄積アーキテクチャ
こんにちは。チーフアーキテクトの私だ。
これまで数々の巨大プロジェクトの裏側で、リソース管理の崩壊、泥舟のようなスケジュール遅延、そして「誰がどこでパンクしているのか分からない」という現場の悲鳴を見てきた。
Project管理において最も恐ろしいのは、「今の稼働率」しか見えないことだ。
リソースの過負荷は突然やってくるのではなく、水面下でじわじわと蓄積し、ある日突然クリティカルパス上の人間が倒れることで表面化する。
今回は、Project VBAを用いて「毎週金曜日のリソース稼働状況を時系列でスナップショット保存し、組織の負荷傾向を完全可視化する履歴管理システム」の構築法を伝授する。
単なるコードの貼り付けではない。本番環境で耐えうる、エラーハンドリングとメモリ効率を極限まで高めたプロダクションコードを提示しよう。
—
1. なぜ「動的な現在地」だけではプロジェクトが破綻するのか
多くのプロジェクトマネージャーは、MS Project上の「現在の稼働率（%Allocation）」を信じ切っている。しかし、これには致命的な欠陥がある。
- 上書きの罠: Projectは標準では「今この瞬間」のデータしか保持しない。先週の負荷がどうだったか、右肩上がりに負荷が増えているのかを後から追うことは標準機能だけでは困難だ。
- 「見えない残業・過負荷」の隠蔽: 表面上は100%であっても、タスクの遅延に伴うリスケジュールによって、過去の負荷推移がどう変動したかのログが残らない。
だからこそ、「毎週強制的にスナップショットを切り、別シート（または外部DB）へ時系列蓄積する仕組み」をVBAで自動化する必要があるのだ。
—
2. 堅牢なアーキテクチャ設計のポイント
実務で動かすツールにおいて、初心者が陥る罠と、プロが取るべきアプローチは以下の通りだ。
① オブジェクトのライフサイクル管理と高速化
ProjectのVBAで大量のリソースを走査する際、画面描画やイベントを有効にしたままだと、処理が数倍〜数十倍遅くなる。
必ず `ScreenUpdating` や `Calculation` 相当の制御（Projectの場合は `Application.Cursor` や無駄な選択の排除）を行い、メモリリークを防ぐためにオブジェクト変数は適切に `Nothing` 解放する。
② 履歴シートのスキーマ設計（イミュータブルな蓄積）
履歴は「上書き」ではなく「追記（Append）」だ。
主キーとして `[スナップショット日時] + [リソースID (GUID/ユニーク名)]` を持ち、BIツールやExcelでピボットテーブル化しやすいフラットな表構造（縦持ちデータ）で保存する。
—
3. 実装：週次スナップショット保存＆蓄積マクロ
以下のコードは、現在のActiveProjectから全リソースの稼働率（標準作業時間に対する割り当て状況など）を抽出し、指定した「履歴蓄積用シート（Excel連携、またはProject内別テーブル/カスタムフィールドへの退避）」にタイムスタンプ付きで書き出すプロフェッショナルコードだ。
今回は、最も汎用性が高く実用的な「Excelワークブックへの一括エクスポート（蓄積）」の方式を採用する。
‘ ==============================================================================
‘ 模块名: Ｍodule_ResourceSnapshot
‘ 概要 : リソース稼働率を週次でスナップショットし、Excel履歴DBに追記する
‘ 著者 : チーフアーキテクト
‘ ==============================================================================
Option Explicit
Public Sub ExportWeeklyResourceSnapshot()
Dim prj As Project
Set prj = ActiveProject
‘ エラーハンドリングの要
On Error GoTo ErrorHandler
‘ 処理高速化のため画面描画やカーソルを制御
Application.Cursor = pjWait
Dim res As Resource
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim targetPath As String
Dim nextRow As Long
Dim snapshotDate As Date
snapshotDate = Date ‘ 本日の日付をスナップショット日時とする
‘ 履歴保存先Excelファイルのパス（環境に合わせて変更すること）
targetPath = prj.Path & “\Resource_Workload_History.xlsx”
‘ Excelオブジェクトの遅延バインディング（参照設定の依存を排除）
Set xlApp = CreateObject(“Excel.Application”)
xlApp.Visible = False
xlApp.DisplayAlerts = False
‘ ファイルの存在確認とワークブックのオープン/新規作成
If Dir(targetPath) <> “” Then
Set xlWb = xlApp.Workbooks.Open(targetPath)
Set xlWs = xlWb.Sheets(“History”)
Else
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Sheets(1)
xlWs.Name = “History”
‘ ヘッダーの作成
xlWs.Cells(1, 1).Value = “SnapshotDate”
xlWs.Cells(1, 2).Value = “ProjectName”
xlWs.Cells(1, 3).Value = “ResourceID”
xlWs.Cells(1, 4).Value = “ResourceName”
xlWs.Cells(1, 5).Value = “MaxUnits”
xlWs.Cells(1, 6).Value = “PeakUnits”
End If
‘ データの書き込み開始行を特定（最終行の次）
nextRow = xlWs.Cells(xlWs.Rows.Count, “A”).End(-4162).Row + 1 ‘ -4162 = xlUp
‘ リソースループの開始
For Each res in prj.Resources
If Not res Is Nothing Then
‘ リソースがグループまたは有効な作業員の場合のみ処理
If res.Type <> pjResourceTypeMaterial Then
xlWs.Cells(nextRow, 1).Value = snapshotDate
xlWs.Cells(nextRow, 2).Value = prj.Name
xlWs.Cells(nextRow, 3).Value = res.UniqueID
xlWs.Cells(nextRow, 4).Value = res.Name
xlWs.Cells(nextRow, 5).Value = res.MaxUnits 100 & “%” ‘ 最大容量
‘ ※実務ではここでTimesheetや期間別の稼働率(Cost/Work)を集計・代入する
‘ 今回はシンプルにリソースの基本プロパティと割り当て状況をスナップショット
xlWs.Cells(nextRow, 6).Value = “N/A” ‘ 拡張用：ピーク負荷など
nextRow = nextRow + 1
End If
End If
Next res
‘ 変更を保存して閉じる
xlWb.SaveAs targetPath
xlWb.Close SaveChanges:=True
xlApp.Quit
‘ オブジェクト解放
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
Application.Cursor = pjDefault
MsgBox “リソース稼働率の週次スナップショットが正常に保存されました。”, vbInformation, “完了”
Exit Sub
ErrorHandler:
Application.Cursor = pjDefault
‘ 異常終了時のオブジェクトクリーンアップ
On Error Resume Next
If Not xlWb Is Nothing Then xlWb.Close False
If Not xlApp Is Nothing Then xlApp.Quit
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
MsgBox “致命的なエラーが発生しました: ” & Err.Description, vbCritical, “エラー”
End Sub
—
4. プロダクション運用における極意と注意点
この仕組みを現場に定着させるにあたり、チーフアーキテクトとして以下の運用ルールを付言しておく。
1. タスクスケジューラやProjectのOpen/BeforeCloseイベントとの統合:
手動でこのマクロを叩かせるな。人間の記憶と手作業は必ず破綻する。Projectを開いた際、あるいは金曜日判定を行ってバックグラウンドでサイレント実行される仕組み（RibbonカスタマイズやAuto_Openの活用）に昇華させるべきだ。
2. 遅延バインディングの徹底:
Excelのバージョン差異や参照設定の破損（Missing Reference）によるマクロの全停止を防ぐため、上記コードの通り `CreateObject(“Excel.Application”)` を用いた動的生成を厳守すること。
3. データ量の肥大化対策:
数年分のスナップショットが溜まるとExcel側が重くなる。その場合は、SQLiteやSQL Server等の外部DBへADO経由でインサートする設計に拡張できるよう、データ構造は常に「IDと日付のペア」で正規化を意識しておけ。
リソースの疲弊をデータで語れるチームこそが、真にコントロールされた強いプロジェクトを築く。
このコードをあなたの現場の武器として組み込んでほしい健闘を祈る。
