Introduction
Designing
Spreadsheets can be a very personal thing. Some organisations have ‘standards’
but quite often they are kept in the bottom draw of the third filing cabinet on
the right in the cellar and tend to be ignored. Even if they are readily
available in an online electronic filing cabinet the chances of them being read
are slim, and updates to allow for the new features of the latest release of
software are usually left until tomorrow, next week or next month, by which
time another new release has come along and the standards are out of date.
Consequently there are as many different ways of doing the same thing as there
are people doing it, and the nice thing about standards is that there are so
many to choose from.
Desktop
Applications are strange creatures because although the responsibility for
evaluation and installation of them will be in the ‘IT Department’, the use of
them will be spread far and wide. Any system developed in a given department by
users will be that department’s responsibility and support from the IT
Department will usually be on a best endeavours basis. The end result in any
large organisation is that there is very little sign of consistent look and
feel across desktop applications, even when they are written in a common
environment such as a spreadsheet application.
This organised
chaos becomes obvious when one is engaged to do a Lotus 1-2-3 to Microsoft
Excel conversion. There are always a fair number of customized menus,
spreadsheet icons and local fashions. By local fashions I refer to the
spreadsheet where the front sheet of a spreadsheet file is a mass of someone’s
favourite colour with a collection of buttons down one side and miscellaneous text,
usually writing credits, tucked away in the corner. Occasionally there is the
hint of a corporate standard but more often than not it’s one person’s design
that has been perpetuated wherever it could be.
When you are
faced with different styles emanating from different departments as you move
through a conversion exercise you have to decide to what degree you are going
to maintain the current look and feel, whilst at the same time avoiding
reinventing the wheel with a different number of spokes in each. Clearly if it
is possible to come up with a standard way of doing things then you can save
some time and create something that might last beyond the end of the
conversion. You also might create something that is maintainable by someone
other than the original author.
Microsoft Office, and in
particular Excel, gives us two alternative ways to customise our environment.
These are Custom Menus and Custom Icon Bars.
The word
‘Custom’ will send some people running for their standards and best practice
manuals to find as many reasons as possible for not using either of these two
features. Don’t worry this is not a movement towards total anarchy. The aim is
to encourage a standard way of implementing customisation. Many organisations
restrict how a user can customise their Desktop, and whilst some allow degrees
of customisation, many are reset at ‘Login’. The advantage of producing
customisation within a workbook is that it lives with the workbook and
disappears when the workbook is closed.
The important
thing to consider when deciding whether to customise Excels Menus or the Icon
Bars, is that changes to the menus are changes made to Excel at the Application
session level, whereas changes to the Icon Bars can be attached to the
individual Workbooks and therefore can be activated or de-activated at the
Workbook level.
The methods
described here provide a consistent way of implementing Add-Ins with Custom
Menus to access the Add-In features, and also a method of producing Custom Icon
Bars that is both consistent and flexible. The benefits don’t stop there.
Probably the main reason why the concept of customisation causes such concern
is the level of extra work that maintaining any customisation is going to
involve. Of course any user macros and Add-In code will need to be maintained
but the real strength of the methods described below is that they are more or
less maintenance free and can be used as a standard way of implementing Add-Ins
or fixed processes.
Any of us who have been programming for any time are using some sort of
coding standard. In VBA the most commonly used appears to be Reddick VBA (details available at www.xoc.net). However these only cover the conventions
used in the code itself they do not give you any guidance on how to organize
the code. If you record a macro in Excel it will put your code in a code module
that is named by the system as ‘Module1’. Many people leave this as is, a few
will give it a meaningful name from a user perspective and some will giving it a meaningful name from a programmers point of
view.
For those that have either not looked under the bonnet of Excel or, have looked and do not understand what they see, Excel has a
Class module for the Workbook called ‘ThisWorkbook’
and a Class module for each sheet in the workbook. These worksheet Class
modules retain the names ‘Sheet1’, ‘Sheet2’ and so on even if you have renamed
the sheets in your Excel Workbook environment. Then there are the modules you
add yourself that become Module1, Module2 and so on. In the spirit of Reddick it is helpful to adopt a naming convention for
modules that suits the various techniques that you are going to employ. The
following is the basis on which we develop our Excel solutions;
Having derived what may seem to be an elaborate way of organizing code
it is necessary to have a way of working out what code exists in which modules.
To do this we will need three functions;
Details of these functions appear in the modules area of this site.
This is not the
place to go into a lengthy discussion about how and why organisations feel the
need to develop Add-Ins. It is sufficient to say that Add-Ins tend to be
produced for two main reasons. The first is to include a process that is
undertaken on a regular basis by a number of people on a consistent set of
data, the second is to provide local functions that are not covered by a built
in function. Variations on the NETWORKDAYS function are obvious examples of
such functions.
Custom
functions do not need menus so the type of Add-In under discussion is the first
of the above. Many people write processes and then replicate them from one
workbook to another either by copying the module or by inserting the code into
a template and then replicating it. Turning a tried and tested process into an
Add-In is the obvious thing to do but probably the most difficult to achieve.
This is partly because of the level of expertise of people writing the code.
Users tend not to have an understanding of the advanced capabilities of VBA,
and the documentation available is ambiguous. The Web is a great source of
technical information containing at least three different ways of implementing Add_Ins all of which use hard coded references to the
processes that have been written.
The objective
must be to find a method of turning standard processes into Add-Ins in a manner
that is consistent and requires no additional effort to implement. That does
not mean that the process we have automated does not need maintaining, it means
that the method we use to implement our process never changes, and perhaps more
importantly, we use the same method to implement a number of different
processes.
Using the
coding standards outlined above, and having thoroughly tested the code that is
the process to be turned into an Add-In, the code is placed in a module called
‘basAddin’ in a new single sheet workbook. This code
may have just one Procedure or it may have many. It does not matter because the
number and names of procedures in a module can be determined using the ‘ProcNames’ function mentioned above.
The next issue
to address is where to put the menu items. There are two possible options;
Adding a menu
for each Add-In will extend the width of the WorkSheet
Menu Bar. If we add another Menu for each Add-In we may lose control and they
may not always appear in the same place.
Adding items to
an existing Menu expands the length of that menu. However, since the Office
Suite reduces these to list the most used it will not have any great impact on
the overall look and fell. If we add items to a menu they will always appear in
the same place.
The conclusion
therefore is that we will disturb the look and feel less if we add items to an
existing menu because by adding menu items, only the menu we add items to will
look different . The ‘Tools’ menu is probably the most
appropriate place to add items. Remember, our objective is to use Add-Ins in a
consistent manner, so if we always add them to the Tools menu, as long as our
users understand that they are using an Add-In, they will always know where to
find the menu items.
Now we have our
process code, we have decided where we are going to place the menu items and we
have the ‘ProcNames’ Function
to convert our processes into an array of menu items. All we need now is
somewhere to put the code that ties this all together. Excel provides us with
an ideal place for this code as part of the Workbook Class module. Two of the
events that are allowed for in the Workbook class module are the “Workbook_AddinInstall” and the “Workbook_AddinUnInstall”
events. Details of this code appear in code section of this site.
It is possible to create an
Icon Bar manually and associate it with a given Workbook. However, anyone who
has played with customising Icon Bars will have learnt that whilst they are
fairly simple to create, attaching them to a particular workbook so that they
are always present regardless of which desk you are sat at is not so simple.
The problem is that even when you attach an Icon Bar to a workbook, it does not
live inside that workbook. What happens is that the workbook holds a pointer to
a file in your user profile. Depending on local implementations there is no
guarantee that a user profile will travel to a different desktop. Even if the
Icon Bar does travel it may remain on the alternative desktop as an orphan.
This frustration is what led to the development of a solution that lives inside
a workbook, builds the icon bar whenever the workbook is opened, deletes the
icon bar when the workbook is closed, and does not need to be changed each time
another Macro is added to the workbook.
According to our coding
convention there may exist a module called ‘basWorkbook’
that may have any number of procedures in it. Additionally there may be a
number of modules with names that are aligned to our sheet names and which may
each have any number of procedures in them. Our ‘ProcNames’
function gives us an array of the procedure names in any given module. In
addition a function is required that gives us an array of module names that
have matching sheet names (‘ModNames’) and this
function will need another function (‘ModExists’)
that tells it that a module of a given name exists. The code for all these
functions is in the code section of this site.
As stated above it is
required that our Icon Bars are created as the Workbook is opened and deleted
as the Workbook is closed. It seems fairly obvious therefore that the ‘Workbook_Open’ event and the ‘Workbook_Close’
event are going to be used to achieve this objective.
The code in the ‘Workbook_Open’ event will decide whether the ‘basWorkbook’ module exists, interrogate it to establish
what procedures exist in that module and build an Icon Bar with the appropriate
number of buttons on it. The next stage is to examine the other modules and
build an array of Sheet names that have valid module names according to our
module naming convention and then build an Icon Bar for each sheet that has a
module containing procedures.
Similarly the code in the ‘Workbook_Close’ event will do much the same but instead of
creating Icon Bars it will delete them. The code required is simpler because it
is not necessary to delete the buttons one at a time as we do when we create
the Icon Bar, here we simply delete the Icon Bars and the buttons disappear
with their container.
At this point there will be
a Workbook Icon bar that gets built when the Workbook is Open and is removed
when the Workbook is closed, and a collection of Sheet based Icon Bars that
simply exist. If another Workbook is opened in the same Excel session then the
Workbook Icon Bar will remain visible regardless of which Workbook has focus
and if we have made visible any of the Sheet Icon Bars then these also will
remain visible. What is required is code in the ‘Workbook_Activate’
and Workbook_Deactivate’ events to manage the
Workbook Icon Bar and also code in the sheet activate and deactivate events for
all the sheets that have Icon Bars to activate/deactivate those Icon Bars.
The code for the Workbook
events is straightforward and will permanently reside in those events. All that
is required is for the activate/deactivate events to establish that a Workbook
Icon Bar exists and then make it ‘visible’, or not, as the Workbook is activated
or deactivated.
Since the Sheet Icon Bars
are more dynamic, in that they are dependent on the modules being present and
containing code, it is necessary to have a method of writing the code for each sheets activate/deactivate event. The code we require in the
sheet activate/deactivate events only consists of the necessary statements to
hide or show the appropriate Icon Bar for the sheet that has just been
activated/deactivated. Our code only needs to build an array of module names
that have matching sheet names, which we already have, and then to write the
necessary Hide/Show statements in the Activate/Deactivate events for each sheet
in that array.
Finally it is necessary to provide
code to cater for one more situation. When a workbook
is deactivated the active sheet deactivate code is not run. Therefore it is
possible for a sheet based icon bar to be visible whilst the sheet to which it
refers does not have focus. We have handled this situation in our workbook
activate/deactivate code by reference to two routines Show_Sheet_IBs
and Hide_Sheet_IBs.
Although we have published
the code for the Functions mentioned, and the code for the Add-In routines we
are not publishing the code for the Icon Bar management. The reason for this is
that although the code is reliable when used ‘as is’, it is important that the
implications of using the code are fully understood. We are happy to provide
solutions based on our code and will also provide training and/or support for
whatever solutions we develop.
So we now have code that we
can use to implement any AddIn that may be written.
We also have code that we can put into any workbook whose code conforms to our
standards for locating code, and create Icon Bars to activate those macros. As
long as we adopt the same conventions for storing code in modules across all
our workbooks we can migrate the Workbook event code,
the Sheet event code and Add-In code to as many Workbooks as we want and our
Menus and Icon Bars will all appear in a consistent way. All that is left to do
is to make sure that our AddIns and Macros do the job
they were intended to, but that’s another matter.
Credits
Thanks are due to John Walkenbach (www.j-walk.com)
for providing the inspiration to undertake the above and Chip Pearson (www.cpearson.com) for showing me the way to
do it both through their web sites not through any personal contact. I would
also give credit to Bill Coan, again through his web
site (www.wordmacros.com), and the MVP
Word site (www.mvps.org/word) for
showing me that although the Excel MVPs may have more
experience they may not know all the answers.