How to find unused object/items? (1 Viewer)

[MajP]

You will have to explain that one, and how it is made. Some does not seem to make any sense. SampleStandardModule_1 is a bunch of fake procedures referencing the Report rptVBIDE_DEMO to test the search feature. So it calls that report, but it does not use anything in the Pearson Module. Also I just put CP's code from the website into the module, but do not use it. I may have used the same names in the class modules but they do not call any external methods. I guess it sees similar names even though they are encapsulated in a class.

 

[Josef P.]

Fixed pic in #40 ... Bug in code that recognizes this as procedure usage:

S = "Sub HelloWorld()" & vbCrLf & _
        "    MsgBox ""Hello, World""" & vbCrLf & _
        "End Sub"

The image was created with GraphViz.

VBIDE_PearsonVBECode needs:

• VBIDE_Procedure because of Enum LineSplits
• VBIDE_ProcedureOld because of ProcScope

The dependency between VBIDE_PearsonVBECode and VBIDE_Project is also not correct.
.. same bug as above:
MsgBox "Error " & Err.Number & " in GetModules : " & Err.Description
=> GetModules is a procedure in VBIDE_PearsonVBECode.

/edit:
With the RegExp variant it works better:

 

[MajP]

We often get requests to identify unused/dead code

@jdraw
I will see if I can add some dead code features. I have MZ tools so it would be pretty complicated to get close to that. I think you can pretty easily determine if a procedure is called. Also for local variables/constants it should be easy to search in a procedure to see if used. Searching for class variables and procedure level variables would be harder.

 

[amorosik]

I've tried both MzTool which I use daily, and Total Access Analyzer (the demo)
But neither can find reports/forms/module never used by the rest of the code
Only bariable and constant are identified
Do you know that there are tools to identify reports, forms, subs, functions, macros that exist but are never used by the rest of the program?

 

[ebs17]

ACCESS Dependency Checker - Overview

by Thomas Koester

Identifies orphanded and broken objects like a query that is based on an already deleted table

For your understanding: The add-in has open source code.

 

[MajP]

When looking for unused code, (code not called by other code) then event procedures are normally not called by other procedures. Anyone have an idea how you could determine if a procedure is an event procedure or regular procedure. Proc_Kind only tells if it is a propert or method/function. If you are looking at it then you could maybe make a huge list of all the signatures ( _Click(), _Load, _BeforeUpdate ....) which you could compare against, but listing all of those for all control types would be painful. Any ideas?

 

[jdraw]

I just knew this topic was of interest. I have been working on finding whole words in a string using regex.

As for Event Procedures, is there a limited list of events that could be checked to identify event, if not one of those then standard? I guess that's what you're asking. I think the list should not exceed 100 or so ---just a guess.

UPDATE: 15:30
Here's a partial list from some previous work.

EventName , EventDesc, EventType

