ListAllMacros

' ListAllMacros
' -----------------
' Macro Name: ListAllMacros
' Description: ブック内のすべてのマクロ（プロシージャ）を一覧表示する
' Parameters: なし
' Returns: なし
' Usage: 実行するとこのブックに保存されているすべてのマクロ一覧が出力される
' -----------------

Sub ListAllMacros()
    Dim vbComp As Object
    Dim proc As Variant
    Dim ws As Worksheet
    Dim outputRow As Long
    Dim lastRow As Long
    Dim outStr As String
    Dim procType As String
    Dim procCount As Long

    On Error GoTo ErrorHandler

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 1 Then lastRow = 1

    outputRow = lastRow + 2

    ws.Cells(outputRow, 1).Value = "マクロ一覧"
    ws.Cells(outputRow, 1).Font.Bold = True
    ws.Cells(outputRow, 1).Font.Size = 14
    outputRow = outputRow + 1

    procCount = 0

    For Each vbComp In ActiveWorkbook.VBProject.VBComponents
        If vbComp.Type = 100 Then
            Dim modCode As Object
            Set modCode = vbComp.CodeModule
            Dim startLine As Long
            startLine = 1
            Do While startLine < modCode.CountOfLines
                On Error Resume Next
                Dim procName As String
                procName = modCode.ProcOfLine(startLine, 0)
                On Error GoTo ErrorHandler

                If procName <> "" Then
                    Dim procKind As Long
                    procKind = modCode.ProcKindOfLine(startLine, 0)
                    Select Case procKind
                        Case 1: procType = "Sub"
                        Case 2: procType = "Function"
                        Case 3: procType = "Property Get"
                        Case 4: procType = "Property Let"
                        Case 5: procType = "Property Set"
                        Case Else: procType = "不明"
                    End Select

                    If procCount = 0 Then
                        ws.Cells(outputRow, 1).Value = "モジュール"
                        ws.Cells(outputRow, 2).Value = "種類"
                        ws.Cells(outputRow, 3).Value = "マクロ名"
                        ws.Cells(outputRow, 1).Font.Bold = True
                        ws.Cells(outputRow, 2).Font.Bold = True
                        ws.Cells(outputRow, 3).Font.Bold = True
                        ws.Range(ws.Cells(outputRow, 1), ws.Cells(outputRow, 3)).Interior.Color = RGB(200, 200, 200)
                        outputRow = outputRow + 1
                    End If

                    ws.Cells(outputRow, 1).Value = vbComp.Name
                    ws.Cells(outputRow, 2).Value = procType
                    ws.Cells(outputRow, 3).Value = procName
                    outputRow = outputRow + 1
                    procCount = procCount + 1

                    startLine = modCode.ProcStartLine(procName, 0) + modCode.ProcCountLines(procName, 0)
                Else
                    startLine = startLine + 1
                End If
            Loop
        End If
    Next vbComp

    If procCount = 0 Then
        ws.Cells(outputRow, 1).Value = "マクロは見つかりませんでした"
    Else
        ws.Cells(outputRow, 1).Value = "合計: " & procCount & " 件"
        ws.Cells(outputRow, 1).Font.Bold = True
    End If

    ws.Columns("A:C").AutoFit
    MsgBox procCount & " 件のマクロが見つかりました。", vbInformation
    Exit Sub

ErrorHandler:
    If Err.Number = 9 Then
        MsgBox "この操作には VBA プロジェクトへのアクセス許可が必要です。" & vbCrLf & "ツール > 参照設定で『Microsoft Visual Basic for Applications Extensibility』を有効にしてください。", vbCritical
    Else
        MsgBox "エラーが発生しました: " & Err.Description, vbCritical
    End If
End Sub