Microsoft Office Tutorials and References
In Depth Information
The spreadsheet model has permitted an in-depth analysis of Fr. Eﬁa’s event. It
has met his initial goal of providing a model that allows him to analyze the revenues
generated by the event. Additionally, he is able to ask a number of important what-if
questions by varying individual values of model inputs. Finally, the formal use of
sensitivity analysis through the Data Table tool provides a systematic approach to
variable changes. All that is left is to examine some of the convenient devices that
he has employed to control the model inputs—Scroll Bars and Option Buttons from
the Forms Control.
7.5.3 Controls from the Forms Control Tools
Now let us consider the devices that we have used for input control in Fr. Eﬁa’s
spreadsheet model. These devices make analysis and collaboration with spread-
sheets convenient and simple. We learned above that sensitivity analysis is one of
the primary reasons we build spreadsheet models. In this section we consider two
simple tools that aid sensitivity analysis: (1) one to change a variable through incre-
mental change control, and (2) the other, a switching device to select a particular
model condition or input. Why do we need such devices? Variable changes can be
handled directly by selecting a cell and keying in new information, but this can
be very tedious, especially if there are many changes to be made. So how will we
implement these activities to efﬁciently perform sensitivity analysis and what tools
will we use? The answer is the Forms Control. This is an often neglected tool that
can enhance spreadsheet control and turn a pedestrian spreadsheet model into a user
friendly and powerful analytical tool.
The Forms Control provides a number of functions that are easily inserted in a
spreadsheet. They are based on a set of instructions that are bundled into a Macro .
Macros as you will recall are a set of programming instructions that can be used
to perform tasks by executing the macro. To execute a macro it must be assigned
a name, keystroke, or symbol. For example, macros can be represented by buttons
(a symbol) that launch their instructions. Consider the need to copy a column of
numbers on a worksheet, perform some manipulation of the column, and move the
results to another worksheet in the workbook. You can perform this task manually,
but if this task has to be repeated many times it could easily be automated as a macro
and attached to a button. By depressing the macro button we can execute multiple
instructions with a single key stroke and a minimum of effort. Additionally, macros
can serve as a method of control for the types of interactions a user may perform.
It is often very desirable to control user interaction, and thereby the potential errors
and the misuse of a model that can result.
To fully understand Excel Macros, we need to understand the programming
language used to create them, Microsoft Visual Basic for Applications ( VBA ).
Although this language can be learned through disciplined effort, Excel has antici-
pated that the majority of users will not be interested, or need, to make this effort.
Incidentally, the VBA language is also available in MS Word and MS Project,