You have a lot of flexibility in the ways you can distribute code
within modules. I generally organize code as follows. First, the only
code that resides in a user form's code module is that code required
directly by the form -- that is, event procedures for each control.
Similarly, the only code that resides in ThisWorkbook or one of the
Sheet module is the event handling for the object, and those
procedures should generally just call other procedures located
elsewhere to do the real work. Keep the core logic out of ThisWorkbook
the sheet modules and user forms.
Everything else goes in other modules. Code is distributed amongst
several modules based on the code's function. General utility
functions in one module, data access functions in another, and core
business functionality in yet another module or two.
Procedures should be as generic as possible, allowing them to be
called in many circumstances without changing any code. All relevant
information should be passed in as parameters. Module- or
Project-scoped variables should be avoided as much as possible. If a
procedure relies on a value that is not passed in (and thus can be
modified by other code), you run the risk of unintentional side
effects that arise when you change a seemingly unrelated procedure
that modifies a non-parameter or non-local variable.
If your application works with specific a data type, the number of
instances of which may vary at run time, consider using a Class module
to encapsulate then entire function of the entity into a single
module. Use properties of the class to set the values that define the
class, and use methods of the class to carry out actions.
You can think of your code base as a set of Lego blocks, each
procedure being one block. Then, you create the application as a whole
as a matter of just building up one block upon another. If you do this
consistently and do it well, you'll find that you can easily re-use
code within your project and also in other projects. My standard
library consists of about 200 individual modules, each self-contained
and devoted to a specific task or set of tasks. and each is fully
tested. For example, if I need to access the system registry, I just
import my library's modRegistry module and then I'm done with it. No
more code to write. If I need XML functions, I just import my modXML
module and call upon its prewritten and tested functions.
Generally, a procedure in one module can call a procedure in any other
module. Unless the method is scoped as Private, it doesn't matter (as
far as the compiler goes -- organization is another question) where
the code resides. See
http://www.cpearson.com/Excel/scope.aspx for an
explanation of scope and the visibility and access of code and
variables within a project.
There is a limit to the number of lines in a procedure and to size of
a module. However, if you find yourself running up against those
limitations, then the code really needs to be restructured.
As a general rule, your code should tend to have a large number of
small procedures rather than a relatively small number of large
procedures. If you find that your procedures are over, say, 200 lines
of code, or you find that your code does the same operations in
several locations, then you should seriously consider rewriting the
code.
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
I have a user form with alot of code in it. As of now all the code is in the
Userform
Code window, but there is so much code that it's getting confusing. I have
one Sub that is to large and needs to be split into several subs.
When I was done I was going to break all the Subs down into different
modules to clean it up. I guess I will have to do that now, but I am not sure
how to do it.
1: My question is in the UserForm Code Window how do I refer to a module?
2: If the Code is to large can one Module call another module?
Something like User form Code calls Module 1 and at the end of module 1
code, it calls for module 2, etc....
3: Most of my Code is With statements / End With then more code.
Here is an Example of the Code in the UserForm Code Window:
Private Sub Update_Installer_Forms_10_Click()
With Workbooks("Master Installer Forms.xlsm").Sheets("Install Pack Con")
**Code for the sub is here**
End With
With Workbooks("Master Installer Forms.xlsm").Sheets("Install Chk List")
**Code for the sub is here**
End With
End Sub
I really need to sort this out so it's not so confusing to work on. Can
anyone please help me or explain it in english to me.
.