VBA中列出模块中所有过程

分类:代码, 博客 标签:

Excel中使用VBA实现将模块中的所有过程列出来,要调用这一过程首先要引用VBA Extensibililty library,具体代码如下:

'================================
' VBA中列出模块中所有过程
'
' http://www.cnhup.com
'================================
Sub ListProcedures()
  Dim VBProj As VBIDE.VBProject
  Dim VBComp As VBIDE.VBComponent
  Dim CodeMod As VBIDE.CodeModule
  Dim LineNum As Long
  Dim NumLines As Long
  Dim WS As Worksheet
  Dim Rng As Range
  Dim ProcName As String
  Dim ProcKind As VBIDE.vbext_ProcKind

  Set VBProj = ActiveWorkbook.VBProject
  Set VBComp = VBProj.VBComponents("Module1")
  Set CodeMod = VBComp.CodeModule

  Set WS = ActiveWorkbook.Worksheets("Sheet1")
  Set Rng = WS.Range("A1")
  With CodeMod
    LineNum = .CountOfDeclarationLines + 1
    Do Until LineNum >= .CountOfLines
      ProcName = .ProcOfLine(LineNum, ProcKind)
      Rng.Value = ProcName
      Rng(1, 2).Value = ProcKindString(ProcKind)
      LineNum = .ProcStartLine(ProcName, ProcKind) + _
          .ProcCountLines(ProcName, ProcKind) + 1
      Set Rng = Rng(2, 1)
    Loop
  End With

End Sub

Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String
  Select Case ProcKind
    Case vbext_pk_Get
      ProcKindString = "Property Get"
    Case vbext_pk_Let
      ProcKindString = "Property Let"
    Case vbext_pk_Set
      ProcKindString = "Property Set"
    Case vbext_pk_Proc
      ProcKindString = "Sub Or Function"
    Case Else
      ProcKindString = "Unknown Type: " & CStr(ProcKind)
  End Select
End Function


分类:代码, 博客 标签:

发表评论

You must be logged in to post a comment.