Here are some general guidelines for organizing VBA code. These have
served me well over the years:
First, the Sheet and the ThisWorkbook modules should contain ONLY
event procedures (
http://www.cpearson.com/excel/events.aspx). All
other code should reside in regular code modules.
ALWAYS use the Option Explicit directive to force explicit variable
documentation. This can save you hours and headaches when debugging
code.
Name your modules to reflect the nature of the code within. When you
are trying to track down some code, it is much easier to find it if
you have a module name like modFileFunctions rather than Module1.
Organize your modules to contain code of related functionality. There
is no (practical) limit on the number of modules in a project. If you
have procedures in a module that are called ONLY by other procedures
in the same module, declare them with the Private modifier.
It is better to have a large number of small procedures than a small
number of large procedures. This makes it much easier to test and
maintain a project. With small procedures, you can easily test them
and then be done with them and use them as needed throughout the rest
of the project code. It makes the code much easier to follow, debug,
and maintain if you have smaller procedures designed to accomplish a
specific task as opposed to a large procedure that tries to cover a
number of different tasks.. (I once worked for a software company
whose coding guidelines required that every procedure must be
printable on a single sheet of paper. This was very obviously
tremendous overkill, but the motivation was sound. Of course, if you
set the font small enough, you can get a lot of code on one piece of
paper.)
For add-ins and for projects that might possibly be reference by other
projects, change the project name from the default VBAProject to
something meaningful, like projFileUtilities.
Most of the updates to the worksheet can be combined in the same macro.
I would recommend that you not combine disparate functionality into a
single macro. This makes the code considerably more complex. Break
each distinct piece of functionality into separate procedures and
organize those procedures into separate modules.
If your code is well written, well organized, and well documented, you
gain the tremendous advantage of code re-use. Once a function is
written, it can be re-used within the same module and project, as well
as in other projects. Over the 12 years that I've been doing
VBA/VB6/VBNET programming, I have accumulated a library of about 200
modules (over 100K lines of code) that I can import into a project as
needed. I cannot overestimate the amount of time this has saved me.
For example, if I need to use the system registry, I just import my
modRegistry module and I have all the functions available, pre-tested,
and documented.
Code should be as self-documenting as possible. This goes beyond
adding comments to the code, although this is very important. Choose
your variable names and procedure names to be descriptive and to
identify what type of active the procedure carries out. For example,
all of my procedures that test for a condition begin with the work
"Is". For example, Function IsWorkbookLocked(). Similarly, functions
that return a setting begin with "Get". E.g., GetActiveProjectName().
Some programmers like to use what is called "Hungarian notation", in
which the first few letters of a variable name indicate the data type
of the variable. For example, a string might be named strWorksheetname
and an integer might be named iPageCount. I have never embraced this
style, but many programmers have, and on balance it is a good habit to
get into.
There is a lot of personal preference and style that goes into
programming, and every programmer has their own style. As you code
more and more, you'll develop your own style. Just be sure that it is
based on sound principles. The suggestions above are just that,
suggestions, not rules. They have served me well over the 20+ years
I've been a professional programmer.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com