Placement of code
 

To what extent does it matter where my code is? That is, whether the
code sits behind a worksheet, or in a module, or behind a userform?

I assume that if I have controls on different objects with the same
name, the code behind that object will control it. For example, if I
have a "Show Instructions" button on a worksheet and a userform, both
with the name ShowInstructions, then the ShowInstructions_Click() event
behind the worksheet and the form will each respond to the button on
the worksheet or form.

But what about code in a module? Does VBA care if its in a module or a
worksheet? Does it share values in a given object but not between
objects? What exactly do the "public" and "private" statements do in
regard to variables, and to objects and events? In the programming I
have done, I've often been casual or careless about this stuff, and
have usually been able to get it to work, but I'd like to understand
better and make my programs better and cleaner, as well as more
functional.

Thanks in advance.

Placement of code
 

Hank Youngerman wrote:
To what extent does it matter where my code is? That is, whether the
code sits behind a worksheet, or in a module, or behind a userform?

I assume that if I have controls on different objects with the same
name, the code behind that object will control it. For example, if I
have a "Show Instructions" button on a worksheet and a userform, both
with the name ShowInstructions, then the ShowInstructions_Click() event
behind the worksheet and the form will each respond to the button on
the worksheet or form.

But what about code in a module? Does VBA care if its in a module or a
worksheet? Does it share values in a given object but not between
objects? What exactly do the "public" and "private" statements do in
regard to variables, and to objects and events? In the programming I
have done, I've often been casual or careless about this stuff, and
have usually been able to get it to work, but I'd like to understand
better and make my programs better and cleaner, as well as more
functional.

Thanks in advance.

Public means visible in all modules of the project. A private
declaration means that its visibility is restricted to that module. See
"Understanding scope and visibility" in the help file. Certain public
declarations *must* be in a general code module - including consts and
arrays.

I used to place almost all of my code in sheet modules, but recently I
have been trying (not always consistently) to put most of my code in
general modules an to reserve sheet modules, etc., to event-handling
code. To me this seems a bit cleaner. Furthermore, to keep the sheet
code modules as uncluttered as possible, I often have one statement
click_events that simply invoke a sub in a general module. This last is
of course a matter of taste and it is over-kill if the click-event code
would be short to begin with.

HTH

-John Coleman

Placement of code
 

"Hank Youngerman" wrote in message
ups.com...
To what extent does it matter where my code is? That is, whether the
code sits behind a worksheet, or in a module, or behind a userform?


It can be critical. If you wish to call the procedure from anywhere it
should be in a general code module. Of course, event code must be in the
class module that it pertains to, be that a workbook, worksheet, userform,
or custom class. Making the procedures Public allows them to be called, if
qualified by the object

Userform1.CommandButton1_Click


I assume that if I have controls on different objects with the same
name, the code behind that object will control it. For example, if I
have a "Show Instructions" button on a worksheet and a userform, both
with the name ShowInstructions, then the ShowInstructions_Click() event
behind the worksheet and the form will each respond to the button on
the worksheet or form.


As I showed above, every procedure is part of the class, so even if the
class isn't stated, it is implicit.


But what about code in a module? Does VBA care if its in a module or a
worksheet? Does it share values in a given object but not between
objects? What exactly do the "public" and "private" statements do in
regard to variables, and to objects and events? In the programming I
have done, I've often been casual or careless about this stuff, and
have usually been able to get it to work, but I'd like to understand
better and make my programs better and cleaner, as well as more
functional.

VBA may not care, but it requires more control by your code if calling from
elsewhere. My rule of thumb is if the code only pertains to that object, put
the code in that class module, if it is more generic, put it in a standard
code module.

Placement of code
 

Hank,

As general good coding practice, the only procedures that should be in the
object modules (ThisWorkbook, the Sheets, Userforms, and Classes) are the
event procedures for that object (e.g Workbook_Open in ThisWorkbook), event
procedures for controls on those objects (e.g., btnCancel_Click in a
Userform), Pubic Property Get/Let/Set procedures that you use to extend
these objects, and *perhaps* Private procedures used *exclusively* by the
other procedures in that particular module. In general, with the exception
of Property procedures (which are almost always declare as Public or
Friend -- a Private Property is of little use), nothing should be Public in
ThisWorkbook or a Sheet module. If you have to declare it as Public to get
it to work, it probably belongs in a regular code module not an object
module.

General-use procedures and procedures assigned to controls' "OnAction"
properties should go in regular code modules. You should split code in to
multiple modules, and give each module a meaningful name, based on common
functionality. All the code in each module should share some common purpose.
You are not limited in the number of modules in a project, so splitting code
into multiple modules make it easier to maintain, manage, and reuse in other
projects. In general, the more modules you have, and the more specific each
one is, the better.

For example, in large projects I do, I have a modStartStop module that
contains code that is used at startup and shutdown, a modMenus module that
deals with menu and command bars, a modConstants module containing public,
project-wide, constants, a modAPI where all Windows APIs are declared, a
modFileFunctions module with file-related procedures, and so on. By
properly designing the modules and giving them meaningful names, it make it
easy to reuse code in multiple projects as well and manage large amounts of
code in a single project.

