By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,045 Members | 1,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,045 IT Pros & Developers. It's quick & easy.

Programmatic Extraction of VBA Code attached to Form Controls in Access

P: 33
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
Mar 13 '14 #1
Share this Question
Share on Google+
7 Replies

Expert Mod 5K+
P: 5,397
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.
Mar 13 '14 #2

Expert 100+
P: 1,240
That sounds like a great tip. I have try that.

Mar 13 '14 #3

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

Expand|Select|Wrap|Line Numbers
  1. Dim modOpenModules As Modules
  2. Dim mdl As Module
  4. Set modOpenModules = Application.Modules
  6. For i = 0 To modOpenModules.Count - 1
  7.   Module_Name = modOpenModules(i).Name
  8.   If Mid(Module_Name, 1, 5) <> "Form_" Then
  9.     docmd.OpenModule Module_Name
  10.     Set mdl = Modules(Module_Name)
  11.     For n = 1 To mdl.CountOfLines
  12.       Debug.Print mdl.Lines(n, 1)
  13.     Next
  14.   End If
  15. 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
Mar 14 '14 #4

Expert Mod 5K+
P: 5,397
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.
Mar 15 '14 #5

P: 33
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
Mar 15 '14 #6

Expert Mod 5K+
P: 5,397
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.
Mar 15 '14 #7

P: 33
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
Mar 15 '14 #8

Post your reply

Sign in to post your reply or Sign up for a free account.