Excel VBAで数式セルと式の結果を一覧表示しよう

ExcelVBAマクロ

2023.04.13

Excel VBAで数式セルを一覧表示しよう

Excelのシートには、たくさんのセルがあり、複雑な数式が入っていることがよくあります。そんな時、数式セルの位置や内容を一覧で確認できると便利ですよね。今回は、Excel VBAを使って、ブック内に存在する「数式セルの位置、その数式、その結果」をリスト化して新しいワークシートに表示する方法を紹介します。

このVBAコードの機能と使い方
注意点
まとめ

このVBAコードの機能と使い方

このVBAコードは、ブック内のすべてのワークシートを対象に、数式が入力されているセルを検索し、そのセルのワークシート名、セルアドレス、数式、数式の結果を新しいワークシート「Formula List」に一覧表示します。

使い方は以下の通りです。

1. ExcelでVBAエディターを開きます（Alt + F11キー）。
2. 「挿入」メニューから「標準モジュール」を選択し、新しいモジュールを作成します。
3. 作成したモジュールに以下のVBAコードを貼り付けます。

Sub ListFormulas()
    Dim ws As Worksheet, newWs As Worksheet
    Dim rCell As Range, rUsed As Range
    Dim i As Long

    ' 「Formula List」ワークシートが既に存在する場合、エラーメッセージを表示し、マクロの実行を中止する
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Formula List")
    On Error GoTo 0
    
    If Not ws Is Nothing Then
        MsgBox "既存の「Formula List」を削除するか別名に変更してください。", vbExclamation, "エラー"
        Exit Sub
    End If
    
    ' 新しいワークシートを作成
    Set newWs = ThisWorkbook.Worksheets.Add
    newWs.Name = "Formula List"
    
    ' ヘッダーを設定
    newWs.Cells(1, 1).Value = "ワークシート"
    newWs.Cells(1, 2).Value = "セルアドレス"
    newWs.Cells(1, 3).Value = "数式"
    newWs.Cells(1, 4).Value = "結果"
    
    i = 2
    
    ' すべてのワークシートをループ
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> newWs.Name Then
            Set rUsed = ws.UsedRange
            
            ' 数式があるセルをループ
            For Each rCell In rUsed.Cells
                If rCell.HasFormula Then
                    ' 数式があるセルの情報を新しいワークシートに書き込む
                    newWs.Cells(i, 1).Value = ws.Name
                    newWs.Cells(i, 2).Value = rCell.Address
                    newWs.Cells(i, 3).Value = "'" & rCell.Formula
                    newWs.Cells(i, 4).Value = rCell.Value
                    
                    i = i + 1
                End If
            Next rCell
        End If
    Next ws
End Sub