Visual Basic for Applications/获取 VBA 代码字符串

摘要

VBA 编辑器

获取整个项目字符串

下面的代码模块是为 Excel 编写的，但很容易适应 Word 和其他 MS Office 应用程序。它将整个代码项目的整个代码项目变成一个字符串，用于运行它的同一个工作簿。过去发现它很有用，当一个长字符串需要测试例如，字符频率代码。只需稍作修改，就可以返回各个模块文本和其他细节。

Sub TestGetVBAProjString()
  'run this
  'assumes VBA code is not locked
  
  Dim sStr As String, nComps As Integer
  Dim vA As Variant, nTLines As Long
  
  'get whole string
  sStr = GetVBAProjString
  
  'show start of project string
  MsgBox sStr
End Sub

Function GetVBAProjString() As String
  'gets ThisWorkbook's whole VBA project string
  'Set reference to Microsoft VBA Extensibility 5.5
  
  Dim VBProj As VBIDE.VBProject, VBComp As VBIDE.VBComponent
  Dim VBMod As VBIDE.CodeModule, sMod As String, sProj As String
  Dim nLines As Long
  
  'get ref to ThisWorkbook project
  Set VBProj = ThisWorkbook.VBProject
    
  'loop through VBComponents collection
  For Each VBComp In VBProj.VBComponents
    Set VBMod = VBComp.CodeModule
    nLines = VBMod.CountOfLines
      If nLines <> 0 Then
        sMod = VBMod.Lines(1, nLines)
        sProj = sProj & vbCrLf & _
            UCase(Chr(39) & VBComp.Name) & _
             vbCrLf & vbCrLf & sMod
      Else 'just accum name of empty component
        sProj = sProj & vbCrLf & _
            UCase(Chr(39) & VBComp.Name) & _
             vbCrLf & vbCrLf
      End If
  Next VBComp
  
  GetVBAProjString = sProj
  Set VBProj = Nothing: Set VBComp = Nothing
  Set VBMod = Nothing
  
End Function

外部链接

在 VBA 编辑器中编程 : Pearson 为 VBA 编辑器提供的综合页面。