Organizing VBA Code

[Joy M]

Hi -

Just wondering if there are any tips for organizing VBA procedures.

I divided the code behind the form into 15 sections, and assigned each
section a number.

At the beginning of the code, I have a little menu in the comments.
I can highlight the number of the section I want to go to and use CTRL+F to
get there.
I have sections for form events, control events, command buttons and action
queries.

Are their any other tips for managing code that goes on 'forever' behind a
form?

Thanks!

Joy

 

[Graham R Seach]

Joy,

Frankly I have little time to organise code behind forms, but such code
should not go on forever. My view is that if you have large amounts of code
sitting behind a form, most of it probably shouldn't be there. Commonly used
procedures and processes, and those that are not specific to *that* form
should be snipped out of there and put into a standard module or class (as
the case may be).

Standard modules should be organised, and for a very good reason. When
anything in a module is referenced (variables, constants, subs, functions,
etc), the whole module is loaded into memory, and remains there until the
application is shut down. If a module contains hundreds of declarations and
procedures, the whole lot gets loaded, even if you only use one variable
from it. This behaviour occurs for forms, reports and class modules too. Not
sure about macros.

For that reason, all modules should be kept as small as possible. You should
group procedures into modules, by some commonality, such as function or
purpose.

Of course, there is a tradeoff. Loading lots of stuff into memory takes
time, and can slow form loads, etc, but once loaded, performance of these
modules drastically improves. But, in extreme circumstances, loading lots
into memory can mean you have little left for your application (or other
programs) to run, slowing overall computer performance.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Joy M]

Hi Graham -

I used PageDown and there are 45 screens of information behind this form.
Do you think that is a lot?

It was my impression (from reading) that it is desirable to keep the code
behind the form, so that it will travel with the form if the form is ever
exported to another app.

I wouldn't know how to put my code into Standard Modules - it seems like all
code behind the form is necessary to the processing of the records.

It is an Invoicing program, BTW. In it, I use a lot of action queries. I
counted 47.
Each has its own error handler. This makes the code long.
Is it possible to combine several action queries into one procedure, with
one error handler for several queries?

I would like to know how much error handling is necessary in VBA.
Should I put it in every single module, or just where I try to break the
form, and access standard error messages pop up?

Thank you again for sharing your knowledge. The ng are a useful source of
infomation to me.

Joy