How to find unused object/items?

[Pat Hartman]

@KitaYama I just updated a module three times and couldn't get the date to change in MSysObjects. When you look by turning on details in the Nav Pane, the modified date for ALL FOUR of the modules in this database changed to 8:12 today. I cannot explain why.

Here is from a different db that I haven't updated in quite a while. this image is MSysObjects

this image is from the Nav Pane.

Notice that the CreateDate's line up but in the Nav Pane, all show being modified 2/2/23 at 7:35:46 PM????? Notice that not even one module shows the February date in MSysObjects.
I added a new function to the first module. Now all the dates show 7/17/23 8:26:15 But the results from MSysObject didn't change.

 

[KitaYama]

@Pat Hartman thanks for the details.

 

[MajP]

I'd like to see the query(ies) or code that shows where a procedure is called - as mentioned in #4.
I understand getting procedures and type(sub/function) from Forms/Reports/Modules and record in a table. But getting info about where the procedure is called from (eg MZTools Method Callers) is unclear.

As I said the Pearson code basically has all the pieces you need if you where going to build this. However, the code is not that usable since it just prints things out to the immediate window.

The issue with VBIIDE Extensibility is the way it handles working with procedures seems very cumbersome. There is no collection of Procedures or procedure objects. You have a VBA Project which has Components ( which are just the container for a module) and then you have CodeModules. You can do all the normal looping and working with properties. But a Code Module does not have a set of Procedures. Instead in order to work with a procedure you basically read a code module like a text file line by line until you come to what would be a procedure. When it hits the declaration line it reads until the end of the text. A code module then has a lot of functions to then find procedure information.
If you study Pearson's file he uses this approach.

So I figured a solution to make this easier would be to build a Procedure Class and a Procedure Collection Class. Since VBA does not have inheritance this gets real awkward. You cannot make a traditional composite class. Which is explained in detail here

Automagic testing framework for VBA

Building on @RubberDuck's recommendations, I now have something I find... beautiful. I'm sure there's a couple of things left to polish - this site is about making great code out of good any code, ...

codereview.stackexchange.com

This forces you to make a Code Module class and Code Module Collection Class. In hindsight it would have been easier to do what CP did and just make a bunch of functions.

Anyway I made a bunch of classes to do this. I prefixed my classes with VBIDE_

VBIDE_Project
   VBIDE_CodeModules
        VBIDE_CodeModule
               VBIDE_Procedures
                      VBIDE_Procedure

The Demo form then shows how to loop the modules, and procedures. I demonstrate how to search for where an object is used.

If there are other things you want demonstrated it should be easier now that I have put together the demo.

 

[Cotswold]

Doesn't Peter Cole's 32bit to 64bit Conversion program (Access Europe) do something similar?
It trawls through the code and locates where changes should be made. Which is similar but not identcal ot this requirement.

Is it possible to repeat what Peter is doing, with amendments?

 

[Josef P.]

In principle, the search for (un)used forms/reports is a text search with some constraints.

• The name is not in a comment.
• The procedure in which the name is used.
• Name could be defined as constant in the procedure, in the module header or also in another module => check use of constant.
• Name could be composed by code.
• .... (there are certainly many more points to consider).

To recognize the procedures/methods/properties VBIDE.CodeModule has good methods that return the type (vbext_ProcKind), procedure code, etc.
If the OP wants to create something himself, you could start "small" and add more and more test conditions later.
We had a similar issue recently: https://www.access-programmers.co.uk/forums/threads/which-modules-are-needed.328123/

And then there might be forms and reports that can be launched from the application but are never used by the users. => here, for a long-term cleanup, one could come up with the idea of logging the actively used forms and reports.

 

[MajP]

As mentioned the search I provided is probably a 90-95% solution. If interested the search could be improved but the last 10% gets complicated.

Here is a demo. I have the following sample code that calls a report.

'******************************** RptVBIDE_Demo ******************************
Public Sub Case1_Report()
  'Easy
  DoCmd.OpenReport "rptVBIDE_Demo", acViewPreview
