Best Practices Question

[Barb Reinhardt]

What do you all consider to be best practices in VBA coding

1) Creating a called procedure with multiple arguments that can be reused
2) Creating multiple called procedures without arguments that are specific
to each time it's called?

I know what I think, but want to get your take on it.

Thanks,
Barb Reinhardt

 

[Charles Chickering]

Barb, it somewhat varies upon the application. In most cases it is best to
have re-usable code. The more time you can save yourself further down the
road the better. One instance I can think of where this might not be the
right option is in a function that is runs for a long period of time, you
then want to write all the code to be as fast as possible.

 

[Chip Pearson]

All else being equal (a rather broad assumption), I would write a single
procedure that takes either optional arguments or a variable number of
arguments using a ParamArray. The proc would take action based on which
arguments are included and what their values are. I think this makes the
proc much better suited to reuse in a variety of ways. The only caveat that
I would offer is that the function and its parameters must be very well
documented to allow for real reuse across multiple projects.

I have a rather large library of code that I include in projects either as
modules or as single procedures. I have reused the same code in many
projects over the years. Code reuse is a good thing. Of course, the code
must be well designed and documented, but it is worth the effort to get a
library of plug and play procedures.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Barb Reinhardt]

What about variable declaration? Would you make them all public or declare
them in the procedures as necessary. My preference is to only declare public
variables if they are needed.
Thanks,
Barb Reinhardt

 

[(PeteCresswell)]

Per Barb Reinhardt:

What about variable declaration? Would you make them all public or declare
them in the procedures as necessary. My preference is to only declare public
variables if they are needed.

I keep variables as local as I can. Only uses I can think of
off the top of my head for globals are:

1) General purpose constants like runtime error codes that I
can't find the proper enumerated variable names

2) Semaphores used by modal dialogs to tell the calling routine
that the dialog ended normally and/or to communicate values back
from the dialog.

3 Definitions of Lookup table values that are used across more
than one module.



On the passed parms thing, I see it as a tradeoff between
simplicity and maintainability. In general, I try to keep my
routines short and dedicated to a single function. I've got
plenty of them that exceed a 1900 x 1200 screen in size, but one
does, I start looking at it to see if I'm mixing too much
functionality into one routine.

Having a single routine perform a single function might involve
passing quite a few parms - like yield calculations, which are
performed differently using different types of data for different
security types. In that case, I'll pass a YieldTypeID and
whatever fields are needed to cover all calcs and then case out
on the ID - where somebody else might have a different routine
for each Case.

e.g. "Yield_Calculate() instead of a dozen different flavors
depending on YieldTypeID. The second approach is simpler in
that each function has less code in it. But I find
one-stop-shopping easier to understand/maintain and would
implement the first.

Others may see it differently.

 

[Chip Pearson]

What about variable declaration? Would you make them all public or
declare
them in the procedures as necessary. My preference is to only declare
public
variables if they are needed.

I don't use Public variables unless absolutely required and there is no
other way to accomplish the task at hand. In code I design for my libraries,
I might use a module-level Private variable, but only in cases when the code
is designed to be imported as a complete, stand-alone module. If a proc is
designed to be imported/copied in as only a single procedure, not a full
module, that code never relies on the existence of anything at all. Anything
it needs (e.g., a database connection, an FSO, or whatever) is passed in as
a parameter. In general, and there are exceptions, nothing should rely on
anything in its parent. Thus, a procedure should not rely on the existence
of anything in its module, and a module should not rely on anything at the
project level (such as another module). There are exceptions, of course, but
my general rule is to make everything as self-contained as possible. If
something is required by the proc, it should passed into the proc, not
declared as a Public variable.

One exception that comes to mind are constants that are used throughout a
project, things like C_APP_NAME, C_APP_VERSION, C_MSGBOX_TITLE and so on,
things that you want to keep constant throughout a project.

In much of my work, I import complete module files into the project to
provide support functions for the primary code. For example, I have a bas
file named modArraryUtilityFunctions that contains around 50 array-related
utility functions. I import the entire module to the project. Since the
procedures call one another, it is not practical to import only individual
procedures from the file.

All this is not to say that Public variables should never be used. There are
cases in which it makes good sense to use a Public variable. For example, if
you are designing an app centered around a database connection, say to SQL
Server, it would make sense to have one Public variable, Public TheDataBase
As ADO.Connection, and have all the code reference that public variable,
rather than having to pass around a reference to the database from one proc
to another.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Tim Zych]

Just to add, I think that the term that is most applicable here is called
"coupling", which is how closely related a function/procedure is to another.

The more tightly coupled, or more dependent a function is on another, the
less reusable and flexible it is.

My opinion is loosely-coupled is better (as I gather you already agree).