Option Explicit
 
Private lNameRow As Long
 
Sub GetMacros()
  ThisWorkbook.Worksheets(1).Cells.ClearContents
  ThisWorkbook.Worksheets(1).Cells(1, 1) = "MakroName"
  ThisWorkbook.Worksheets(1).Cells(1, 2) = "MakroTyp"
  ThisWorkbook.Worksheets(1).Cells(1, 3) = "Parameter"
  ThisWorkbook.Worksheets(1).Cells(1, 4) = "Rückgabetyp"
 
  lNameRow = 2
 
  ReadMacroName ThisWorkbook.Name
End Sub
 
Sub ReadMacroName(ByVal sFile As String)
  On Error GoTo ShowError
 
  Dim lRows As Long, sCode As String, lModules As Long, x As Long, i As Long
  Dim P As Long, PName As Long
  Dim sName As String, sType As String, sArg As String, sRetType As String
 
  With Workbooks(sFile).VBProject.VBE
    lModules = .CodePanes.Count
    If lModules > 0 Then
      For x = 1 To lModules
        lRows = .CodePanes(x).CodeModule.CountOfLines
        If lRows > 0 Then
          For i = 1 To lRows
            sCode = Trim(.CodePanes(x).CodeModule.Lines(i, 1))
 
            PName = 0
 
            If (UCase(Left(sCode, 4)) = "SUB ") Then
              PName = 5
            ElseIf (UCase(Left(sCode, 11)) = "PUBLIC SUB ") Then
              PName = 11
            ElseIf (UCase(Left(sCode, 9)) = "FUNCTION ") Then
              PName = 10
            ElseIf (UCase(Left(sCode, 16)) = "PUBLIC FUNCTION ") Then
              PName = 17
            End If
 
            If PName > 0 Then
              P = InStr(1, sCode, "(")
 
              sName = Trim(Mid(sCode, PName, P - PName))
              sType = Trim(Left(sCode, PName - 1))
              sArg = Trim(Mid(sCode, P, Len(sCode)))
 
              P = InStr(1, sArg, ")")
              sRetType = Trim(Mid(sArg, P + 1, Len(sArg)))
              sArg = Trim(Mid(sArg, 2, P - 2))
 
              If sArg = "" Then sArg = "-"
              If sRetType = "" Then
                If UCase(Right(sType, 8)) = "FUNCTION" Then
                  sRetType = "- [As Variant]"
                Else
                  sRetType = "-"
                End If
              End If
 
              ThisWorkbook.Worksheets(1).Cells(lNameRow, 1) = sName
              ThisWorkbook.Worksheets(1).Cells(lNameRow, 2) = sType
              ThisWorkbook.Worksheets(1).Cells(lNameRow, 3) = sArg
              ThisWorkbook.Worksheets(1).Cells(lNameRow, 4) = sRetType
 
              lNameRow = lNameRow + 1
            End If
          Next i
        End If
      Next x
    End If
  End With
 
  Exit Sub
 
ShowError:
  MsgBox "Fehler!" & vbNewLine & Err.Description & vbNewLine & vbNewLine & _
    "Sie haben wahrscheinlich die VBA-Projekt-Sicherheitseinstellungen nicht" & _
    "korrekt eingestellt!" & vbNewLine & vbNewLine & _
    "Menü" & vbNewLine & _
    "--> Extras/Makro/Sicherheit" & vbNewLine & vbNewLine & _
    "Registerseite" & vbNewLine & _
    "--> Vertrauenswürdige Quellen" & vbNewLine & vbNewLine & _
    "Option aktivieren" & vbNewLine & _
    "--> Zugriff auf Visual Basic Projekt vertrauen", vbExclamation
End Sub

Viel Spass,
R@lf