トップ　>　汎用コード　>　数式情報を一覧化

'---------------------------------------------------------------------------
' アクティブブックの全ワークシートの数式情報を調べ新規ブックに一覧表示
'---------------------------------------------------------------------------
Sub ExampleOfUse_GetInformationAboutFormulas()

    Application.ScreenUpdating = False
    
    'アクティブブックを変数に設定
    Dim actWB As Workbook
    Set actWB = ActiveWorkbook
    
    '新規ブックを追加
    With Workbooks.Add.Worksheets(1)
        '見出し作成・列表示形式設定
        With .Range("A1:E1")
            .Value = Array("シート名", "セルアドレス", "数式", "値", "書式")
            .Interior.ThemeColor = xlThemeColorAccent5
            .Interior.TintAndShade = 0.799981688894314
            .EntireColumn.NumberFormatLocal = "@"
        End With
        
        'アクティブブックの各シートの数式情報を取得
        Dim Sh        As Worksheet
        Dim varInfo   As Variant
        Dim rngTarget As Range
        For Each Sh In actWB.Worksheets
            '起点セル設定
            Set rngTarget = .Cells(Rows.Count, "A").End(xlUp).Offset(1)
            '数式情報を取得
            varInfo = GetInformationAboutFormulas(Sh)
            If IsArray(varInfo) Then
                '数式情報を取得できれば情報を起点セルから設定
                rngTarget.Resize(UBound(varInfo), 5) = varInfo
            Else
                'エラーの場合は内容を起点セルに表示
                rngTarget.Value = varInfo
            End If
        Next
        
        '罫線
        .UsedRange.Borders.LineStyle = xlContinuous
        
        '列幅調整
        .Columns("B:B").AutoFit
        .Columns("C:C").ColumnWidth = 60
        .Columns("D:D").ColumnWidth = 30
        .Columns("E:E").AutoFit
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "結果を出力しました", vbInformation
End Sub

'----------------------------------------------------------------
' 指定したシートの数式に関する情報を2次元配列として取得する関数
'----------------------------------------------------------------
'［引数］
'   Sh  ：対象のワークシート
'［戻り値］
'   成功：2次元配列
'            行方向：取得セル件数
'            列方向：シート名,セルアドレス, 数式, 値, 書式
'   失敗：文字列(エラーメッセージ)
'［作成日］
'   2023/05/26
' https://excel.syogyoumujou.com/vba/getinfoaboutformulas.html
'----------------------------------------------------------------
Function GetInformationAboutFormulas(ByRef Sh As Worksheet) As Variant
    'シート保護確認
    If Sh.ProtectContents Then
        GetInformationAboutFormulas = "シート [" & Sh.Name & "］は保護されています"
        Exit Function
    End If

    '数式セルの取得
    Dim rngFormula As Range
    On Error Resume Next
    Set rngFormula = Sh.Cells.SpecialCells(xlCellTypeFormulas, 23)
    Err.Clear
    On Error GoTo 0
    If rngFormula Is Nothing Then
        GetInformationAboutFormulas = "シート［" & Sh.Name & "］に数式のセルはありません"
        Exit Function
    End If

    '数式情報の取得
    On Error GoTo ERROR_HANDLER
    Dim c   As Long
    Dim rng As Range
    ReDim varInfo(1 To rngFormula.Cells.Count, 1 To 5)
    For Each rng In rngFormula
        If rng.Formula <> "" Then '結合セルは最左上のセル以外数式は空欄となる
            c = c + 1
            varInfo(c, 1) = rng.Parent.Name       'シート名
            varInfo(c, 2) = rng.Address(0, 0)     'セルアドレス
            varInfo(c, 3) = rng.Formula           '数式
            varInfo(c, 4) = rng.Value             '値
            varInfo(c, 5) = rng.NumberFormatLocal '書式
        End If
    Next
    On Error GoTo 0
    GetInformationAboutFormulas = varInfo
Exit Function
ERROR_HANDLER:
    GetInformationAboutFormulas = "シート［" & Sh.Name & "］ 想定外のエラー"
End Function