End Sub

Public Sub Case2_Report()
  'Set to a variable and used
  Dim r As String
  r = "rptVBIDE_Demo"
  DoCmd.OpenReport r, acViewPreview
End Sub
'************************* Returns but should not
Public Sub Case3_Report()
  'Set to an unused variable
  Dim r As String
  r = "rptVBIDE_Demo"
  'DoCmd.OpenReport r, acViewPreview
End Sub
'***************** Will Not Return ************************
Public Sub RptVBIDE_Demo()
  'Case 4 with the procedure name
  MsgBox "Hello world"
End Sub
Public Sub Case5_Report()
'Coded out
'DoCmd.OpenReport "rptVBIDE_Demo", acViewPreview
End Sub

Selecting the report 'rptVBIDE_Demo' from the object pull down returns the following

Case 3 is returned, but should not. The report name is set to the variable R but R is not called. I imagine that this case could be fixed and the capability exists, but the code would be complicated. The search function might have to even be recursive. However, this could get far more complicated if for example you pass the report name in open args to another form or report. Checking to see if the open args is used in the called object would be extremely complicated.
Case 4 and 5 are not returned as expected
Case6 and Command23 just demonstrate something returned from another standard module and a class module and are correct

So it is pretty easy to build a 90% solution and extremely hard to make the 99% - 100% solution. In these cases I do not usually try to solve the 99% solution but provide the tools for the user to verify potential issues. In the above case I provided the lines where the code is called. I see in case 2 and case3 that I could have an issue because the name is set to a variable R and r may not be called. I provide the user a way to then verify the potential issue by diving into those cases.

Simply verify the found procedures. Checking Case3 the user can see that R is coded out and not used. Same can be done on Case 3 showing it is used.

 

[jdraw]

MajP,
Thank you for the database. I'll work to understand the innards. Should be a learning opportunity.

I have followed some of Chip Pearson's materials and previously did create code to review project and identify each procedure and type and the number of lines. I found some posts and articles by you, Colin and Daniel/devhut.

--Just saw another post has been made while typing-- it's your 'rptVBIDE_Demo'---

This is great stuff. Thank you.

As an aside: Have you considered creating videos, such as Youtube, with a focus on Classes and Event Programming? Seems you are the most fluent in this area and have posted demos and refinements in the forum.

 

[MajP]

Thank you for the database. I'll work to understand the innards. Should be a learning opportunity.

I would focus on the Pearson code. I thought I was doing myself a favor rolling my own Procedure/s classes, but I think that did not make it as simple to use as I hoped. This had down stream effects forcing me to build Code Module/s classes. I believe there is a reason for working at the code module level, and then when you need to work at the procedure level you reread the module text.

In fact here is the code module class which show the methods and properties.

As you can see there is no Procedures class, only functions (start with PROC) to read the lines of text and return "properties" of the procedure. Think of it like a word document without a collection of paragraph. You would get the second paragraph by reading down the the second carriage return.
Interesting though there is a robust Find function in the CodeModule class. If you can see at the bottom of the window you can do some robust searching (Case sensitive, pattern matching.)

To learn this I would use what CP provided code (i provide his module with some additional code in my demo), and build a form similar to mine. Then change his methods that print to the immediate window into functions that return a string. Then you could demo that functionality from a form and print to a textbox like I am doing.

The biggest problem I had was all the functions dealing with finding a procedure info need to know what kind of function it is. All of the PROC functions have a signature requiring the type of procedure (vbext_ProcKind) as a parameter. If not it fails on any let, get, or set. This seems impossible because you are reading down the text until you come to a line that is the start of the procedure. You use the ProcOfLine function to get the procedure name for that line in the module. Once you have the name and ProcKind you can use it in the other PROC functions to get information about the procedure. But ProcOfLine requires you to pass in the vbext_ProcKind, but you do not know that yet. In Pearsons example it fails on properties. This problem is described in the link I provided, so I was not the only one struggling with this.
So here is the needed workaround. You have to modify this to pass in the codemodule since I was using my class.

