Hi Mark,

There are two broad types of separation that we need to keep
distinguished. One is the separation of the logical layers of an application
from each other (UI, business logic and data access). For example, you
should be able to change the implementation of your data access layer from
Excel to Access to SQL Server without ever needing to modify the code in any
other layer because the other layers don't know or care how the data is
stored.

Second, within a user interface built from Excel workbooks, worksheets
and charts (hereafter referred to as a worksheet UI) a separation of VBA
from Excel objects should also be maintained. This separation within the UI
layer doesn't apply to purely VBA UI constructs like UserForms because there
isn't any practical reason or method for doing it.

The way I handle the separation of a worksheet UI from its associated
VBA code is to create an intermediate class module that "represents" my user
interface to the business logic layer. This class module is part of the user
interface, so it has direct linkage to the names, number and structure of
the user interface workbooks and sheets. It is also typically a WithEvents
class so it allows you to perform event handling within the class module
rather than having to sink events directly in the document object modules
behind the workbook and worksheet objects.

In this class module I provide methods that collect worksheet UI data,
typically from named ranges on the worksheets, and transmit it into the
business logic layer through the use of UDTs. Again, the purpose of this is
so that no application layer has any intrinsic knowledge of how the data is
managed in any other application layer.

In many Excel applications, this UI handling class module and any
UserForm-based UI elements, are located in the same add-in workbook as the
business logic and even the data access layer. The important thing is not
that these layers be physically separated but that they be logically
separated. I have programmed many small Excel applications that consist of
two workbooks: a codeless UI workbook and an add-in that contains everything
else. Even though all three tiers of the application are physically
contained in the same add-in in these cases, they are still logically
separate and therefore reap the many benefits that result from this design.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Mark Driscol" wrote in message
oups.com...
On page 44 of the excellent Bullen/Bovey/Green book, "Professional
Excel Development," it states:

"Within the user-interface tier of many Excel applications lie two
unique subtiers. These consist of the workbook and sheet elements used
to construct the user-interface and the code supporting those elements.
The concept of separation should be rigorously applied to these
subtiers. A workbook-based interface should contain no code, and the
UI code that controls a workbook-based interface should reside in an
add-in completely separated from the workbook it controls."

In my application I have the following.

User-interface: a userform and a worksheet
Business logic: standard code modules
Data storage: a worksheet

There are certain ranges on the worksheet that forms part of the user
interface. If I put Get and Let properties in the code module behind
this worksheet that allow for data storage and retrieval, I can access
the ranges' data from my standard code modules (business logic). That
would eliminate i) declaring global range objects to be used among the
various code modules, or ii) passing range objects as arguments among
the macros that would use them. However, if I do put properties in the
code module for the user interface's worksheet, will I be violating the
principles cited above from the book? What is best practice? Will I
not be properly isolating the user interface from the business logic?

Thanks in advance.

Mark