Visual Basic for Applications/Get the VBA Code String

Getting the Whole Project String

The code module below is written for Excel but is easily adapted for Word and other MS Office applications. It makes a single string out of the entire code project for the same workbook in which it is run. In the past it has been found useful when a long string is needed to test say, character frequency code. With only slight modification the individual module text can be returned, and other details.

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

External Links

• Programming in the VBA Editor  : A comprehensive page by Pearson for the VBA editor.