How can I find whether VBA code is called in an Access database?

Question

I'm working with a legacy Access database that has a front-end and a good amount of VBA code.

The goal is to either replace the tool with a purchased product or a web application (one day), and while I'm in making bug/fiscal year fixes, I'd like to do a bit of cleanup 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 used)?

I know I can export the modules and class objects, but I'm not seeing an option to easily check the actual forms.

Do I have to throw Debug.Print or breakpoints and then just bounce around, or search the forms individually, or is there a better way?

Whenever I'm working with VBA and I open up the form's code in the editor, it automatically opens the form in design view in Access. Not sure if that helps? — Grant, Jun 17 '13 at 22:06
I sure hope you get a good answer to this question. I have the same problem, and not much hair left. I've wasted huge amounts of time fixing code that wasn't being used anymore, because I just couldn't be certain it was unused. — Brian.D.Myers, Jun 17 '13 at 22:08
@TKEyi60 - I actually don't experience that, but even if I did unfortunately one of these has 40+ fields, and a good number of them have events associated with them. I suppose I could click on each field, but ... I'd rather keep it as-is and let my future self or someone else deal with it. :D — James Skemp, Jun 17 '13 at 22:22
@JamesSkemp, The best I've found is the Debug.Print thing. But I'm not a Access Guru. There's plenty of people who know it way better than me. Keep your fingers crossed. — Brian.D.Myers, Jun 17 '13 at 22:25
I am confused. You said "Is it possible to determine if subroutines and functions are used by the application?" but you said earlier it already has a lot of VBA code? Are you not seeing the code? Is it protected or something? Please elaborate on what you're trying to do. Access the code or change it? Have you tried CTRL-F11 (or ALT, not sure) to view the code? Also, to view forms in Access just select the forms from the modules on the left of the Access database when you open up the code (if you can). — Fandango68, Jun 18 '13 at 00:57
@Fernando68 - Sorry, should have repeated what I wanted to do again in my question. I want to do a bit of cleanup if I can. So I can see them in the code, but I don't know where they're used, short of going through each form. EDIT: I've tweaked my 3rd paragraph accordingly. — James Skemp, Jun 18 '13 at 13:30

score 1 · Answer 1 · answered Jun 18 '13 at 02:45

1

MZTools does this.

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

[No affiliation.]

answered Jun 18 '13 at 02:45

Mitch Wheat

295,962
43
465
541

This callers withing other VBA code, but not queries, record source, or control source. But combined with @E Mett's answer it should work. – Tom Collins Jun 18 '13 at 17:01
Does this search Form Events? Based upon the site it doesn't appear that it does, but if you've used it ... – James Skemp Jun 19 '13 at 14:25
Note: From the FAQ, as of this writing--"Q: Are the old freeware MZ-Tools 3.0 versions still available for download? A: No, MZ-Tools 3.0 has been replaced by MZ-Tools 8.0 and it is no longer available for download." http://www.mztools.com/v8/faq.aspx . I should note that MZ-Tools 8.0 still seems to provide support for "VBA Editor of Microsoft Office (64-bit & 32-bit)"--it just seems you will need to purchase a license, now (currently $80, for a single license). – iokevins Dec 31 '15 at 19:00

score 1 · Answer 2 · answered Jun 18 '13 at 08:37

1

The following will search all queries (including ones 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

answered Jun 18 '13 at 08:37

E Mett

2,272
3
18
37

See my comment under @Mitch Wheat's answer. – Tom Collins Jun 18 '13 at 17:01

score 0 · Answer 3 · answered Jun 18 '13 at 02:35

OK, this answer will require some work and programming. I only have the beginnings of the code you'll need, so you'll have to do some research, along with some trial & error.

When you're working in the VBA editor within Access, it's called the VBE. This holds all the code in all the modules forms, & reports. The debug.print lines are what you're after.

If you go line by line and save it to a table, along with the procedure name & and object name (forms & reports), and save it to a table, you can then do a no match query to see what procedures never get called (they're listed as a procedure, but not in any code line.

Expect this to take a few hours of your time to get right. But once you do, you'll have a nice 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

score 0 · Answer 4 · edited May 23 '17 at 11:44

0

Use the built-in Documenter tool. This is easier than poking open the VBE editor (at least if you're new to that). And easier than using MZ-Tools, although it is a TERRIFIC tool.

See this post for specifics.

edited May 23 '17 at 11:44

Community

1
1

answered Jun 18 '13 at 03:20

Smandoli

6,919
3
49
83

In the few minutes I had yesterday I experimented with this first (since it was at the top at the time) and while this is very cool, I'm not sure it's documenting what I'm looking for. For example, I know `originals_afterupdate` is being used by the originals field for the AfterUpdate event. But all I get in my export is `AfterUpdate: [Event Procedure]` for the form, and `originals_afterupdate` as a sub. Maybe I'm missing something? Or do I need to play with the export a bit to get what I'm looking for? – James Skemp Jun 19 '13 at 14:17
I can tell you that if you check everything, the result is exhaustive. I gather you know you'll find what you need in VBA code; in this case you would go through the options, requesting VBA and unchecking anything else. (Maybe treat macros as VBA.) Nothing will be left out. – Smandoli Jun 20 '13 at 00:23

How can I find whether VBA code is called in an Access database?

4 Answers4