47
48
Advanced Modelling in Finance
3.4.1 Variables and Data Types
In programming, variables are used to store and manipulate data. Conventionally, data
comes in different forms or ‘types’ and this influences the amount of memory allocated
for storage. In most numerical calculation, the main distinctions are between numbers
(conventional decimal system numbers otherwise known as scalars), integers, Booleans
(True or False), and dates. There are also string variables that hold textual as opposed to
numerical information. VBA has a useful data type, variant, which can represent any data
type. It is the default data type, which can be useful when a variable’s type is uncertain.
However, the variant type consumes additional memory, which can affect processing
speeds if large amounts of numerical processing are required. There is also an ‘object’
variable type which can refer to an object such as a range or a worksheet. See Green
(1999) for more details of the different data types.
From the viewpoint of novice VBA programmers, it is important to explicitly declare
all variables used, as this cuts down errors in coding. This can be forced by ensuring
that the Option Explicit statement is included at the top of each Module sheet. (The VBE
can be set up to ensure that this happens automatically–via Tools then Options on the
VB menu, then checking the ‘Require Variable Declaration’ code setting.) At run-time,
the VBA compiler produces an error message if any undeclared variable is encountered.
This is particularly useful in detecting mis-spelt variable names in the code. Declaring
the data type (as integer, string, Boolean, etc.) is less important in the financial modelling
context, but it can be used as a way of indicating (to the user) a variable’s purpose in
the program.
Variables are declared with the Dim keyword as shown in the third statement of the
Factorial subroutine. Here, the two variables have been declared but not data-typed (as
integer say). By default they are variant in type:
SubFactorial()
’calculates the factorial of a number
Dimfac, num
num = InputBox(“Enter number ”, “Calculate Factorial ”)
fac = Application.Fact(num)
MsgBox “Factorial is ”& fac
EndSub
(As an aside, notice that InputBox(), which returns the user’s input, has two input param-
eters or ‘arguments’ here. The first argument is the prompt ‘Enter number ’, the second
(optional) argument is the title of the dialog box.)
In summary, all variables used in subroutines should be explicitly declared using the
Dim keyword. The statement Option Explicit at the top of a Module sheet ensures that
all variables must be declared for the subroutine to work correctly.
3.4.2 VBA Array Variables
Where sensible, variables can be grouped by name into arrays (vectors or matrices). For
example, the quartiles of the cumulative distribution of a data set can be represented
by the array variable, qvec(). Individual elements of the array are referenced as qvec(0),
qvec(1), etc. Using the log returns data discussed in the previous chapter, Figure 3.2 shows
the quartiles calculated with the Excel QUARTILE function in cells H21:H25, with the
first and last Q points being the minimum and maximum of the data set, 0.043 being
