Prepare a List of Procedure Names from a Module
How to prepare a list of Procedure Names (Function or Sub-Routine Names) from a Standard Module or Class Module?
We have seen how to insert a cmdButton_Click() Event Procedure into a Form Module using the .InsertLines() method of the Module Object in the first Articlle and how to upload the same program from a Text File with the .AddFromFile() method of the Module Object in the second Article.
In this example, we will try to prepare a list of Procedures from a Standard Module and from a Class Module. Here, we will learn the usage of the following Properties of the Module Object:
With Modules(strModuleName): lng_BodyLines = .CountOfLines ‘ Total Number of lines of Code in the Module lng_LinesAtGobalArea = .CountOfDeclarationLines ‘Takes a count of lines in the Global declaration area str_ProcedureName = .ProcOfLine(LineNumber, NumberOfLines) ‘indexed list of Code lines with their Procedure names End with
The .ProcOfLine (stands for Procedure Name of the current line of VBA Code) is an indexed list and we must provide the index number of the Code line under process to check which procedure that the code line belongs to. The second index value is normally one, if you are inspecting a line by line basis. If you prefer to check two or more lines together then change this number accordingly.
The Pseudo Code of the VBA Program we are going to write is given below.
- Take Count of Code Lines of the Module, Standard Module or Class Module.
- Take Count of Global Declaration Lines.
- First Procedure Name = Count of Global declaration Lines + 1
- Array(0) = First Procedure Name. Saves the Function/Sub-Routine name into an Array.
- Scan through the remaining lines of code:
A) Check the .ProcOfLine property value for the Procedure Name of the current line of Code.
B) If the Procedure Name of the current line is same as of previous line then go to C)
Else save the current Code line’s Procedure Name in the next element of Array().
C) Move to the next Code line and if End-of-lines reached then go to D) else repeat from A)
D) Create a string with the Procedure Names from the saved Array.
E) Display the list of Procedures in a Message Box.
F) End of Program.
Now let us write our VBA Code for the above program.
Public Function ListOfProcs(ByVal strModuleName As String) '------------------------------------------------------ 'Courtesy : Microsoft Access '------------------------------------------------------ Dim mdl As Module Dim linesCount As Long, DeclLines As Long Dim strProcName As String, lngR As Long, intJ As Integer Dim str_ProcNames() As String, lngK As Long Dim strMsg As String Set mdl = Modules(strModuleName) 'Total Count of lines in the Module linesCount = mdl.CountOfLines 'Take the count of Global declaration lines DeclLines = mdl.CountOfDeclarationLines lngR = 1 'The first line below the declaration lines 'is the first procedure name in the Module strProcName = mdl.ProcOfLine(DeclLines + 1, lngR) 'Re-dimension the str_ProcNames() Array for a single element 'and save the procedure name in the Array. intJ = 0 ReDim Preserve str_ProcNames(intJ) str_ProcNames(intJ) = strProcName 'Determine procedure Name for each line after declaraction lines For lngK = DeclLines + 1 To linesCount 'compare current Code-line’s procedure name with earlier line’s name ‘if not matching then we have encountered a new procedure name If strProcName <> mdl.ProcOfLine(lngK, lngR) Then 'increment array index by one intJ = intJ + 1 'get the procedure name of the current program line strProcName = mdl.ProcOfLine(lngK, lngR) 'Redimension the array for a new element by 'preserving the data of earlier elements ReDim Preserve str_ProcNames(intJ) 'Save the procedure name in the array str_ProcNames(intJ) = strProcName End If Next lngK 'create the list of Procedure Names from Array to display strMsg = "Procedures in the Module: " & strModuleName & vbCr For intJ = 0 To UBound(str_ProcNames) strMsg = strMsg & str_ProcNames(intJ) & vbCr Next MsgBox strMsg End Function
Copy and paste the above program into a Standard Module and save it.
To display a list of procedures from a Standard Module, display the Debug Window (Ctrl+G) and type the following command with a standard Module name as parameter like the sample given below and press Enter Key:
Sample run display of Procedure Names in a MsgBox is shown below:
To take listing of procedures from a Form or Report Module use the following Syntax:
Sample run output from a Form Module.