Private Function GetProcedureKind(ByVal Linenum As Long) As vbext_ProcKind
    Dim result As vbext_ProcKind
    Dim strline As String
    Dim aLine() As String
    Dim firstWord As String
    Dim secondWord As String
    strline = Trim(Me.CodeModule.Lines(Linenum, 1))
    aLine = Split(strline, " ")
    If Left(strline, 4) = "END " Or Left(strline, 1) = "'" Or UBound(aLine) < 1 Or Left(strline, 5) = "Exit " Then
         GetProcedureKind = -1
         Exit Function
    End If
    
    firstWord = aLine(0)
    secondWord = aLine(1)
    If firstWord = "Sub" Or firstWord = "Function" Or secondWord = "Sub" Or secondWord = "Function" Then
      result = vbext_pk_Proc
    ElseIf firstWord = "Property" Or secondWord = "Property" Then
        If InStr(strline, "property Get ") Then
          result = vbext_pk_Get
        ElseIf InStr(strline, "Property Let ") Then
          result = vbext_pk_Let
        ElseIf InStr(strline, "Property Set ") Then
          result = vbext_pk_Set
        End If
    Else
        result = -1
    End If
    'If result <> -1 Then Debug.Print "result " & result & " "; strline
    GetProcedureKind = result
End Function

Now supposedly you do not need to do this because vbext_ProcKind is passed by reference and therefore set in the ProcOfLine function and you can use it as a return parameter. I could not get it to work, and the above function make more logical sense to me.

 

[Josef P.]

Now supposedly you do not need to do this because vbext_ProcKind is passed by reference and therefore set in the ProcOfLine function and you can use it as a return parameter.

In CodeModuleReader.Procedures I cycle through the procedures from a code module using the line numbers.

Principle: (for complete code see property get procedure 'Procedures' in CodeModuleReader, line 486)

   CurrentLine = m_CodeModule.CountOfDeclarationLines + 1
   Do While CurrentLine <= m_CodeModule.CountOfLines
      TempProcKind = vbext_pk_Proc ' .. .only a dummy value - will be overwriten in ProcOfLine
      TempProcName = m_CodeModule.ProcOfLine(CurrentLine, TempProcKind)
      [... do something with procedure ...]
      CurrentLine = m_CodeModule.ProcStartLine(TempProcName & "", TempProcKind) + m_CodeModule.ProcCountLines(TempProcName, TempProcKind)
      CurrentLine = CurrentLine + 1 'm_CodeModule.ProcCountLines(procName, procKind)
   Loop

 

[MajP]

The way I understand it is supposed to work is that in the ProcOfLine function the vbext_ProcKind argument is used only as a return parameter in this property where the other PROC properties require the correct value. You pass it by reference and it is then set in ProcOfLine. Now on the return it can be used in the other PROC procedures that require it. I get it, but sure would have been helpful if the signature was something more like

Property ProcOfLine(Line As Long, ProcKind As Return_vbext_ProcKind) As String

instead of

Property ProcOfLine(Line As Long) As String

especially since this is poorly documented anywhere.
Heck they could of simply made another property
ProcKindOfLine(Line as Long) as vbext_Prockind

 

[Josef P.]

Even worse is CodeModule.Find ... there the line number is read as input (start line) and then overwritten as return value for the hit.

Example:

Option Compare Database
Option Explicit

Private Sub Test()

   Dim cm As CodeModule
   Dim LineNum As Long
   Dim procKind As vbext_ProcKind

   Set cm = VBE.ActiveVBProject.VBComponents("modTest").CodeModule

   LineNum = 23 ' = line before Xyz1
   If cm.Find("Xyz", LineNum, 0, 0, 0) Then
      Debug.Print LineNum, cm.ProcOfLine(LineNum, procKind), procKind
   End If

   LineNum = LineNum + 1
   If cm.Find("Xyz", LineNum, 0, 0, 0) Then
      Debug.Print LineNum, cm.ProcOfLine(LineNum, procKind), procKind
   End If

