Visual Basic for Applications/獲取 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 編輯器提供的綜合頁面。