Find all empty vba fucntions and procedures

[jsdba]

Can someone help with some code that can make a list of empty vba functions and procedures in my access application? I need to do some code cleaning up. e.g.

Private Sub Form_Load()

End Sub

 

[GPGeorge]

Can someone help with some code that can make a list of empty vba functions and procedures in my access application? I need to do some code cleaning up. e.g.

Private Sub Form_Load()

End Sub

MZ-Tools has a utility that does this, along with a host of other functions. It's a commercial tool. I consider it well worth the cost of a license.

You may find other tools that do the same thing, some of which might be free. Otherwise, it's going to be pretty much searching manually.

 

[MajP]

has a utility that does this

I was trying to see if MZ-Tools could do this, but could not find that feature. Where is that feature?

 

[Eugene-LS]

Where is that feature?

In Review Quality feature - Select Dead Code Review

 

[MajP]

In Review Quality feature - Select Dead Code Review

Thanks!

 

[jdraw]

I was expecting that MZTools --ReviewQuality ---DeadCodeReview would identify functions/subs that had no executable code which I think is what the OP is asking. It doesn't identify such functions/subs in my test. It does show constants and variables that are not used.
Perhaps the OP can tell us more about the requirement.

Sub EmptyDummy()
End Sub

MZTools identified that this routine had no header.

 

[ebs17]

You could refer to this:
VBA process to delete nearly 200 empty modules is needed.

 

[isladogs]

The following code is by Patrick Wood and is a modified version of code by Thomas Moller at

https://team-moeller.de/?Tipps_und_Tricks:Rund_um_die_VB-IDE:pruefen_ob_Modul_leer_ist

This creates a list of empty code modules in the immediate window

It uses VB Extensibility but with late binding so no extra references are required

Option Compare Database
Option Explicit

'http://www.team-moeller.de/?Tipps_und_Tricks:Rund_um_die_VB-IDE:Pruefen_ob_Modul_leer_ist
'---------------------------------------------------------------------------------------
' Procedure : ModulesEmptyList
' Author    : http://www.team-moeller.de/?Tipps_und_Tricks:Rund_um_die_VB-IDE:Pruefen_ob_Modul_leer_ist
'               : Modified by Patrick Wood - Gaining Access Technologies http://gainingaccess.net/
' Date      : 1/24/2012
' Purpose   : Produce a list of empty modules
'---------------------------------------------------------------------------------------
'
Public Function ModulesEmptyList() As String
On Error GoTo ErrHandle
  
    Dim vbcModule As Object
    Dim lngLine As Long
    Dim strLine As String
    Dim strList As String
    Dim blnEmpty As Boolean
  
    ModulesEmptyList = False 'Default Value
    
    With VBE.ActiveVBProject
        'For each component in the project ...
        For Each vbcModule In .VBComponents
  
            blnEmpty = False 'Assuming the Module is Not Empty.
      
            'If the Module Declaration lines = total count of lines in the Module check it.
            If .VBComponents(vbcModule.Name).CodeModule.CountOfLines _
                = .VBComponents(vbcModule.Name).CodeModule.CountOfDeclarationLines Then
              
                blnEmpty = True 'Assuming the Module is Empty.
              
                For lngLine = 1 To .VBComponents(vbcModule.Name).CodeModule.CountOfLines
                    strLine = Trim$(.VBComponents(vbcModule.Name).CodeModule.Lines(lngLine, 1))
                    If Len(strLine) > 0 Then
                        If Left$(strLine, 6) <> "Option" Then
                            If Left$(strLine, 1) <> "'" Then
                                If Left$(strLine, 3) <> "Rem" Then
                                    blnEmpty = False
                                    Exit For
                                End If
                            End If
                        End If
                    End If
                Next lngLine
              
                'Add any Empty Module Names to list.
                If blnEmpty = True Then
                    strList = strList & vbcModule.Name & vbCrLf
                End If
            End If
        Next vbcModule
    End With
  
    'Build the Return string
    If Len(strList & "") > 0 Then
        strList = "********** List of Empty Modules **********" & vbCrLf & strList
    Else
        strList = "No empty Modules were found."
    End If
  
    ModulesEmptyList = strList
  
    Debug.Print strList

ExitHere:
    On Error Resume Next
    Set vbcModule = Nothing
    Exit Function

ErrHandle:
    Call MsgBox(Err.Number & " " & Err.Description _
    & " In Procedure ModulesEmptyList")
    Resume ExitHere
  
End Function

Note that it does NOT list empty procedures within other code modules.
To do that you would need to loop through every procedure and count the number of code lines without comments.
If a procedure only has 2 uncommented code lines (header & footer), it is empty

Doing this requires programming the VB Editor & using the VB Extensibility reference
You can find code that will do part of the work for you at Chip Pearson's excellent Excel site:

Programming In The VBA Editor

www.cpearson.com

 

[jdraw]

Colin,
Thanks for the info. The references I found (based on ebs17 post #7) all talked about empty modules, not empty procs. So reviewing modules to identify "procs"(function/sub) is key. I also found that count procedure lines included comments when using vbe. However, I am an extreme novice with vbe and have lots to learn. And you (again) are providing some links and hints to support the learning.

 

[MajP]

This code Finds the empty procedures and functions except it has a bug I have not yet figured out. It works but will crash on any Property. I did not put in any error checking to overcome this in case someone wants to figure this out.
This uses the Pearson code module.
Basically loops all Components (modules, forms, reports), and reads all procedures. Then reads line by line in a procedure to see if there is a non empty line that does not start with
'
REM
Private_
Public _
End_

(where _ is a space)

Public Function GetEmptyProcedures() As String
   
On Error GoTo Err_Handler
        Dim VBAEditor As VBIDE.VBE
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim lineNum As Long
        Dim NumLines As Long
        Dim ProcName As String
        Dim ProcKind As VBIDE.vbext_ProcKind
        Dim PI As ProcInfo
        Dim strOut As String
        Dim ProcStart As Boolean
        Dim CurrentProc As String
        Dim OldProc As String
       
        Set VBAEditor = Application.VBE
        Set VBProj = VBAEditor.ActiveVBProject
       
        For Each VBComp In VBProj.VBComponents
               If VBComp.Name <> "modVBECode" Then
               
                Set CodeMod = VBComp.CodeModule
                With CodeMod
                    lineNum = .CountOfDeclarationLines + 1
                    CurrentProc = .ProcOfLine(lineNum, ProcKind)
                    Do Until lineNum >= .CountOfLines
                        ProcName = .ProcOfLine(lineNum, ProcKind)
                        'Debug.Print ProcName
                       
                         lineNum = .ProcStartLine(ProcName, ProcKind) + _
                                .ProcCountLines(ProcName, ProcKind) + 1
                        If Not HasCode(CodeMod, ProcName) Then
                        If strOut = "" Then
                          strOut = "Module: " & VBComp.Name & "            Procedure: " & ProcName
                        Else
                          strOut = strOut & vbCrLf & "Module: " & VBComp.Name & "            Procedure: " & ProcName
                        End If
                        End If
                           
                    Loop
                End With
            End If
            Debug.Print strOut
        Next VBComp
        GetEmptyProcedures = strOut
Exit_Handler:
        Exit Function
       
Err_Handler:
    MsgBox "Error " & Err.Number & " in GetEmptyProcedures : " & Err.Description
    GoTo Exit_Handler
   
End Function

Public Function HasCode(CodeMod As CodeModule, ProcName As String) As Boolean
  Dim lineNum As Long
  Dim Thestart As Long
  Dim Thestop As Long
  Dim theLine As String
  Dim ProcKind As VBIDE.vbext_ProcKind

  Thestart = CodeMod.ProcStartLine(ProcName, ProcKind)
  Thestop = Thestart + CodeMod.ProcCountLines(ProcName, ProcKind)
' Debug.Print Thestart & vbCrLf & Thestop
  For lineNum = Thestart To Thestop
    theLine = Trim(CodeMod.Lines(lineNum, 1))
    If Len(theLine) > 0 Then
      If Left$(theLine, 1) <> "'" And Left$(theLine, 3) <> "Rem" And Left(theLine, 8) <> "Private " And Left(theLine, 7) <> "Public " And Left(theLine, 4) <> "End " Then
          HasCode = True
         ' Debug.Print theLine
         Exit Function
     End If
   End If
   Next lineNum
End Function

In the code a lot of the functions require to pass the procedure name, and the type of procedure (let, set, get, regular). As far as I can tell CP never actually sets the ProcKind and the code works. In these lines I do not set it because not sure how to get it.

Thestart = CodeMod.ProcStartLine(ProcName, ProcKind)
Thestop = Thestart + CodeMod.ProcCountLines(ProcName, ProcKind)

So I seem to have to determine ProcKind or the let/get/set will cause the code to fail. It seems to work for all others. The demo does not include any property procedures.

 

[jdraw]

Thanks for posting this MajP.
I found some related info on VBE, but VBE is new to me. Trying to learn a little.
-Deconstructing VBCode module
-VBA – VBE Enumerate Modules, Procedures and Line Count

Does anyone have a reference to the VBE Object model that shows all the pieces and their relationship to each other?

 

[sonic8]

Does anyone have a reference to the VBE Object model that shows all the pieces and their relationship to each other?

The official documentation is here: Visual Basic Add-in Model reference
Unfortunately, structure as well as content of this documentation are somewhat lacking.