End Sub

Private Function Xyz1() As Long

End Function

Private Property Get Xyz2() As Long

End Property

Output in immediate window:
24           Xyz1           0
28           Xyz2           3

Nevertheless, the methods are quite useful.

 

[MajP]

Nevertheless, the methods are quite useful.

Trying to overcome some of this weirdness is why I thought to make my own wrapper classes, but not sure I do it that way again. I think a single class with a bunch of methods using the native vbide would make more sense.

 

[jdraw]

MajP,
Yes I recognize there is no Procedure class. In the stuff I have done after reading and trying many things, it's a matter of reviewing code modules and identifying where a declaration or start of procedure occurs. I wish I knew more in order to communicate better, but I find the vbe and articles by Pearson and others confusing. I realize his (CP) is probably the best reference there is, but it is a different mind set. And as many have said, there isn't a lot of info and examples of vbe coding.
Here is a bit of code I did earlier to review forms/reports and modules an to put some subs and functions into a table.
It isn't much, but it is a sample of what I have found/done. It's certainly a different level than you and Josef are discussing.

I invoke this routine from a click on a label in a form.

' ----------------------------------------------------------------
' Procedure Name: ReportProcNames
' Purpose: Routine to write all procs and procedures to Table ModsAndProcsT
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 26-Mar-23
' ----------------------------------------------------------------
Public Sub ReportProcNames()

          Dim component As Object
          Dim NameX As String
          Dim Kind As Long
          Dim Start As Long
          Dim Body As Long
          Dim Length As Long
          Dim BodyLines As Long
          Dim Declaration As String
          Dim ProcedureType As String
          Dim Index As Long
          Dim ssql As String
          
        
          
10        On Error Resume Next
20        DoCmd.Hourglass True
30        CurrentDb.Execute "Drop Table ModsAndProcsT;", dbFailOnError ' rebuild the table
40        ssql = "Create Table ModsAndProcsT " _
              & " (CompID AUTOINCREMENT Not Null Primary Key, ComponentName varchar(40), ProcName varchar(50),ProcType varchar(10), BodyLines Integer,runDate date);"
50        CurrentDb.Execute ssql, dbFailOnError

60        Debug.Print "Component/Module Name" & String(20, " ") & "Proc Name" & _
              String(30, " ") & "ProcedureType" & "    " & "NumBodyLines" & vbCrLf _
              & String(110, "-") & vbCrLf
70        For Each component In Application.VBE.ActiveVBProject.VBComponents

80            With component.CodeModule

                  'The Procedures
90                Index = .CountOfDeclarationLines + 1
                  
100               Do While Index < .CountOfLines
                    
110                   NameX = .ProcOfLine(Index, Kind)
120                   Start = .ProcStartLine(NameX, Kind)
130                   Body = .ProcBodyLine(NameX, Kind)
140                   Length = .ProcCountLines(NameX, Kind)
150                   BodyLines = Length - (Body - Start)
160                   Declaration = Trim(.Lines(Body, 1))
170                   ProcedureType = GetProcKind(Kind, Declaration)

180                   Call WriteToModsAndProcsT(component.name, NameX, ProcedureType, BodyLines, Date)
190                   Debug.Print component.name & String(55 - Len(component.name), " ") & NameX & " " & _
                          String(45 - Len(NameX), " ") & ProcedureType & String(20 - Len(ProcedureType), " ") & CStr(BodyLines)

200                   Index = Start + Length + 1

210               Loop

220           End With
230           Debug.Print

240       Next component
250       If Not component Is Nothing Then Set component = Nothing
260       Debug.Print "Finished reporting Procs and Modules"
  
270       DoCmd.Hourglass False
End Sub


