Can you add Option Explicit or Error Handling to several modules at one time? (1 Viewer)

mulch17

Registered User.
Local time
Today, 06:41
Joined
Nov 5, 2015
Messages
30
I've been tasked with adding features to a database that I recently inherited from another company. The reason I'm taking over is because that company has been "relieved" by our customer. Once I opened this file for the first time, let's just say it didn't take me 2 minutes to start forming the picture of why they were relieved. This database is....sub-par.

This file at least 50 modules and 200 subs/functions (conservative estimate), and none of them have Option Explicits, Dim statements, or Error Handlers (no wonder they reported so many reliability issues). I understand how to fix this for each individual module, but it would be extremely tedious and time-consuming to do this by hand for every single module.

Is there a quicker way to automate this process (or at least some of it)? Is it possible for VBA to make changes programmatically to other sections of VBA code in other subs/modules?

I checked the box under Tools -> Options to require variable declaration, and it solves the Option Explicit problem for new modules, but it doesn't appear to apply that change retroactively to pre-existing modules.

Does this make sense? Are these valid questions/criticisms, or am I way off the mark? Thanks!
 

Ranman256

Well-known member
Local time
Today, 06:41
Joined
Apr 9, 2015
Messages
4,337
you dont have to add these. Access can run them all without Dim or Option explicit.
You would only need to add them IF the code is not working, where youd need to track down the cause. THEN Option explicit comes in handy.
 

mulch17

Registered User.
Local time
Today, 06:41
Joined
Nov 5, 2015
Messages
30
Thanks for the quick reply at this hour!

I'm aware that I don't *have* to add them to make the code run, but unfortunately they've documented several scenarios where their code gives various run-time errors (and god knows how many other failures still lie ahead that nobody has gotten to). So I would still like to use those features if possible.
 

Minty

AWF VIP
Local time
Today, 10:41
Joined
Jul 26, 2013
Messages
10,353
You could do a global Replace of Option Compare Database with Option Compare Database ¬CR¬ Option Explicit

Think it would work ?
 

Orthodox Dave

Home Developer
Local time
Today, 10:41
Joined
Apr 13, 2017
Messages
218
The global replace suggested by Minty will set all your code to Option Explicit. This will then of course throw up a multitude of errors if / when you compile the database. I suggest you only hit the compile button when you have several hours to spare to fix all the errors!

Alternatively you could just add Option Explicit module-by-module and compile them one module at a time.
 

mulch17

Registered User.
Local time
Today, 06:41
Joined
Nov 5, 2015
Messages
30
I'm embarrassed I didn't think of that idea myself. Simpler is always better. :D

That's within a hair's breadth of working. It doesn't look like ¬CR¬ is the line break character in the VBA editor's Find and Replace dialog unfortunately. Any idea what else it might be?

I've tried searching, but I haven't found how to do it *to* VBA. All the results assume I'm asking how to do it *with* VBA (e.g. Replace("foobar","foo","foo" & vbCrLf)), which isn't what I'm looking for.

It appears that Word's Find and Replace dialog box could do this using "^l" instead of "¬CR¬" (https://support.office.com/en-us/ar...document-c6728c16-469e-43cd-afe4-7708c6c779b7 - 2nd to last group from the bottom), but that doesn't apply to Access unfortunately.
 
Last edited:

Orthodox Dave

Home Developer
Local time
Today, 10:41
Joined
Apr 13, 2017
Messages
218
v-Tools is a useful add-in for Access that includes global find and replace. See this thread: https://access-programmers.co.uk/forums/showthread.php?t=171921

If you can't use the carriage return in the Replace part, you could use:
Code:
Option Compare Database: Option Explicit
The colon (":") with a space after it can be used to separate commands without putting them on the next line.
 

Minty

AWF VIP
Local time
Today, 10:41
Joined
Jul 26, 2013
Messages
10,353
As an alternative to Dave's excellent suggestion, if you simply copy

Option Compare Database
Option Explicit

And paste that into the Replace box it should work.

I confusingly used ¬CR¬ to "Represent" a carriage return .. my bad.
 

isladogs

MVP / VIP
Local time
Today, 10:41
Joined
Jan 14, 2017
Messages
18,186
you dont have to add these. Access can run them all without Dim or Option explicit.
You would only need to add them IF the code is not working, where youd need to track down the cause. THEN Option explicit comes in handy.


I take issue with that.
Preventative medicine is better than invasive surgery

Whilst strictly true, ranman's comment is very bad advice as it will lead to lots of unnoticed errors which can then be very hard to pin down.
The response by mulch17 was the correct one.

mulch17 said:
I'm aware that I don't *have* to add them to make the code run, but unfortunately they've documented several scenarios where their code gives various run-time errors (and god knows how many other failures still lie ahead that nobody has gotten to). So I would still like to use those features if possible.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:41
Joined
Feb 28, 2001
Messages
26,996
For existing modules, particularly with form/report class modules, this would be a one-time operation anyway. A manual COPY/PASTE would work with a FIND in the code window. Inventing some code to do it would take FAR longer to design and debug than to just do it.

Just remember to add the Option Explicit every time you create a new module, then do the one-time manual replacement, and you are done.

BTW - I'm a FIRM believer in letting the computer do my work for me - but I draw the line at writing a complex set of lines of code to be designed, tested, and somehow triggered by some other item in the DB vs. doing a manual operation and being done with it.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:41
Joined
Jan 23, 2006
Messages
15,361
mulch17,
I agree with most of the comments ---option explicit, Dim all variables, and Error Handling. You didn't mention which version(s) of Access you are using, nor any related data naming conventions or the applications/databases you had prior to this "acquisition". Adding Option Explicit to existing modules will generate messages if there are no Dim statements. If you are using Option Expliit in your other databases, you probably realize you can set a parameter to have Option Explicit as defaul (for new modules).
You might find that the MZTools utility would be helpful to your project and routine operations. They do offer a free trial period, but the tool is well respected and used by many.

Good luck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:41
Joined
Feb 19, 2002
Messages
42,970
Been there. Done that. Have the scars to prove it. I agree with Doc and Dave. Unless you have a very high threshold for pain, just do the modules manually one at a time so you can fix the compile errors as you go. Globally adding the Option Explicit might seem more efficient but will create chaos with all the compile errors it will generate.

PS - compile first to get a baseline.
 

Users who are viewing this thread

Top Bottom