【実務・中級編】大規模プロジェクトにおけるリソース情報の読み込みを高速化する「Dictionary」活用術 – Project VBA解析バイブル

大規模プロジェクトのリソース管理を極限まで加速する：Dictionaryを活用した高速化と堅牢設計の全技術

プロジェクト管理において、リソース（人員や設備）の登録、割り当て、コスト管理、そして稼働率の調整は、プロジェクトの成否を握る心臓部だ。特に数千行、数万行に及ぶ大規模なWBSやリソースプールを扱うとき、多くの開発者が壁にぶつかる。

「なぜか処理が数分もフリーズする」
「Excelシートへのセルアクセスを繰り返していたら、PCのファンが唸りを上げ出した」

もしあなたが、`Range.Find`や`WorksheetFunction.VLookup`をループの中で毎度回すようなコードを書いているなら、今すぐその手を止めてほしい。それはVBAのパフォーマンスを殺す最も確実なアンチパターンだ。

今回は、Project VBA（およびExcel VBA）の限界を突破し、メモリ上でミリ秒単位の検索・更新を実現する`Scripting.Dictionary`を用いた極限の高速化アーキテクチャを伝授する。実務の現場でそのまま稼働する、堅牢かつ洗練されたプロダクションコードを共に出発点としよう。

—

1. なぜ「セルへの頻繁なアクセス」は悪なのか？

VBAの実行速度において、最大のボトルネックは「VBAとExcelワークシート間のコンテキストスイッチ（往復）」にある。

ループの中で `Cells(i, j).Value` を読み書きするたびに、COMを介した重いプロセス間通信が発生する。これを何万回も繰り返せば、処理が遅くなるのは物理的必然だ。

解決策：メモリ上へのデータキャッシュ（Dictionary）

データ構造の基本に立ち返ろう。リソースIDや担当者名を「キー（Key）」とし、その人の単価、所属、稼働率、コストといった関連データを「アイテム（Item）」として一度メモリ上のDictionaryにすべて読み込む。

• ディスク（またはシート）からの読み込み: 1回のみ（一括配列取得）
• 検索・更新: メモリ上（Dictionary）で超高速に完結
• 書き込み: 処理完了後に一括出力

この「インメモリ・データベース」の思想を取り入れるだけで、数分かかっていた処理が1秒未満に劇的な進化を遂げる。

—

2. 堅牢な設計：早期バインディングとエラーハンドリング

Dictionaryを扱う際、初心者が陥りがちな罠が「遅延バインディング（`CreateObject(“Scripting.Dictionary”)`）」の多用や、キー重複によるクラッシュ、大文字小文字の差異によるバグだ。

プロダクションコードとして耐えうるシステムを作るためには、以下の原則を厳守せよ。

1. 早期バインディングの採用:
`Microsoft Scripting Runtime` への参照設定を行い、コード補完（IntelliSense）の恩恵を受けると共に、実行時オーバーヘッドを排除する。
2. 大文字小文字の区別（CompareMode）の制御:
リソースIDやユーザー名において、”EMP001″ と “emp001” を別人と判定しては大事故につながる。明示的に `TextCompare`（大文字小文字を区別しない）を指定する。
3. カスタムクラス（UDT）によるItemの構造化:
単一の値だけでなく、コストや稼働率など複数のプロパティを保持させるため、ユーザー定義型（UDT）または専用の構造体をDictionaryのItemに格納する。

—

3. 【プロダクションコード】大規模リソース管理エンジン

以下のコードは、リソースマスタからデータを高速に読み込み、タスクごとの割り当てとコスト計算をメモリ上で完結させ、一括出力するモジュールだ。そのままプロジェクトに組み込んで即戦力として利用してほしい。

Option Explicit

‘ =================================================================================
‘ módulo: ResourceEngine
‘ 概要: メモリ上でのDictionary活用による大規模リソース管理・コスト計算エンジン
‘ 前提条件: 参照設定に「Microsoft Scripting Runtime」を追加すること
‘ =================================================================================

‘ リソース情報を格納する構造体（UDT）
Public Type ResourceInfo
ResourceID As String
ResourceName As String
Department As String
UnitCost As Double ‘ 時間単価
MaxCapacity As Double ‘ 最大稼働率 (例: 1.0 = 100%)
End Type

Public Sub ExecuteResourceOptimization()
Dim wsMaster As Worksheet
Dim wsTasks As Worksheet
Dim startTime As Double
startTime = Timer

On Error GoTo ErrorHandler

‘ 画面描画と自動計算を停止し、極限までパフォーマンスを引き上げる
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

‘ ワークシートの定義（実際のシート名に合わせて変更してください）
Set wsMaster = ThisWorkbook.Sheets(“ResourceMaster”)
Set wsTasks = ThisWorkbook.Sheets(“TaskAllocations”)

‘ —————————————————————————–
StrP1: ‘ 1. リソースマスタをDictionaryへ一括キャッシュ
‘ —————————————————————————–
Dim dictResources As Scripting.Dictionary
Set dictResources = LoadResourceMasterToDictionary(wsMaster)

If dictResources Is Nothing Then Err.Raise 9999, , “リソースマスタの読み込みに失敗しました。”

‘ —————————————————————————–
StrP2: ‘ 2. タスク割り当てデータの読み込みとインメモリ計算
‘ —————————————————————————–
Call ProcessTaskAllocations(wsTasks, dictResources)

‘ 処理時間計測の終了
Dim elapsedTime As Double
elapsedTime = Timer – startTime

‘ クリーンアップ
Call ResetApplicationState

MsgBox “リソースの割り当ておよびコスト計算が完了しました。” & vbCrLf & _
“処理時間: ” & Format(elapsedTime, “0.00”) & ” 秒”, vbInformation, “最適化完了”
Exit Sub

ErrorHandler:
Call ResetApplicationState
MsgBox “致命的なエラーが発生しました: ” & Err.Description, vbCritical, “システムエラー”
End Sub

‘ ——————————————————————————–
‘ リソースマスタを配列経由でDictionaryに高速ロードする関数
‘ ——————————————————————————–
Private Function LoadResourceMasterToDictionary(ws As Worksheet) As Scripting.Dictionary
Dim lastRow As Long
Dim rawData As Variant
Dim i As Long
Dim dict As Scripting.Dictionary
Dim res As ResourceInfo

lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
If lastRow < 2 Then Set LoadResourceMasterToDictionary = Nothing Exit Function End If ' データを一括で二次元配列に格納（セルへのアクセスはここだけ！） rawData = ws.Range("A2:E" & lastRow).Value ' Dictionaryの初期化（大文字小文字を区別しない設定） Set dict = New Scripting.Dictionary dict.CompareMode = TextCompare For i = 1 To UBound(rawData, 1) ' キーが空の場合はスキップ If rawData(i, 1) <> “” Then
res.ResourceID = CStr(rawData(i, 1))
res.ResourceName = CStr(rawData(i, 2))
res.Department = CStr(rawData(i, 3))
res.UnitCost = CDbl(rawData(i, 4))
res.MaxCapacity = CDbl(rawData(i, 5))

‘ 重複チェックを行いつつDictionaryに追加
If Not dict.Exists(res.ResourceID) Then
dict.Add res.ResourceID, res
End If
End If
Next i

Set LoadResourceMasterToDictionary = dict
End Function

‘ ——————————————————————————–
‘ タスクデータを処理し、コストを計算して一括書き込みするメインロジック
‘ ——————————————————————————–
Private Sub ProcessTaskAllocations(ws As Worksheet, dictRes As Scripting.Dictionary)
Dim lastRow As Long
Dim taskData As Variant
Dim i As Long
Dim resID As String
Dim hours As Double
Dim currentRes As ResourceInfo

lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
If lastRow < 2 Then Exit Sub ' タスク表の構造: A列=タスクID, B列=リソースID(FK), C列=予定工数(H), D列=計算コスト(出力), E列=警告(出力) taskData = ws.Range("A2:E" & lastRow).Value For i = 1 To UBound(taskData, 1) resID = CStr(taskData(i, 2)) If dictRes.Exists(resID) then ' DictionaryからO(1)の速度でリソース情報を取得 currentRes = dictRes(resID) hours = CDbl(taskData(i, 3)) ' コスト計算（工数 × 時間単価） taskData(i, 4) = hours currentRes.UnitCost ' 簡易的な稼働・容量オーバーチェック（例） If hours > (currentRes.MaxCapacity 40) Then ‘ 週40時間基準の簡易判定
taskData(i, 5) = “【警告】容量オーバー”
Else
taskData(i, 5) = “正常”
End If
Else
taskData(i, 4) = 0
taskData(i, 5) = “【エラー】リソース未登録”
End If
Next i

‘ 計算結果をワークシートへ一括書き込み（逆もまた然り、一括出力が鉄則）
ws.Range(“A2:E” & lastRow).Value = taskData
End Sub

‘ ——————————————————————————–
‘ アプリケーション環境の復元
‘ ——————————————————————————–
Private Sub ResetApplicationState()
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub

—

4. プロジェクトの現場で気をつけるべき実務上の罠

このアーキテクチャを導入するにあたり、現場のリーダーや開発者が知っておくべき「実践の勘所」を共有しよう。

① メモリリークとオブジェクトの解放

VBAのDictionaryはガベージコレクションの挙動が特殊な場合がある。処理の終了時には明示的に `Set dictResources = Nothing` とし、巨大なメモリ領域を速やかに開放する癖をつけよ。

② データ型の厳格な一致（バリアント型への配慮）

Excelシートから取得した配列（`Range.Value`）は、数値であってもVariant型として格納される。Dictionaryのキーにする際、セル上の `123`（数値）と文字列の `”123″` は別物として扱われる危険性がある。
そのため、今回のコードのように `CStr()` を使って明示的に型をキャスト・統一することが、未然にバグを防ぐ防壁となる。

③ データベース（SQL Server / Access）連携へのスケールアップ

もし将来的に、扱うデータがExcelの行数制限（104万行）を超える、あるいは複数人での同時書き込みが必要になった場合でも、この設計思想はそのまま活きる。
Excelシートから読み込んでいた部分を ADODB.Recordset や SQLクエリからの取得 に置き換えるだけで、Dictionaryをエンジンとした高速処理の構造そのものは一切変えずにシステムをスケールさせることが可能だ。

—

終わりに：VBAを「おもちゃ」から「エンタープライズ・ツール」へ

「VBAだから遅い」のではない。設計が非効率だから遅いのだ。

Dictionaryオブジェクトを武器に加えた瞬間から、あなたのVBAスクリプトは単なるマクロの域を超え、堅牢で高速なインメモリ・ビジネスロジックへと昇華する。
大規模プロジェクトの荒波を乗りこなす真のエンジニアとして、ぜひこの「Dictionary活用術」をあなたの開発現場の標準装備にしてほしい。