It still amazes me how Microsoft can develop these complex applications but seems to overlook some simple functions that they should included within them to aid developers… But then, as demonstrated with the release of Office 2007 and 2010, Microsoft is not interested in the developer, they are only interested in the end-user’s opinion. Not productivity (that went down, about 30-40% drop in efficiency, the tubes with their change of format)! So all that matters is looks, the feel – very superficial (rant over)!!!

This will be the first in a series of procedure that I will be posting in the coming months in which I hope to demonstrate how you can use the ‘Microsoft Visual Basic for Application Extensibility’ library in conjunction with the power of VBA to learn more, control more, manipulate more the VBE.

In this first post, I simply wanted to create a simple procedure that would give me a breakdown of my Access project. I wanted to return a listing of procedure per module with a line count. As you can see, the ‘Microsoft Visual Basic for Application Extensibility’ enable us to perform this task with ease with little code. Heck, half of the code below is to write to the generated text file!
 

'---------------------------------------------------------------------------------------
' Procedure : GetVBEDeatils
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Goes throught the VBE and creates a text file which give a brief listing
'             of the procedures within each module and a line count for each
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Requirements: reference to the Microsoft Visual Basic for Application Extensibility
'               library.
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-06-04              Initial Release
' 2         2017-09-15              Code tweaks thanks to comments from Rob Hoffman
' 3         2018-10-15              Updated Copyright
'---------------------------------------------------------------------------------------
Function GetVBEDeatils()
10        On Error GoTo Error_Handler
          Dim vbProj                As VBIDE.VBProject
          Dim vbComp                As VBIDE.VBComponent
          Dim vbMod                 As VBIDE.CodeModule
          Dim pk                    As VBIDE.vbext_ProcKind
          Dim sProcName             As String
          Dim strFile               As String
          Dim iCounter              As Long
          Dim FileNumber            As Integer
          Dim bFileClosed           As Boolean
          Const vbNormalFocus = 1

          'Where do you want the text file created
20        strFile = "C:\Users\Daniel\Desktop\VBEDetails.txt"
30        If Len(Dir(strFile)) > 0 Then Kill strFile
40        FileNumber = FreeFile                           'Get unused file number.
50        Open strFile For Append As #FileNumber          'Create file name.
60        Print #FileNumber, "Database: " & Application.CurrentProject.Name
70        Print #FileNumber, "Database Path: " & Application.CurrentProject.Path
80        Print #FileNumber, String(80, "*")
90        Print #FileNumber, String(80, "*")
100       Print #FileNumber, ""

110       For Each vbProj In Application.VBE.VBProjects   'Loop through each project
120           Print #FileNumber, "VBA Project Name: " & vbProj.Name
130           For Each vbComp In vbProj.VBComponents      'Loop through each module
140               Set vbMod = vbComp.CodeModule
150               Print #FileNumber, "   " & vbComp.Name & " :: " & _
                                     vbMod.CountOfLines & " total lines"
160               Print #FileNumber, "   " & String(80, "*")
170               iCounter = 1
180               Do While iCounter < vbMod.CountOfLines  'Loop through each procedure
190                   sProcName = vbMod.ProcOfLine(iCounter, pk)
200                   If sProcName <> "" Then
210                       Print #FileNumber, "      " & sProcName & " :: " & _
                                             vbMod.ProcCountLines(sProcName, pk) & " lines"
220                       iCounter = iCounter + vbMod.ProcCountLines(sProcName, pk)
230                   Else
240                       iCounter = iCounter + 1
250                   End If
260               Loop
270               Print #FileNumber, ""
280           Next vbComp
290       Next vbProj

300       Close #FileNumber                               'Close file.
310       bFileClosed = True

          'Open the generated text file
320       Shell "cmd /c """ & strFile & """", vbNormalFocus
          'In Access you could also use the following line instead of the previous one.
          'Application.FollowHyperlink strFile

Error_Handler_Exit:
330       On Error Resume Next
340       If bFileClosed = False Then Close #FileNumber   'Close file.
350       If Not vbMod Is Nothing Then Set vbMod = Nothing
360       If Not vbComp Is Nothing Then Set vbComp = Nothing
370       If Not vbProj Is Nothing Then Set vbProj = Nothing
380       Exit Function

Error_Handler:
390       MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: GetVBEDeatils" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occurred!"
400       Resume Error_Handler_Exit
End Function