Does VBA care if its in a module or a
worksheet?

Yes, VBA does care. If you put a procedure in a sheet module, that procedure
can be called only within the sheet itself or by prefixing the sheet's code
module name (not tab name) to the procedu

Sheet1.SomeProc() ' and in this case SomeProc must be declared as Public
or Friend

If SomeProc() does not deal exclusively with the Sheet1 object (and not
meaning that it deals only with data on the "Sheet1" worksheet), it doesn't
belong it the sheet's object module.

What exactly do the "public" and "private" statements do in
regard to variables, and to objects and events?

"Public", "Private", and one you missed, "Friend", all deal with the topic
called "Scope", and define from where that variable or procedure may be
accessed. A Private procedure or variable may be accessed only from within
the module that contains it. If you have a procedure or variable that is
declared Private you can use it only within that module. This also means
that you can use that name in other modules without conflict. This may not
or may not be advisable, depending on the specific context and use of a
procedure or variable. If it is Public, it is available to any procedure in
any module of the project (and any external project -- another workbook
VBProject --that may reference your project). If a procedure or variable is
Public, but in an object module (ThisWorkbook, the Sheets, Userform, or
Class), you still must prefix that variable or procedure name with the name
of the object module or class instance name that contains it in order to use
that procedure or variable. E.g.,

ThisWorkbook.SomeProc() ' SomeProc is declare as Public or Friend

If the variable or procedure is declared as Public in a standard code
module, its name must be unique across all other standard modules in the
project, and you can call that procedure or access that variable from
anywhere in the project.

The same procedure or variable name may be used in separate object modules
(ThisWorkbook, the sheets, Userform, Class). If it is Private it may be
accessed only from within that module. If Public it must be prefixed with
the module name:
Sheet1.SomeProc()

It is also possible to have Public procedures with the same name in regular
code modules. However, unless you prefix the procedure name with the module
name (e.g., Module.MyProc) you will receive a compiler error when you
attempt to access that procedure, because the compiler doesn't know which
module's MyProc you are trying to call. E.g., both Module1 and Module2 may
have a procedure named MyProc, and you can call either of these with code
like

Module1.MyProc
or
Module2.MyProc

but simply
MyProc

will raise an "ambiguous name" compiler error. It is consider poor
programming practice to have procedures with the same name in multiple
modules. Avoid it at all costs.

For documentation purposes, you may prefix the name of a Public property,
variable, or procedure within a regular code module with the module name
that contains it, but this is not necessary. However, it does provide an
nice level of documentation. E.g.,

Result = modArrayFunctions.SomeProc(1234)

I do this quite often in large projects. It serves for documentation only.
It is not required and it does not affect the performance or functionality
of the code.

In object modules (ThisWorkbook, the Sheets, Userform, or Class) you can
declare a property or procedure as "Friend" instead of "Public" or
"Private". In this case, the property or procedure is accessible from
anywhere in your project (of course, it must be prefixed with the object
module name or class instance name), but it is not accessible to external
projects (other workbooks) that may reference your project. ("Friend" I
believe, was added in VBA6, and is not available in Excel97 or earlier).

With the exception of Class modules, which in general MUST have Public
properties and procedures to be useful, object modules (ThisWorkbook, the
Sheets, Userforms) shouldn't contain Public procedures or variables. (Public
Get/Set/Let Properties are fine and can be used to extend these objects.)
If you declare something Public in ThisWorkbook or a Sheet module, most
likely it should be placed in a standard code module instead.

Private declarations are useful in regular code modules when you are
creating a module containing a single function or a small group of tightly
related functions that require certain constants, types, or Windows API
declares, and you plan on using that module in more than one project. By
declaring everything necessary for the function(s) to work (e.g., constants,
types, enums, declares, module-level variables, etc) as Private, you can
Import that module into other projects without worrying about whether those
projects have variables or declares or other elements with the same names.
Since you've declare them as Private within the module, they are visible
only within the module and won't conflict with names (declared as either
Public or Private) within other modules.

See "Understanding Scope And Visibility" in the VBA Help for more info about
Public and Private.


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


"Hank Youngerman" wrote in message
ups.com...
To what extent does it matter where my code is? That is, whether the
code sits behind a worksheet, or in a module, or behind a userform?

I assume that if I have controls on different objects with the same
name, the code behind that object will control it. For example, if I
have a "Show Instructions" button on a worksheet and a userform, both
with the name ShowInstructions, then the ShowInstructions_Click() event
behind the worksheet and the form will each respond to the button on
the worksheet or form.

But what about code in a module? Does VBA care if its in a module or a
worksheet? Does it share values in a given object but not between
objects? What exactly do the "public" and "private" statements do in
regard to variables, and to objects and events? In the programming I
have done, I've often been casual or careless about this stuff, and
have usually been able to get it to work, but I'd like to understand
better and make my programs better and cleaner, as well as more
functional.

Thanks in advance.