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)


"Barb Reinhardt" wrote in message
...
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



"Chip Pearson" wrote:

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" wrote in
message
...
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