Visual Basic for Applications/Get the VBA Code String

Summary The VBA Editor 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.