I'm new to VBA - why more than one Module?

[Harold Good]

Hi, I'm trying to learn how to be better organized with code. I can't
find anywhere why someone would want more than one module for general
code. Yet I see Module 1, Module 2, etc.

Is it because some code gets really long?
Is it to better organize code, even if not long?
How many modules do some of you pros end up with?

Any tips on how to organize code into various modules, renaming them to
be more meaningful, and organized.

Thanks,
Harold

 

[Otto Moehrbach]

Your last paragraph says it all regarding why more than one module. It is
done to organize things in your head. It is amazing how clear the code is
when you are writing it and how opaque it is a few weeks or months or years
later. It is also better (again, for your head) to break up your code into
smaller macros rather than have one long macro. HTH Otto

 

[Don Guillett]

And it helps to name the macros by having your cursor in the module and
touching the f4 key and then name it.

 

[Gord Dibben]

I have an add-in where I store most of my macros......frequently used and
some just for practice and those I glean off these news groups.

I have a dozen or so modules which I have given descriptive names so's I can
keep track of which types of routines are stored in each module.

Toolbars_Menus
Event_Codes
Protection
Range_Selections
User_Defined_Functions

You get the idea.

When the add-in gets a little large I simply export very unused modules as
*,bas files and keep them in a folder so's I can retrieve when needed.

To rename Module1 to something more descriptive, select and View>Properties
Window. Delete the Module1 name and rename.


Gord Dibben MS Excel MVP

 

[(PeteCresswell)]

Per Harold Good:

Hi, I'm trying to learn how to be better organized with code. I can't
find anywhere why someone would want more than one module for general
code. Yet I see Module 1, Module 2, etc.

Is it because some code gets really long?
Is it to better organize code, even if not long?

Both.

Also, there's portability.

For instance, one might have a module that is dedicated to
parsing and displaying errors. If that module contains all the
needed code - and only the needed code - the functionality can be
easily copied to other applications.

Excel is a concrete example for me. I've a module I call
"basExcel" and it has routines to open a new spreadsheet, open an
existing spreadsheet, make sure a tab name is legal, and so-on
and so-forth. Whenever I write a new app that has to do stuff
with Excel spreadsheets, I just copy that module into it.

How many modules do some of you pros end up with?

Depends on the app.

Right now I'm looking at a bond trading application that has 37
modules.

6 of them are unique to the application, but always present in
every app:

basAutoExec
basCalc
basGlobals
basReport
basTree
basValidate

21 of them are the same or very similar code in every
application:

basAppKill
basAutoExec
basBizDay
basBugAlert
basColor
basCommonFileDialog
basConnect
basEmail
basErrorCheck
basErrorCodes
basExcel
basFollowHyperLink
basGuid
basLogFile
basParseToArray
basScreenModeSet
basStickTo
basSubDataSheets
basTextWidth
basUtility
basWorkTables

Any tips on how to organize code into various modules, renaming them to
be more meaningful, and organized.

Portability is my primary concern.

I prefix module names with "bas", just because I like to be able
to tell what kind of object something is by looking at the name.
"tbl..", "qry...", "frm...", "rpt..." and so-on and so-forth.

 

[JLGWhiz]

Not to confuse VBA modules with modular programming, VBA modules are more
like a filing system for code within a workbook. There is no set rule as to
how the public modules should be used. Those are the ones numbered 1, 2,
3...etc. But there are also code modules behind the ThisWorkbook, behind
each Sheet and behind each UserForm. These latter three types of modules
are generally used for event code related directly to those particular
objects. The public modules hold the control code that executes programs
and functions. If you try to put a public procedure in a Sheet or UserForm
module, it probably will throw an error when you try to run it, and vice
versa with a sheet or form event procedure being put in a public module.

You can write modular programs by breaking a long procedure into several
small procedures where each program module performs a specific action and
all of the small procedures are called and controlled from one master
procedure. But these could all be in a public code module and in their
respective sheet or form code modules as applicable. The modular
programming simply means that you do not have one lengthy program which
requires consecutive events that might have the same code written repeatedly
over and over. With modular programming, you can use the same procedure
more than once simply by calling it to execute when you need it. Again,
don't confuse VBA code modules with modular progamming, even though there
might be coincidental similarity.

 

[(PeteCresswell)]

Per (PeteCresswell):

Depends on the app.

Right now I'm looking at a bond trading application that has 37
modules.

Oops!... RCI strikes again.

I predicated my response on this being the MS Access NG.

The portability thing is probably a red herring in Excel, since
one can point many workbooks at a single code repository.

 

[Harold Good]

Wow, what a helpful lot of responses. Thank you very much to each of you
who shared from your years of experience. It has been an eye opener to
me as I now think of some of the amateur code I've patched together, and
how understanding how to organize it better will be a giant step
forward. Some of the thick books I have on Excel VBA haven't explained
multiple modules very well.

Again, thanks for your help.
Merry Christmas to all,
Harold

 

[K_Macd]

There may actually be a physical limitation on the size of any module - 64K I
think but regardless of this the preceding responses are more important for
good code design. Why are all of us being so helpful? Simply that it's really
self-help as at some stage most of us have had to tidy up after individuals
who have written lengthy linear code all in one module.

 

[Bernd P]

Hello,

One small addition:

If you need to clean up somebody else's code and if that guy had not
used OPTION EXPLICIT (happens, unfortunately), you might want to
create a new module starting with OPTION EXPLICIT. Then you clean up
function by function and sub by sub: delete it from the old module and
create it in the new one.

Regards,
Bernd

 

[Harold Good]

These comments are all showing me I'd better learn how to do a better
job of it, lest others have to clean up my code down the road.

Thanks again for all your help,
Harold