Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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?

Earlier Articles:

  1. Writing Code with VBA.
  2. Uploading Code from external Text File into a 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 Article 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 Procedure Steps.

The Pseudo Code of the VBA Program is given below.

  1. Take Count of Code Lines of the Module, Standard Module or Class Module.
  2. Take Count of Global Declaration Lines.
  3. First Procedure Name  =  Count of Global declaration Lines + 1
  4. Array(0) = First Procedure Name. Saves the Function/Sub-Routine name into an Array.
  5. 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.

The VBA Code.

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 
 MsgBox strMsg 
End Function 

Copy and paste the above program into a Standard Module and save it.

Run Code Directly from Debug Window.

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 a parameter like a sample given below and press Enter Key:

ListOfProcs "Utility_Local"

Sample run display of Procedure Names in a MsgBox is shown below:

Form/Report Module Procedure Listing.

To take the listing of procedures from a Form or Report Module uses the following Syntax:

ListOfProcs "Form_myFormName"


ListOfProcs "Report_myReportName"

Sample run output from a Form Module.

Technorati Tags: ,

No comments:

Post a Comment

Comments subject to moderation before publishing.



MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.



Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object Graph Charts ListView Control Query VBA msaccessQuery Calculation Event ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Android App Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code