50
40
Advanced Modelling in Finance
It is important to match your usage of VBA to the task in hand, and to be selective
in the parts of VBA that you use, bearing in mind that spreadsheets do not provide the
answer to all questions. Using VBA subroutines to automate repetitive tasks is efficient
programming; using VBA to create your own simple functions is efficient; but trying
to program bullet-proof interaction for a novice user accessing a complex spreadsheet
model seems like the road to misery. Whilst seasoned applications programmers can
probably tackle this task, the pertinent question is should you? VBA is not a panacea
for all programming tasks, and should be used selectively since more efficient computing
methods exist for some computationally intensive tasks.
Excel’s macro recorder translates user keystrokes into VBA code and can be used to
suggest appropriate coding. While it is possible to use the recorder and remain ignorant
of the underlying VBA code, the aim of this chapter is to provide some explanations and
demonstrate practical applications, thus allowing you to become a more efficient user of
the whole Excel package. Probably the best approach at first is to use the recorder to
generate rudimentary code, then to selectively edit (or rewrite) the results as you become
more familiar with VBA.
It is important to be aware of the distinction between VBA subroutines and functions.
Whilst both are distinct, named blocks of source code, the raison d’
ˆ
etre of functions is
to return values. Typically subroutines accept no inputs, but they carry out a sequence of
spreadsheet commands (which may use values from cells in the spreadsheet, and change
the values in particular cells). In contrast, functions can accept inputs (or ‘arguments’),
they carry out a series of predominantly numerical calculations away from the spreadsheet,
and return a single value (or a single array). However, for both subroutines and functions,
the spreadsheet is the first development tool.
3.2 OBJECT-ORIENTED ASPECTS OF VBA
Afew concepts that you need to grasp follow from the fact that VBA is an ‘object-
oriented’ programming language. Each Excel object represents a feature or a piece of
functionality in Excel, e.g. workbooks, worksheets, ranges, charts, scenarios, etc. are
all Excel objects as is Excel itself (the Application object). You program in VBA to
manipulate the properties and apply methods to Excel objects.
Many statements can be made about objects and VBA, but essentially they can be
condensed into four rather cryptic statements. Textbooks and VBA help screens abound
with explanations, but overall, these tend to be confusing.
The first statement is Objects come in collections. For example, the Workbooks collec-
tion consists of all open workbooks, similarly the Worksheets (or Sheets) collection (all
the sheets in a workbook), the Scenarios collection (all scenarios associated with a partic-
ular sheet), the Charts collection (all the charts on a sheet), etc. However, some objects
come as singular objects (i.e. collections of one member only), for example, Excel has
only one Application object (itself) and for any cell on the spreadsheet there is only one
Font object (although this object has several properties, such as Name, Size, etc.). These
are singular objects that are referenced directly, e.g. by simply writing Application. or
Font.(theobjectfollowedbya‘fullstop’).Individualobjectsincollectionsarereferenced
by indexing the collection either by number 1, 2, 3... or by name, e.g.Workbooks(1).
orSheets(“inputs”). The Range object is by definition a singular object, but notice that it
is referenced in a way that is similar to that of a collection, either by name or by address,
e.g.Range(“data”). orRange(“A1:B20”).
