Professional Excel Development book question

[Mark Driscol]

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

 

[Rob Bovey]

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]

Good morning Rob,

Thank you for your response. Let me ask for clarification.

Suppose I want to transfer the contents of a variant array to and from
a worksheet range. In my worksheet UI I could put properties such as:

Public Property Let ArrayValues(ByVal varArray As Variant)
Me.Range("Range1).Value = varArray
End Property

Public Property Get ArrayValues() as Variant
ArrayValues = Me.Range("Range1).Value
End Property

In my business logic, I could access the data contained in Range1 with
something like the following.

Sub GetArrayData()
Dim varArray as Variant
varArray = wksUI.ArrayValues
...
End Sub

The business logic doesn't know how the array values are determined; it
just knows it can access a property of the worksheet to obtain the
data. However, although this is separation, I understand your response
to mean this is not good programming. Can you help me understand why?
That is, if the way the data were stored on the worksheet UI, I would
only have to change the properties in the worksheet UI code module. I
would not have to change the business logic, but there must be a
problem with this that I do not quite see.

You mentioned defining a separate class module to access the range data
and other objects on the worksheet, let's say its code name is wksUI.
Can you give me a short example of the code that would define it and
how my business logic would use it to access a range contained on the
worksheet UI?

Thank you, I very much appreciate you helping me understand this.

Mark

 

[Mark Driscol]

That should have read "That is, if the way the data were stored on the
worksheet UI were changed,"...

Mark

 

[Doug Glancy]

Rob,

When you say "codeless UI workbook" does this mean that the associated addin
is always installed and checking each opened workbook to see if it's the UI
workbook?

Thanks,

Doug

 

[Rob Bovey]

Hi Mark,

There's nothing particularly wrong with the scenario you describe other
than the fact you'd be combining the VBA and Worksheet parts of your UI. The
primary reason for not wanting to do this is maintainability. It's much,
much easier to maintain and upgrade a Worksheet UI if you keep the Worksheet
and the VBA code associated with it in separate places.

I wish I could offer a simple example of the class module abstraction
approach, but with an example simple enough to fit in a newsgroup post it
wouldn't be clear why the approach was useful. In reality there's quite a
bit of code involved in a worksheet UI abstraction class. The stripped down
version in my code library that I use as the base for the worksheet UI
abstraction class modules I add to my own projects has 450 lines of code.
All very generic and reusable, but quite a bit of code nonetheless.

Let me show you a rough outline of what one looks like. In order, from
top to bottom:

- A list of worksheet CodeName constants. You always want to reference
worksheets in your UI by CodeName rather than by sheet name because users
will change sheet names on you and you may very well want to allow this.
These are all private to the class

- A public enumeration that provides a list of friendly names for each sheet
in your UI. This not only makes using the class easier, because you can
identify sheets by name, it also provides an additional layer of
abstraction. So if you had to distinct UI sections on one worksheet for
example, each would have an enumeration that identified it and the class
modules does the dirty work internally of figuring out what worksheet to use
for each enumeration value.

- One or more WithEvents Excel.Workbook variables that you use to keep
references to your UI workbooks and trap their events.

- Various Windows API declarations for reading and writing registry and/or
INI values associated with the UI.

- Property procedures to pass data in and out of the class module. Property
procedures are used for very uncomplicated data transfer that will not fail
and require error handling under any normal circumstance (and if they do
you're likely facing a fatal error anyway).

- Public methods (functions and subroutines) for performing UI actions and
complex data transfers that may fail under normal circumstances and
therefore require error handling and graceful recovery.

- Event procedures for any events you want to trap.

- Various private procedures that implement the abstraction the class
provides by doing things like translating CodeNames into sheet tab names
and/or worksheet references, translating enumeration values into sheet tab
names and/or worksheet references, retrieving and storing registry and/or
INI values, etc.

To answer your specific question, the property procedure you've shown
below would look essentially identical if it were located in a class module.
The main difference would that the class would have some mechanism for
returning a reference to the appropriate worksheet, either storing it in a
module-level variable that was initialized on start-up or providing a
function that dynamically returned a reference as needed.

--
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]

Thanks again, Rob, for taking time to explain this.

So, since I my data storage tier is also a worksheet in this same
workbook, I should have an intermediate class to interact with this
worksheet as well? Or, is this intermediate class just for the
interaction between the UI and the business logic?

Mark

 

[Rob Bovey]

When you say "codeless UI workbook" does this mean that the associated
addin
is always installed and checking each opened workbook to see if it's the
UI
workbook?

Hi Doug,

The add-in does not need to be installed and in most cases it won't be.
If you're building an application that integrates with the standard Excel
UI, your add-in does need a mechanism for determining whether it "owns" a
given workbook in order to prevent it from attempting to operate on
arbitrary workbooks the user may have opened. I typically do this by adding
a named constant to my application workbooks. If this constant exists then
my application knows it owns the workbook. If you're building a dictator
application this isn't really necessary because you're taking control of the
entire Excel UI and not allowing the user any possibility of opening
arbitrary workbooks.

--
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

 

[Rob Bovey]

So, since I my data storage tier is also a worksheet in this same
workbook, I should have an intermediate class to interact with this
worksheet as well? Or, is this intermediate class just for the
interaction between the UI and the business logic?

Hi Mark,

If it was my application that's how I'd build it. You never know when
you might outgrow your current data storage implementation and have to move
up to something more robust. Keeping the data access layer as abstract as
possible will make life a lot easier if you need upgrade to an Access
back-end at some point in the future for example.

--
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

 

[Doug Glancy]

Rob,

Thanks for the answer. I understand what you have described except for
this: in a non-dictator application with a workbook UI, if the UI workbook
doesn't install the addin and the addin isn't already installed and looking
for the UI workbook, what starts the application? Is it all called from an
executable?

I hope that makes sense.

Doug

 

[Rob Bovey]

Thanks for the answer. I understand what you have described except for
this: in a non-dictator application with a workbook UI, if the UI workbook
doesn't install the addin and the addin isn't already installed and
looking for the UI workbook, what starts the application? Is it all
called from an executable?

Hi Doug,

The applications I create are typically launched from an icon that I
place on the users desktop and start menu. But how an application starts up
really isn't an issue, as long as it does start up. Once a non-dictator
add-in starts up it typically begins monitoring events at the application
level (as well as doing a start-up check to see if one of its UI workbooks
is already open). As the user opens workbooks or switches among multiple
workbooks, events trigger the add-in to examine each newly opened or
activated workbook and if it owns that workbook take the appropriate action.

I guess I should have also clarified that there isn't necessarily just
one UI workbook. Sometimes there is, but in other applications the user can
create multiple versions of the UI workbook and often those can stand alone,
e.g. you could e-mail a copy to a user who doesn't have the application and
they'd be able to open the workbook for viewing purposes and even perform
minor data entry modifications that don't require the activity of the
add-in.

An alternate scenario for applications that use only one UI workbook
that can't be permanently modified is to create a reference from the UI
workbook to the add-in. This way, if you open the UI workbook first it
automatically opens the add-in workbook and if you open the add-in first its
start up code will open the UI workbook.

--
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

 

[Doug Glancy]

Hi Rob,

Thanks again. I've done pretty much everything you describe in the first
two paragraphs, in terms of multiple workbooks that are recognized by one
associated add-in - an add-in that creates and deletes menus as the "owned"
workbooks are activated and deactivated, etc.

But I'm still having a "chicken or the egg problem." I think I understand
that your non-dictator app icon typically opens the add-in. If that's the
case, what I don't understand is, when is the app finished and the add-in
uninstalled? I mean, as it's running in the background checking for
workbooks it owns, at what point does it stop doing so? I don't see how it
knows that it's finished. That's why I originally thought the add-in was
running all the time.

I appreciate your time (a lot!). This ties in with work that I've been doing
and is just very interesting anyways.

Doug

 

[Rob Bovey]

Hi Doug,

<<I think I understand that your non-dictator app icon typically opens the
add-in.>>

Yep, that's correct.

<<If that's the case, what I don't understand is, when is the app finished
and the add-in uninstalled?>>

The app can be made to finish in one of two ways. Closing Excel is
obviously the first one. I'll also typically provide an Exit toolbar button
or menu option that allows the user to close the application without closing
Excel.

--
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

 

[Doug Glancy]

That seems painfully simple <g>.

I think my difficulty in grasping was that I don't so much make applications
as sets of tools that work with specific spreadsheets, so the "end" is not
as defined as in a true appplication.

Thanks again for your time.

Doug