Public Function GetProcKind(Kind As Long, Declaration As String) As String

    'Change the procedure kind to text
    Select Case Kind

        Case vbext_pk_Get
            GetProcKind = "Get"

        Case vbext_pk_Let
            GetProcKind = "Let"

        Case vbext_pk_Set
            GetProcKind = "Set"

        'Best Guess
        Case vbext_pk_Proc
            If InStr(1, Declaration, "Function ", vbBinaryCompare) > 0 Then
                GetProcKind = "Func"
            Else
                GetProcKind = "Sub"
            End If

        Case Else
            GetProcKind = "Undefined"

    End Select

End Function

' ----------------------------------------------------------------
' Procedure Name: WriteToModsAndProcsT
' Purpose: Routine to write to ModsAndProcsT
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 24-Mar-23
' ----------------------------------------------------------------
Sub WriteToModsAndProcsT(ComponentName, ProcName, ProcType, BodyLines, rundate)
 
          Const InsSQL As String = _
              "Insert into ModsAndProcsT (ComponentName,ProcName,ProcType, BodyLines, rundate)" _
              & " VALUES ( p0,p1,p2,p3,p4)"
          With CurrentDb.CreateQueryDef("", InsSQL)
              .Parameters("p0") = ComponentName
              .Parameters("p1") = ProcName
              .Parameters("p2") = ProcType
              .Parameters("p3") = BodyLines
              .Parameters("p4") = Date
              .Execute dbFailOnError
          End With
End Sub

 

[AHeyne]

Maybe use a completely different approach: Rename all reports of which you aren't sure if they are used and let the time do the work. You will recognize yourself or be informed by your users which ones are missing now.

 

[jdraw]

Yes AtzeX that is a common method. We all have used it or similar for a long time. It does take time to reconcile. It does prevent/reduce deleting things physically in error. You can't be sure a routine is NOT needed until you exercise all paths through your code. It could be something that runs end of year for example.

 

[MajP]

@jdraw,
So here is my philosophy that it is easier to build a better UI then write code that can find all the cases. By adding a tree view on the left and a detail view when you click on a node you can very quickly verify the results.
On the right I return my results for the report search, but I see that in case 2 and 3 I set the report to a variable R. I then can quickly validate that it being called in code. In this case it is coded out.

 

[Josef P.]

Another approach: Code refactoring
Replace each string with the name of the report with a constant etc.
Then all you have to do is comment out the constant declaration and the compiler will show you where the report is used. Then check this code and so on.

 

[jdraw]

Great stuff MajP and Josef ---just a little over my "pay grade" and understanding. However, this is great info and I'm sure will be helpful to others.
We often get requests to identify unused/dead code.
MajP/Josef if this could be made an add in, it could be extremely helpful/useful to others --especially those doing development/maintenance or potential rewrite.

MajP, as mentioned in #27
As an aside: Have you considered creating videos, such as Youtube, with a focus on Classes and Event Programming? Seems you are the most fluent in this area and have posted demos and refinements in the forum.

 

[MajP]

this is a little slicker with the icons. I color methods without any useable code as red.

In the short term if you want to play with this you can do it similar to an add in. Open this database and go to references. Browse to another database and add it as a reference. In the code that calls the initialize method you can define which project to use. If you leave it blank it picks the active project.

Private Sub InitVP()
  vp.Initialize '"Northwind"
  Me.lblProject.Caption = vp.SelectedProject.Name
End Sub

Put the name of the project of your referenced database. It will appear in the regular vb object tree. Mine showed up as
Northwind (Northwind2010ALL), but use just the name excluding what is in the brackets.
The combo box with the objects to search pulls from the systems table and it will not be in synch with the referenced database. You could link to the other database system table.

As an aside: Have you considered creating videos, such as Youtube, with a focus on Classes and Event Programming? Seems you are the most fluent in this area and have posted demos and refinements in the forum.

If I ever clear my honey do list maybe get a round to learning how to make content. I already waste to much time on here when I should be doing real work.

 

[Josef P.]

[OT]
For fun, the dependencies within VBA Extensibility Demo V4.accdb: