How do I know if VBA code is called in an Access database?

Question

I am working with an outdated Access database with an interface and good VBA code.

The goal is to either replace the tool with a purchased product or with a web application (one day), and while I am making bug / fiscal year corrections, I would like to do a bit of cleaning if I can.

Is it possible to determine if subroutines and functions are used by the application (so I can remove those that are no longer in use)?

I know that I can export modules and class objects, but I see no way to easily check the actual forms.

Do I need to throw Debug.Print or breakpoints and then just bounce or search for forms individually or is there a better way?

Answer 1

MZTools does this.

MZ-Tools 3.0 is a free add-on for Visual Basic 6.0, Visual Basic 5.0, and the Visual Basic for Applications editor (provided by a VBA-enabled application, such as 32-bit in Office 2000-2013 except Office 64-bit), which adds many performance features to the IDE.

[Without affiliation.]

Answer 2

The following will search for all queries (including in RecordSource and Rowsource):

Public Sub CheckQueries(ByVal str As String) Dim qu As QueryDef For Each qu In CurrentDb.QueryDefs If InStr(qu.SQL, str) > 0 Then Debug.Print qu.Name End If Next End Sub 

Answer 3

OK, this answer will require some work and programming. I have only the beginning of the code that you will need, so you will have to do some research, as well as trial and error.

When you work in the VBA editor in Access, it is called VBE. It contains all the code in all forms of modules and reports. The debug.print lines are what you need.

If you go through a line and save it in the table, along with the name and / or name of the procedure (forms and reports) and save it in the table, you can then perform a compliance request to see which procedures are never called (they are listed as a procedure but not on any line of code.

Expect it to take several hours of your time to get better. But once you do this, you will have a good tool.

 Function GetVBEDeatils2() Dim vbProj As VBProject Dim vbComp As VBComponent Dim vbMod As CodeModule Dim sProcName As String Dim pk As vbext_ProcKind Dim iCounter As Long Dim ProcLines As Long For Each vbProj In Application.VBE.VBProjects 'Loop through each project For Each vbComp In vbProj.VBComponents 'Loop through each module Set vbMod = vbComp.CodeModule iCounter = 1 Do While iCounter < vbMod.CountOfLines 'Loop through each procedure sProcName = vbMod.ProcOfLine(iCounter, pk) If sProcName <> "" Then Debug.Print vbMod.Lines(iCounter, vbMod.ProcCountLines(sProcName, pk)) Debug.Print iCounter = iCounter + vbMod.ProcCountLines(sProcName, pk) Else iCounter = iCounter + 1 End If Loop Next vbComp Next vbProj Set vbMod = Nothing End Function 

Answer 4

Use the built-in Documenter tool. This is easier than opening the VBE editor (at least if you are new to this). And easier than using MZ-Tools, although it is a TERRIFIC tool.

See this post for specifics.