How to find unused object/items?

[amorosik]

I would like to build a procedure to search for reports present in my program but which are never started by any piece of code
So the first phase will be to make a list of the available reports, but then how to go through the code both present in the modules and present in the forms to verify that the name of the report never appears?
I know there are specialized programs that do that, but I'd like to understand exactly how they work, and therefore how to step through the code of modules and forms Do you know if there are examples of vba code that allow me to understand exactly how these programs work?

 

[ebs17]

With the Application.SaveAsText method, you can export the complete definitions of all access objects (forms, reports, macros, modules) in text files.
Text files with a plaint text are then easy to search.

 

[MajP]

Programming In The VBA Editor

www.cpearson.com

The code is here to loop all modules and procedures and search for text. Then simply write to a table the report and where it is called.
I did experience the issue with proc_kind which is resolved 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

 

[jdraw]

Has anyone done this "Then simply write to a table the report and where it is called."? Not necessarily Report, but whatever and wherever?

 

[MajP]

Yes

 

[jdraw]

Thanks MajP. I suppose I should have asked that question a little differently.
Does anyone, who has done this, have code they can share and post in the forum?

This is the sort of thing Google has identified.

 

[MajP]

I have a whole bunch of code and a helper class that demos things you can do in video. I r o not do that specific case, but have most of the pieces.
I will try to demo this specific case.

 

[jdraw]

Thanks.

 

[Josef P.]

The challenge in this task is to detect if the report name is in an active code block.

Example:
Report name to be checked: "repXyzTotals"

Code location with the name:

private sub OpenSummaryReport()
     ' Const RepName as String = "repXyzTotals"
     ' replaced with:
     Const RepName as String = "repXyzSummary"
     '...
     DoCmd.OpenReport  RepName, ...
end sub

Note: the example is of course bad code style, but something like that could maybe happen.

 

[MajP]

You have to read line by line so you can check if it is commented out. You can get a 95% solution I am sure.

 

[Minty]

VTools deep search can find a string pretty much anywhere within a database.

V-Tools

 

[ebs17]

V-Tools have open source code, regarding...

but I'd like to understand exactly how they work

 

[The_Doc_Man]

Of course, there is this problem: If you have a module with multiple Subs or Functions in it and one particular Sub in that module uses all of the other subs and functions therein, you would find in a cross-referencer or any other search tool that all of the entry points in the module are active ... except one.

 

[amorosik]

Have you looked at Total Access Analyzer by FMS? They have some pretty good tools. www.fmsinc.com Your employer will almost certainly pay for it given the amount of time it will save you and therefore them

Yes, I know the fame of FmsInc tools well
But I would like something more limited in functionality but over which I can have full control of the actions to be performed

 

[amorosik]

Of course, there is this problem: If you have a module with multiple Subs or Functions in it and one particular Sub in that module uses all of the other subs and functions therein, you would find in a cross-referencer or any other search tool that all of the entry points in the module are active ... except one.

I was thinking of dedicating a db table to record all the objects and their type contained in the program
Once stored permanently, it should be easy to check if the name of a form/report appears only once and therefore is essentially orphaned, as it is never used in the code of the procedure analysed.
From the first tests, however, the times seem much greater than what you see on the standard performance analyzer of Access or similar tools

 

[MajP]

I was thinking of dedicating a db table to record all the objects and their type contained in the program

That can be done in a query of the systems table. No need for a table.

 

[KitaYama]

but the date information regarding Modules is wrong since the date changes each time Access closes whether anything got updated or not.

@Pat Hartman can you elaborate on this please?

The following image is from a database that I use regularly on a daily basis. All the objects started with mod are modules.
I don't know if the shown DateUpdate is correct or not, but I know I open and close it several time. Yet the date shown is not from the last time the database was closed. You also see modManu that shows 2023/03 which I think I remember I updated it several months ago.

thanks.

 

[jdraw]

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.

 

[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.