Programmatic Extraction of VBA Code attached to Form Controls in Access

[Phil Davis]

Hi, I am trying to pull together all of the VBA code I have written into a single document.

I have worked out how to programmatically list all modules and "print" the code contained within using the Modules Object but code that sits behind buttons etc within a Form is proving elusive.

Possibly a bit obscure and I guess I could copy and paste but that would be deeply unsatisfying!!

Many Thanks in anticipation
Phil

[zmbd]

Phil,
They started making it harder for VBA script to directly access the running code/script some years ago.

You can go in by hand and export each module.
You can use the database tools and the Database Documenter and selectively document the modules and forms.
You can use the CurrentProject.AllModules
loop thru each module, check for For Modules(strModule).CountOfLines, then loop thru the lines Modules(strModule).Lines appending to string our stdio file (see my insight article for stdio methods)

I usually just use the documenter tool as it covers enough for my company's needs.

[jimatqsi]

zmbd,
That sounds like a great tip. I have try that.

Jim

[Phil Davis]

Many thanks ZMBD, I found the following works for code contained within modules:

Dim modOpenModules As Modules
Dim mdl As Module
 
Set modOpenModules = Application.Modules
 
For i = 0 To modOpenModules.Count - 1
  Module_Name = modOpenModules(i).Name
  If Mid(Module_Name, 1, 5) <> "Form_" Then
    docmd.OpenModule Module_Name
    Set mdl = Modules(Module_Name)
    For n = 1 To mdl.CountOfLines
      Debug.Print mdl.Lines(n, 1)
    Next
  End If
Next
 

However modules with names beginning FORM_ had to be filtered out (as you see) because the OPENMODULE DOCMD would fail with a module not found condition!

I'd really like to be able to totally automate this process rather than wade through individual modules, forms etc but trying to locate the objects, methods or properties that enable it for "FORMS_xxxxx" type modules is not easy as you say! I do find navigating the MS documentation a challenge in itself!!

Best Regards
Phil

[zmbd]

Try to open the form first in design mode, might check to see if it's loaded first. Once the form is opened, you shouldn't need to filter it out. Close the form once the stripping is done.

[Phil Davis]

Hi ZMBD, an OpenModule request for a "FORM_module_name" fails with a module not found condition, so I guess there is something about FORM type module names that emerge from the MODULES collection that doesn't agree with the OpenModule method (Class modules vs Standard modules maybe - its still a vague area for me!).

But actually I am making progress with my understanding of the object system. You steered me toward the ALLMODULES and subsequently ALLFORMS, ALLQUERIES etc and the idea of collections in general to the point where I can now gather the names of code routines ([EVENT PROCEDURES] or SUBs) that are associated with form CONTROLs.

I suspect there must be some kind of object that represents these "[EVENT PROCEDURE]s" and can even see how I could create or modify such a procedure programatically using the INSERTLINES Method. Its the ability to read one that is still proving elusive!!

If I do succeed I'll post what I find here as it may be of interest to others from a purely acedemic point of view.

Once again, many thanks for your help.

Kind Rgds, Phil

[zmbd]

Just a thought:

Rather than re-invent the wheel here, The built in database documenter does a fair job of documenting everything you're talking about so far...

I've also seen some code and applications (one I thing was freeware, others shareware) that already strip and analyze this information.

[Phil Davis]

ZMBD, fantastic - thank you for that - I had never seen or used the documentor before. Looking in completely the wrong direction!

That will give me a solution for now if not entirely as slick as I was hoping for (a single click on a Form!). The documentor does show its possible to do and so I may keep pursuing it for purely acedemic reasons.

Best Rgds, Phil