【VBAリファレンス】VBA100本ノック86本目攻略 全シートの総当たりマトリックスを自動生成する実務テクニック

概要

Excel VBAのスキルを磨く上で欠かせない「VBA100本ノック」。その中でも、実務で頻繁に遭遇する「データ集計」や「一覧作成」のロジックが詰まっているのが86本目の「全シートの総当たり表作成」です。この課題の目的は、ブック内に存在するすべてのシート名を抽出し、それらを縦横の軸に配置したマトリックス（行列）を自動作成することです。一見すると単純なループ処理に見えますが、シート数が増減しても耐えうる堅牢なコードを書くためには、オブジェクトの操作、配列の活用、そして動的な範囲指定といったVBAの基礎体力が試されます。本記事では、この課題を題材に、単に動くコードを書くレベルから、保守性を考慮したプロフェッショナルな実装手法までを徹底的に解説します。

詳細解説

総当たり表を作成する際、最も重要なのは「シート名の取得」と「出力先の制御」です。通常、この作業を人力で行うと、シートの追加や削除のたびに表を修正する必要があり、ミスが多発します。VBAで自動化する場合、以下の3つのステップを意識してください。

第一に、シートのループ処理です。Worksheetsコレクションを順番に走査し、その名前を取得します。ここで重要なのは、隠しシートや特定のテンプレートシートを除外する「条件分岐」を組み込むことです。実務では「計算用シート」や「マスタシート」など、表に出したくないシートが混在することが多いため、これらをフィルタリングするロジックは必須となります。

第二に、マトリックス構造の構築です。縦軸（A列）と横軸（1行目）にシート名を配置します。ここで、二重ループ（Nested Loop）を使用します。外側のループで縦軸を、内側のループで横軸を決定することで、N×Nのグリッドを作成します。

第三に、範囲のクリアと再描画です。一度作成した表を再実行する際、古いデータが残っていると誤解を招きます。必ず出力先範囲をクリアする処理を冒頭に置くことで、繰り返し実行可能なプロシージャに仕上げるのがプロの流儀です。

サンプルコード

以下に、実務でも即座に応用可能な整理されたコードを提示します。

Sub CreateSheetMatrix()
    Dim ws As Worksheet
    Dim targetSheet As Worksheet
    Dim sheetCount As Long
    Dim i As Long, j As Long
    
    ' 出力用シートの設定（新規作成または既存のクリア）
    On Error Resume Next
    Set targetSheet = ThisWorkbook.Worksheets("総当たり表")
    If targetSheet Is Nothing Then
        Set targetSheet = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
        targetSheet.Name = "総当たり表"
    Else
        targetSheet.Cells.Clear
    End If
    On Error GoTo 0
    
    ' シートカウントの取得（出力用シート自体は除外）
    sheetCount = ThisWorkbook.Worksheets.Count - 1
    If sheetCount <= 0 Then Exit Sub
    
    ' 見出しの作成とシート名の入力
    Dim k As Long
    k = 1
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> targetSheet.Name Then
            ' 縦軸
            targetSheet.Cells(k + 1, 1).Value = ws.Name
            ' 横軸
            targetSheet.Cells(1, k + 1).Value = ws.Name
            k = k + 1
        End If
    Next ws
    
    ' 表の装飾（任意）
    With targetSheet.Range(targetSheet.Cells(1, 1), targetSheet.Cells(sheetCount + 1, sheetCount + 1))
        .Borders.LineStyle = xlContinuous
        .HorizontalAlignment = xlCenter
        .EntireColumn.AutoFit
    End With
    
    MsgBox "総当たり表の作成が完了しました。", vbInformation
End Sub

実務アドバイス

このコードを実務で運用する際、さらなるブラッシュアップを行うためのポイントを3つ伝授します。

1. シートの並び順の固定：シートの並び順は、ユーザーがドラッグ＆ドロップで自由に変更可能です。もし特定の順序で表を作成したい場合は、シート名でソートする機能（配列に格納してバブルソートなど）を追加すると、より利便性が高まります。
2. リンクの付与：マトリックス内に「HYPERLINK関数」を仕込む、あるいはVBAでハイパーリンクを設定することで、セルをクリックした瞬間に該当シートへ飛ぶように設定できます。これは大規模なブックを管理する際、非常に重宝される機能です。
3. エラーハンドリングの強化：シート名に特殊文字が含まれていたり、シート数がExcelの列数制限（16,384列）を超えようとした場合のバリデーション（入力チェック）を加えてください。実務において、予期せぬエラーでマクロが停止するのは避けなければなりません。

まとめ

VBA100本ノック86本目の「全シートの総当たり表作成」は、単なる表作成の練習を超えて、オブジェクト指向的なシート管理や、動的なデータ構造の構築方法を学ぶ絶好の機会です。今回紹介したループ処理の基本構造と、出力先をクリーンに保つ設計思想をマスターすれば、他のデータ集計タスクにも応用が効くはずです。VBAは「書ける」だけでなく「メンテナンスし続けられる」ことが重要です。ぜひこのコードをベースに、ご自身の業務環境に合わせてカスタマイズを繰り返してみてください。反復練習こそが、ベテランへの唯一の近道です。