1,"Open"," When the report opens but before printing","Report "
2,"Close"," When the report closes and is removed from the screen","Report "
3,"Active"," When the report receives the focus and becomes the active window","Report "
4,"Deactivate"," When a different window becomes active","Report "
5,"NoData"," When no data is passed to the report as it opens","Report "
6,"Page"," When the report changes pages","Report "
7,"Error"," When a runtime error is produced in Access","Report "
8,"Format"," When the section is pre-formatted in memory before being sent to the printe You can apply special formatting","ReportSection "
9,"Print"," As the section is sent to the printe No more formatting allowed.","ReportSection "
10,"Retreat"," After the Format event but before the Print event.","ReportSection "
11,"Open"," When a form is opened, but the first record is not displayed yet","Form "
12,"Load"," When a form is loaded into memory but not yet opened","Form "
13,"Resize"," When the size of a form changes","Form "
14,"Unload"," When a form is closed and the records unload, and before the form is removed from the screen","Form "
15,"Close"," When a form is closed and removed from the screen","Form "
16,"Activate"," When an open form receives the focus, becoming the active window","Form "
17,"Deactivate"," When a different window becomes the active window, but before it loses focus","Form "
18,"GotFocus"," When a form with no active or enabled controls receives the focus","Form "
19,"LostFocus"," When a form loses the focus","Form "
20,"Timer"," When a specified time interval passes. The interval (in milliseconds) is specified by the TimerInterval property.","Form "
21,"Click"," When the user presses and releases (clicks) the left mouse button","mouse "
22,"DblClick"," When the user presses and releases (clicks) the left mouse button twice on a form","mouse "
23,"MouseDown"," When the user presses the mouse button while the pointer is on a form","mouse "
24,"MouseMove"," When the user moves the mouse pointer over an area of a form","mouse "
25,"MouseUp"," When the user releases a pressed mouse button while the pointer is on a form","mouse "
26,"MouseWheel"," When the user spins the mouse wheel","mouse "
27,"KeyDown"," When the user presses any key on the keyboard when a form has focus or when the user uses a SendKeys macro action","mouse "
28,"KeyUp"," When the user releases a pressed key or immediately after the user uses a SendKeys macro action","mouse "
29,"KeyPress"," When the user presses and releases a key on a form that has the focus or when the user uses a SendKeys macro","mouse "
30,"Current"," When you move to a different record and make it the current record","Data "
31,"BeforeInsert"," After data is first entered into a new record, but before the record is actually created","Data "
32,"AfterInsert"," After the new record is added to the table","Data "
33,"BeforeUpdate"," Before changed data is updated in a record","Data "
34,"AfterUpdate"," After changed data is updated in a record","Data "
35,"Dirty"," When a record is modified","Data "
36,"Undo"," When a user has returned a form to clean state (has been set back to an unmodified state); the opposite of OnDirty","Data "
37,"Delete"," When a record is deleted, but before the deletion takes place","Data "
38,"BeforeDelConfirm"," Just before Access displays the Delete Confirm dialog box","Data "
39,"AfterDelConfirm"," After the Delete Confirm dialog box closes and confirmation has happened","Data "
40,"Error"," When a runtime error is produced","Data "
41,"Filter"," When a filter has been specified, but before it is applied","Data "
42,"ApplyFilter"," After a filter is applied to a form","Data "
43,"BeforeUpdate"," Before changed data in the control is updated to the underlying recordset","Control "
44,"AfterUpdate"," After changed data is transferred to the form's recordset","Control "
45,"Dirty"," When the contents of a control change","Control "
46,"Undo"," When the form is returned to a clean state","Control "
47,"Change"," When the contents of a text box change or a combo box's text changes
Updated When an A","Control "
48,"Updated"," When an ActiveX object's data has been modified","Control "
49,"NotInList"," When a value that isn't in the list is entered into a combo box","Control "
50,"Enter"," Before a control receives the focus from another control","Control "
51,"Exit"," Just before the control loses focus to another control","Control "
52,"GotFocus"," When a nonactive or enabled control receives the focus","Control "
53,"LostFocus"," When a control loses the focus","Control "
54,"Click"," When the left mouse button is pressed and released (clicked) on a control","Control "
55,"DblClick"," When the left mouse button is pressed and released (clicked) twice on a control or label","Control "
56,"MouseDown"," When a mouse button is pressed while the pointer is on a control","Control "
57,"MouseMove"," When the mouse pointer is moved over a control","Control "
58,"MouseUp"," When a pressed mouse button is released while the pointer is on a control","Control "
59,"KeyDown"," When any key on the keyboard is pressed when a control has the focus or when a SendKeys macro action is used","Control "

 

[Josef P.]

The structure is always like this: Sub ObjectName_EventName

1. Check if a _ exists in the procedure name
2. Check if there is a control (or WithEvents declaration) that corresponds to the value before the _.
3. Check whether [Event Procedure] is entered in the respective property.

You could do that via the form reference or export the form with SaveAsText and search in it.

Can Mz-Tools actually handle this? ... that would have to be included in the MZ Tools check, since it is in (normally) the private context.

A function could also be set as an expression in the event property.
This would look like this in the SaveAsText export file:

[..]
Begin Form
    [...]
    OnCurrent ="=Func123([...])"

 

[jdraw]

I updated a list of Events in #47 in this thread.

 

[MajP]

@jdraw,
I added a bunch of controls to a form and ran code to pull out the form, report, and control events. I only got about 65 total. So a few more.
EventList EventList

EventName
_Activate(
_AfterDelConfirm(
_AfterFinalRender(
_AfterInsert(
_AfterLayout(
_AfterRender(
_AfterUpdate(
_ApplyFilter(
_AttachmentCurrent(
_BeforeDelConfirm(
_BeforeInsert(
_BeforeNavigate(
_BeforeQuery(
_BeforeRender(
_BeforeScreenTip(
_BeforeUpdate(
_Change(
_Click(
_Close(
_CmdBeforeExecute(
_CmdChecked(
_CmdEnabled(
_CmdExecute(
_Connect(
_Current(
_DataChange(
_DataSetChange(
_DblClick(
_Deactivate(
_Delete(
_Dirty(
_Disconnect(
_DocumentComplete(
_Enter(
_Error(
_Exit(
_Filter(
_Format(
_GotFocus(
_KeyDown(
_KeyPress(
_KeyUp(
_Load(
_LostFocus(
_MouseDown(
_MouseMove(
_MouseUp(
_MouseWheel(
_NavigateError(
_NoData(
_Open(
_Page(
_Paint(
_PivotTableChange(
_Print(
_ProgressChange(
_Query(
_Resize(
_Retreat(
_SelectionChange(
_Timer(
_Undo(
_Unload(
_Updated(
_ViewChange(

 

[jdraw]

Good stuff! I had a feeling the list would not be too long and could be processed.
Another aside: Could you look at

Classes and Events with a SplitForm or DataSheet

I have attached a database which contains a sample the issue I have. I am implementing a clsForm so I don't have to paste the same code in over and over again. My class works perfectly when the form is a Standard form or Continuous form --> All events fire in the class!!! YEA (Thank you JOHN...

www.access-programmers.co.uk

This seems to be right in your ballpark!
TIA.

 

[isladogs]

@MajP
Thanks for the very comprehensive list.
Of the 65 events in that list, I have definitely never used at least 13 of those in over 25 years of using Access and there are at least another 20 that I have only rarely used. I suspect that goes for most Access developers

Just for info, the list isn't quite complete as it doesn't cover ActiveX controls such as the Slider. That includes an undocumented OnScroll event which I use whenever I include a slider control.
I don't know whether it would be possible to get a list of additional ActiveX events

 

[MajP]

I don't know whether it would be possible to get a list of additional ActiveX events

I did not do it but I think I can get more events for a lot of common Active X. Obviously this list is pretty unbounded
Normally with the Active X you have two sets of properties. I do not understand how it actually works. But if I put a Treeview on the form there is a set of properties I see in Access but this is limited. I think this is more like the "container"
But If you do something like this you get everything else.
dim TVW as Treeview
set TVW as MyTreeViewControl.Object
So I think I can just do the same technique and drop a bunch of active x controls on a form.

 

[Cotswold]

I did not do it but I think I can get more events for a lot of common Active X. Obviously this list is pretty unbounded
Normally with the Active X you have two sets of properties. I do not understand how it actually works. But if I put a Treeview on the form there is a set of properties I see in Access but this is limited. I think this is more like the "container"
But If you do something like this you get everything else.
dim TVW as Treeview
set TVW as MyTreeViewControl.Object
So I think I can just do the same technique and drop a bunch of active x controls on a form.

Have to say MajP, very impressed by your work on this utility.

 

[spaLOGICng]

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?

Hi, hope this helps…

There is really no built in way to do this. I learned this lesson a long time ago. Now in every application that I build a implement a routing handler for all open message whether it is a table, a query, a form, a report, etc. I have a table called object usage log every time one of those objects is opened. It is routed through the handler, and a record is written to the table, along with the username and machine name. I can bump that up against the list of objects found in the MSysObjects table. After about three months, you should have a pretty good idea of what objects are being used and what are not.


I also route all errors through an error handler that writes the error to an error log table. If the back in database is access, I will usually have those two tables in a separate database. That way it does not consume unnecessary space.

I hope that give you a good idea.

 

[jdraw]

Can you share the code that you use for your Handler? Or a sample database that shows objects and the handler and related table? I think readers would like to see some sample usages .

 

[amorosik]

Hi, hope this helps…

There is really no built in way to do this. I learned this lesson a long time ago. Now in every application that I build a implement a routing handler for all open message whether it is a table, a query, a form, a report, etc. I have a table called object usage log every time one of those objects is opened. It is routed through the handler, and a record is written to the table, along with the username and machine name. I can bump that up against the list of objects found in the MSysObjects table. After about three months, you should have a pretty good idea of what objects are being used and what are not.


I also route all errors through an error handler that writes the error to an error log table. If the back in database is access, I will usually have those two tables in a separate database. That way it does not consume unnecessary space.

I hope that give you a good idea.

Yes is a good idea, but it seem me very resource expensive
But exactly, wich objet you track?
Form/Report/Sub/macro/Single Variable?

 

[Josef P.]

Just for fun:
C# code to get events from Access lib.

public void ReadAllEventsFromAccessLib()
        {
            Assembly vbaInteropAssembly = Assembly.Load("Microsoft.Office.Interop.Access");

            Dictionary<string, List<string>> eventDictionary = new Dictionary<string, List<string>>();

            foreach (var type in vbaInteropAssembly.GetTypes())
            {
                if (type.IsInterface || type.IsClass)
                {
                    Dictionary<string, int> enumValues = new Dictionary<string, int>();
                    foreach (var eventInfo in type.GetEvents())
                    {
                        if (!eventDictionary.ContainsKey(eventInfo.Name))
                        {
                            eventDictionary.Add(eventInfo.Name, new List<string>());
                        }  
                        eventDictionary[eventInfo.Name].Add(type.Name);
                    }
                }
            }

            using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\test\events.csv"))
            {
                var maxClasses = eventDictionary.Max(x => x.Value.Count);
                var header = "Event;";
                for (int i = 0; i < maxClasses; i++)
                {
                    header += "Class" + i + ";";
                }
                file.WriteLine(header);
                foreach (var item in eventDictionary)
                {
                    file.WriteLine(item.Key + ";" + string.Join(";", item.Value));
                }
            }
        }

Output see attachment.

In my opinion this is not the right way to detect event procedures, because all self-created events are ignored.