Introduction to VBA
53
used rather than the Excel function. (For example, write Log rather than Application.Ln
when you require the natural logarithm of some quantity; similarly write Sqr rather than
Application.Sqrt.)
3.4.6 General Points on Programming
To conclude this section, some more general advice about the process of tackling a
programming project is given. In structured programming, the objective is to write
programs that progress in an orderly manner, that are easy to follow and, most important,
easy to modify.
It helps to work out on paper the overall application’s main stages, breaking the overall
task down into distinct and separable subtasks. Then the program can be built in steps,
coding and testing a series of self-contained and separate subprograms to accomplish the
subtasks. Where possible, it helps to keep the subprogram segments reasonably small.
One of the tenets of structured programming is that a code segment should have one
entry and one exit point. Program control should not jump into or exit from the middle of
code segments. If this practice is adhered to, the process of linking the separate segments
of code together is greatly simplified.
Useful programs frequently require updating or modifying in different ways. Program
logic is made easier to follow if comments are added when the program is first written.
Wherever possible, important constants in the numerical calculations should be para-
meterised (explicitly declared) so that they are easy to change if the program is to be
used for different circumstances. If code can be written to handle slightly more general
situations, it is usually worth the extra effort.
3.5 COMMUNICATING BETWEEN MACROS
AND THE SPREADSHEET
Having introduced the use of variables and control structures in programming, this section
discusses how VBA macros can obtain inputs directly from the spreadsheet and how
results can be returned. It concentrates on the communication between macros and the
spreadsheet.
In most cases, a subroutine consists of three parts: input of data, calculations (or manip-
ulation of inputs), then output of results. Writing the VBA code for the calculations usually
involves conventional programming techniques. The novel aspect of VBA programming
is the interaction with the spreadsheet. Taking the three parts separately:
ž Input can be from spreadsheet cells or directly from the user via dialog boxes, with the
input stored in variables.
ž Output can be written to cells or displayed in dialog boxes.
ž Calculation can be done in code (‘offline’), or by using formulas already in cells or by
writing to cells via statements in the VBA subroutine.
The following three versions of the familiar factorial example illustrate combinations of
different types of input, calculation and output.
Subroutine Factorial1 gets input from the user, employs the Excel FACT function for
the calculation and returns the output via MsgBox. The subroutine does not interact with
Pdf xmp metadata editor - add, remove, update PDF metadata in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Allow C# Developers to Read, Add, Edit, Update and Delete PDF Metadata
google search pdf metadata; remove pdf metadata online
Pdf xmp metadata editor - VB.NET PDF metadata library: add, remove, update PDF metadata in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
Enable VB.NET Users to Read, Write, Edit, Delete and Update PDF Document Metadata
embed metadata in pdf; c# read pdf metadata
54
Advanced Modelling in Finance
the contents of the worksheet at all:
SubFactorial1()
Dimfac, num
num = InputBox(“Enter number ”, “Calculate Factorial ”)
fac = Application.Fact(num)
MsgBox ”Factorial is ”& fac
EndSub
In contrast, Factorial2 takes its input from a spreadsheet cell (B5) and returns the
factorial answer to another cell (C5):
SubFactorial2()
’gets number from spreadsheet,uses Excel Fact function,returns answer tospreadsheet
Dimfac, num
num = Range(“B5”).Value
fac = Application.Fact(num)
Range(“C5”).Value = fac
EndSub
As a further variation, in Factorial3 the input number is written to a cell (B6), the
factorial formula is written to an adjacent cell with the code:
Range(“C6”).Formula = “=FACT(b6)”
and the result displayed back to the user.
SubFactorial3()
’gets number from InputBox, calculates factorial inspreadsheet
‘returns answer via MsgBox
Dim fac, num
num = InputBox(“Enter number ”, “Calculate Factorial ”)
Range(“B6”).Value = num
Range(“C6”).Formula = “=FACT(b6)”
fac = Range(“c6”).Value
MsgBox “Factorial is ”& fac
EndSub
To consolidate, try developing these input–output subroutines with your own formulas
and requirements. For reference, the factorial subroutines are all in ModuleF of the
VBSUB workbook, and are best run from the IntroEx sheet. When you adapt or write
your own subroutines, you may wish to take note of the material in Appendix 3A on
the Visual Basic Editor, especially the paragraphs on stepping through and debugging
macros.
The process of reading the values of spreadsheet ranges, and writing results to other
spreadsheet cells and ranges, is simple if cell-by-cell computation is not required. Suppose
the input data is in the spreadsheet range named ‘avec’, the values of whose cells have
to be pasted to another range, with anchor cell (the top, left-hand cell) named ‘aoutput’.
The recorder produces the code in subroutine ReadWrite1, which can be improved by
editing out the ‘selection’ operations, as shown in ReadWrite2:
SubReadWrite1()
’recorded macro
Application.Goto Reference:=“avec”
Selection.Copy
Application.Goto Reference:=“aoutput”
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
C# TIFF: TIFF Metadata Editor, How to Write & Read TIFF Metadata
How to Get TIFF XMP Metadata in C#.NET. Use this C# sample code to get Tiff image Xmp metadata for string. // Load your target Tiff docuemnt.
batch pdf metadata; read pdf metadata
XDoc.Tiff for .NET, Comprehensive .NET Tiff Imaging Features
types, including EXIF tags, IIM (IPTC), XMP data, and to read, write, delete, and update Tiff file metadata. Render and output text to text, PDF, or Word file.
read pdf metadata java; pdf xmp metadata viewer
Introduction to VBA
55
False, Transpose:=False
Application.CutCopyMode = False
End Sub
Sub ReadWrite2()
’edited version
’reads data, writes values to another range
Range(“avec”).Copy
Range(“aoutput”).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
’cancels Copy mode
End Sub
The coding becomes a little more complicated if the calculation routine has to be
performed on the individual cells of avec. These calculations are likely to be carried out
in a repeating loop, and the results pasted one-by-one into an output range. Counting the
range avec provides the number of cells in avec to be read and values to be pasted to the
output range. Suppose the calculation routine produces the running product of the elements
of avec. For the calculation, two variables x and y are used, x for the current element
of avec, and y for the current product. Suppose the top cell of the input range, avec,
is named ‘atop’ and the top cell of the output range is named ‘aoutput’. The following
subroutine reads data cell-by-cell, calculates the product to date and outputs the current
product at each step:
Sub ReadWrite3()
’reads data cell-by-cell, calculates, writes results
Dim i As Integer, niter As Integer
’niter is counter for iterations
Dim x, y
niter = Range(“avec”).Count
’no. of cells in avec
y=1
’initial value for product
For i = 1To niter
x= Range(“atop”).Offset(i - 1, 0)
y= x
Ł
y
’calculation routine
Range(“aoutput”).Offset(i - 1, 0) = y
Next i
Application.CutCopyMode = False
End Sub
Offset(i, j) is a most useful method which returns the cell i rows below and j columns
to the right of the referenced cell, here Range(“atop”). For example, the cell given by
Range(“atop”).Offset(1, 0)isonerowbelowthecellnamedatop,etc.Initiallyxtakes
the valueRange(“atop”).Offset(0,0), that is the value of Range(“atop”).
As a final example of communication between macro and spreadsheet, we reconsider
the Quartiles subroutine, improving the coding so that the five quartiles are returned
as an array to the spreadsheet. The new subroutine, Quartiles1, retains the five quartile
values in the array variable qvec() and outputs this to the previously named range
qvec1. (The range name qvec1 is attached to range K20:K24 in the Data sheet,
as can be confirmed by choosing Insert then Name then Define and inspecting the
dialog box.)
In the code below, the array variable qvec(4) is dimensioned to hold the five values.
The input and output array variables, dvec (the data set) and qvec1 (the results array),
have been declared as Variants to indicate (to us) that they are not simply scalar variables.
Since dvec and qvec1 are not accessed element-by-element, their variable declaration Dim
C# Raster - Raster Conversion & Rendering in C#.NET
RasterEdge XImage.Raster conversion toolkit for C#.NET supports image conversion between various images, like Jpeg, Png, Bmp, Xmp and Gif, .NET Graphics
remove metadata from pdf online; pdf metadata editor
C# Raster - Image Process in C#.NET
Image Access and Modify. Image Information. Metadata(tag) Edit. Color VB.NET How-to, VB.NET PDF, VB.NET Word process various images, like Jpeg, Png, Bmp, Xmp
change pdf metadata; read pdf metadata online
56
Advanced Modelling in Finance
statements do not include the brackets (). After filling array dvec from the spreadsheet
range dvec, the quartiles array is assembled element-by-element in the For...Next loop.
By default, VBA sets up arrays such as qvec() in a worksheet row, so Excel’s Transpose
function is applied to output the quartiles as a column vector:
Option Base 0
SubQuartiles1()
’pastes 4 by 1 col vector of quartiles intorange named ‘qvec1’
’requires 2 named ranges in spreadsheet, dvec with dataand qvec1
Dimi As Integer
Dimqvec(4)
’quartiles array with5 elements
Dimdvec As Variant
’col vec of data
Dimqvec1 As Variant
’results array
’fill array variable from spreadsheetrange
dvec = Worksheets(“Data”).Range(“dvec”)
’calculatequartiles and assemble as an array
For i = 0To 4
qvec(i) = Application.Quartile(dvec, i)
Next i
qvec1 = Application.Transpose(qvec)
’to make a column vector
’transfer results into spreadsheet range qvec1
Worksheets(“Data”).Range(“qvec1”) = qvec1
EndSub
Notice that only one statement is executed in the loop. This illustrates the practice of never
putting unnecessary statements in loops. Any statements unaffected by the processing in
the loop should always be put outside the loop.
Once again, you should consolidate your understanding by trying out some of these
subroutines with your own data and formulas. For reference, the ReadWrite subroutines
are in ModuleI and the Quartiles subroutines in ModuleQ of the VBSUB workbook.
This concludes our brief introduction to writing VBA subroutines. For further amplifi-
cation at this stage, Chapter 2 of Green’s (1999) text provides an excellent primer in Excel
VBA. The next section contains three further applications, developed and explained at
greater length. They represent some realistically useful applications for macros and taken
together illustrate further ideas about writing VBA code.
3.6 SUBROUTINE EXAMPLES
In this section, a number of subroutines are developed more fully to illustrate firstly,
the process of incremental improvement and secondly, some useful application areas for
macros. These examples combine use of the recorder and subsequent code editing. They
include subroutines for generating particular types of charts, cumulative frequency and
normal probability plots, and repeated optimisation with Solver.
3.6.1 Charts
To start, we develop a subroutine for charting some data such as the cumulative frequency
data shown tabulated and plotted out in Figure 3.3.
Suppose the frequency data is in the ‘Data’ sheet of a workbook in a range named
‘chartdata’ and the objective is to produce an XY chart with cumulative percentage
frequencies on the Y axis. The chart is to be placed as an object on the Data sheet.
Introduction to VBA
57
Fre
quenc
c
y Distribution:
:
interval
%cum freq
-0.16
0%
-0.12
10%
-0.08
16%
-0.04
27%
0.00
44%
0.04
52%
0.08
77%
0.12
92%
0.16
98%
0.20
100%
Cumulative Frequency
0%
20%
40%
60%
80%
100%
-0.20
-0.10
0.00
0.10
0.20
0.30
Freq(X>x)
Figure 3.3 Frequency distribution and chart in Data sheet of workbook VBSUB
For a novice VBA user, the code required to manipulate charts is likely to be unknown,
so the best approach is to use the recorder to generate the code. The VBA recorder is
switched on and the keystrokes required to plot the data in range ‘chartdata’ are recorded.
Wherever possible, use range names (selecting the names from the namebox or clicking
the Paste Name button) to specify cell ranges. The code generated for macro here called
ChartNew() is likely to be similar to the following:
Sub ChartNew()
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
’WizStep1
ActiveChart.SetSourceDataSource:=Sheets(“Data”).Range(“chartdata”),PlotBy:=
xlcolumns
’WizStep2
ActiveChart.Location Where:=xlLocationAsObject, Name:=“Data”
’WizStep4
With ActiveChart
’WizStep3
.HasTitle = True
’Titles
.ChartTitle.Characters.Text = “CumulativeFrequency”
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = “Freq(X>x)”
EndWith
With ActiveChart.Axes(xlCategory)
’Gridlines
.HasMajorGridlines = False
.HasMinorGridlines = False
EndWith
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
EndWith
ActiveChart.HasLegend = False
’Legend
End Sub
(The annotations on the right-hand side above have been added to help explain this
somewhat lengthy set of statements. WizStep1 is short for Chart Wizard Step 1, etc.)
The Charts collection in the workbook is augmented by a new chart[Charts.Add] of
type XYScatterSmoothNoMarkers[Active.ChartType] taking as source data the cell range
named ‘chartdata’[ActiveChart.SetSourceData]. The code generated relates closely to
58
Advanced Modelling in Finance
the answers given in the first two Chart Wizard steps. In the first step, the chart type
is chosen, and in the second, the data range containing the source data is defined and
its structure (rows or columns) specified. [The syntax for the method applied to the
object ActiveChart is: SetSourceData(Source, PlotBy).] In passing, note that part of
the ActiveChart.SetSourceData statement overflows onto the following line. This is
indicated in the code by a space followed by underscore (
).
The code line: ActiveChart.Location Where:=xlLocationAsObject, Name:=“Data”
corresponds to choices made on the fourth wizard screen and ensures that the
chart will be located as an embedded object on the Data sheet itself. (If the chart
is to be in a separate chart sheet, the code simplifies to ActiveChart.Location
Where:=xlLocationAsNewSheet.)
The majority of the subsequent code relates to the choices made in the Wizard’s third
step, where Chart Options are specified. Notice the useful bit of syntax:
‘With...End With’
which Excel’s recorder frequently uses when several changes are made to an object, here
the ActiveChart object. Hence the code:
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = “Cumulative Distribution”
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = “P(X>x)”
EndWith
defines the various titles, and subsequent code segments define the Gridlines, Axes and
Legend in turn.
Much of the code generated by the recorder leaves default settings unchanged and can
be edited out to make the macro more concise. A first step is to ‘comment out’ possibly
redundant statements (by adding an apostrophe at the start of the statement), and then
to check that the code still works as wished. For example, the following subroutine,
ChartNew1 works perfectly well without the comment lines:
SubChartNew1()
Charts.Add
ActiveChart.ChartType= xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets(“Data”).Range(“chartdata”), PlotBy:=
xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=“Data”
WithActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = “Cumulative Frequency”
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = “Freq(X>x)”
End With
’With ActiveChart.Axes(xlCategory)
’ .HasMajorGridlines = False
’ .HasMinorGridlines = False
’EndWith
WithActiveChart.Axes(xlValue)
Introduction to VBA
59
.HasMajorGridlines = False
’ .HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
End Sub
The code can therefore be reduced to the following:
Sub ChartNew2()
’same as ChartNew Macro after removing redundant lines
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets(“Data”).Range(“chartdata”), PlotBy:=
xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=“Data”
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = “Cumulative Frequency”
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = “Freq(X>x)”
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
End With
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Select
’new code added
Selection.Interior.ColorIndex = xlAutomatic
’remove plot area colour
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MaximumScale = 1
’reset max. value on Y axis
End With
End Sub
The code fromActiveChart.PlotArea.Selectonwards is additional. It ensures that the Y
axis scale stops at 1 (or 100%) and removes the default grey colour in the Plot area of
the chart.
The various chart macros which can be run with the Data sheet equity returns are in
ModuleC of the workbook.
3.6.2 Normal Probability Plot
One useful data visualisation is the so-called normal probability plot. This plot of a set
of readings shows whether the variation in the readings can be assumed to be statistically
normal. Suppose there are 50 readings. Then the order statistics of a set of 50 normally
distributed readings (called norm-scores) are plotted against the standardised values of the
readings (called z-scores). If the readings are effectively normal, the points resulting from
the two scores will lie more or less on a straight line. An illustrative normal probability
plot shown in Figure 3.4 indicates some departures from normality, especially on the tails
of the distribution.
At present, Excel does not include this useful plot in its statistical armoury. (The plot
referred to as a normal probability plot in the Data Analysis ToolPak Regression option
is not a conventional normal probability plot at all, but simply the cumulative distribution
of the dependent variable.)
60
Advanced Modelling in Finance
Normal Probability Plot
-3
-2
-1
0
1
2
3
-3
-2
-2
-1
-1
0
1
1
2
2
3
z-score
norm-score
Figure 3.4 A normal probability plot of log returns data
Suppose the readings are in a range named dvec in the Data sheet of the workbook. In
the VBA code, an array variable dvec is set up to take the values from the range named
dvec. Because we need to process individual array elements, dvec(i), dvec is declared a
Variant (and is actually an object variable, specifically a Range object). A second array
variable, Znvec, is set up to take vectors of z-scores and norm-scores, one pair of scores
for each data value in dvec. These array variables are declared at the beginning of the
subroutine NPPlotData.
Since a range variable is referenced by dvec, its values are assigned with the Set
keyword, hence:
Setdvec = Sheets(“Data”).Range(“dvec”)
Once dvec has been specified, the number of readings in dvec (say n) can be evaluated
and previously declared arrays dimensioned correctly. Hence the statement:
ReDim Znvec(n, 2)
For each reading in the data set, a z-score and a norm-score are calculated within a
For...Next loop. The calculations for the z-score use the Excel AVERAGE and STDEV
functions, whereas for the norm-score the calculations involve the Excel RANK function
and the inverse standard normal probability function (NORMSINV). The scores are stored
in the n by 2 matrix called Znvec, and subsequently output to the named range Znvec
in the Data sheet. Once the vectors of scores have been calculated, they can be plotted
against each other in an XY chart.
Introduction to VBA
61
The full code for obtaining the scores for the plot is given below. The penultimate step
calls the subroutine ChartNormPlot, which is similar to the chart subroutine developed in
section 3.6.1. You can try out the NPPlot macro on the log returns data in the Data sheet
of the VBSUB workbook.
OptionExplicit
OptionBase 1
Sub NPPlotData()
’returns normal probability plot of data from named range dvec
’declaring variables
Dim m1, sd1, rank1, c1
Dim i As Integer, n As Integer
Dim Znvec() As Variant
Dim dvec As Variant
’data input from worksheet
Set dvec = Sheets(“Data”).Range(“dvec”)
’useSet becausedvec(i) required
n= Application.Count(dvec)
ReDim Znvec(n, 2)
’number of readings
’Znvec dimensioned as n by 2 matrix
’calculating elements of Znvec array
m1 = Application.Average(dvec)
’mean of readings
sd1 = Application.StDev(dvec)
’standard deviation of readings
For i = 1 Ton
Znvec(i, 1) = (dvec(i) –m1) /sd1
’z-score for ith reading
rank1 = Application.Rank(dvec(i), dvec, 1)
c1 = (rank1 – 3 / 8) / (n + 1 /4)
’using a continuity correction
Znvec(i, 2) = Application.NormSInv(c1)
’n-score for ithreading
Next i
’output results to range Znvec
Sheets(“Data”).Range(“Znvec”) = Znvec
’matrix of scores output to ’range Znvec
With Sheets(“Data”).Range(“Znvec”)
.NumberFormat = “0.00”
’output data formatted
.Name = “npdata”
’output range given name npdata
End With
ChartNormPlot
’subroutine ChartNormPlot called
End Sub
The code for the NPPlot subroutine and ChartNormPlot (called from the NPPlotData
macro) are stored in ModuleN of the workbook.
3.6.3 Generating the Efficient Frontier with Solver
This application requires a degree of familiarity with portfolio theory and optimisation
with Excel’s Solver add-in. The reader may prefer to delay inspecting this material until
Chapter 6 has been studied.
In looking at portfolios with several risky assets the problem is to establish the asset
weights for efficient portfolios, i.e. those that have minimum risk for a specified expected
62
Advanced Modelling in Finance
return. It is straightforward to get the optimum weights for a target return with Solver.
If the optimisation is repeated with different targets, the efficient portfolios generate the
risk–return profile known as the efficient frontier.
For example, Figure 3.5 shows the weights (40%, 50%, 10%) for one portfolio made
up of three assets with expected return 2.2%. The target return is 7% and the efficient
portfolio is determined by the set of weights that produce this return with minimum
standard deviation.
4
5
6
7
8
9
10
11
12
13
14
15
16
17
A
B
C
D
E
F
G
H
I
J
Asset Data
Exp Ret
Std Dev
TBills
0.6%
4.3%
Target exp return
7.0%
target1
Bonds
2.1%
10.1%
Shares
9.0%
20.8%
Correlation Matrix
TBills
Bonds
Shares
Portfolio weights:
TBills
1.00
0.63
0.09
TBills
40.0%
Bonds
0.63
1.00
0.23
Bonds
50.0%
change1
Shares
0.09
0.23
1.00
Shares
10.0%
VCV matrix
TBills
Bonds
Shares
TBills
0.0018
0.0027
0.0008
Exp Ret
2.2%
portret1
Bonds
0.0027
0.0102
0.0048
Std Dev
7.0%
portsd1
Shares
0.0008
0.0048
0.0433
Figure 3.5 Portfolio weights, expected return and standard deviation of return from Eff1 sheet
To perform this optimisation, Solver requires ‘changing cells’, a ‘target cell’ for minimi-
sation (or maximisation) and the specification of ‘constraints’, which usually act as
restrictions on feasible values for the changing cells. In Figure 3.5, we require the weights
in cells I10:I12 to be proportions, so the formula in cell I10 of D1SUM(I11:I12) takes
care of this requirement. For the optimisation, the ‘changing cells’ are cells I11:I12, named
‘change1’ in the sheet. The target cell to be minimised is the standard deviation of return
(I16) named ‘portsd1’. There is one explicit constraint, namely that the expected return
(cell I15) named ‘portret1’ equals the target level (in cell I5 named ‘target1’). The range
names are displayed on the spreadsheet extract to clarify the code subsequently developed
for the macro.
Applying Solver once, the optimum weights suggest buying Bonds and Shares and
short selling TBills in the proportions shown in Figure 3.6. Cell I15 confirms that this
efficient portfolio with minimum standard deviation of 15.7% achieves the required
return of 7%.
Changing the target expected return in cell I5 and running Solver again with the same
specifications as before would produce another efficient portfolio. Clearly, if the entire
efficient frontier is required, the best approach is via a macro. If the optimisation using
Solver is recorded, the following type of code is obtained:
SubTrialMacro()
SolverReset
SolverAdd CellRef:=“$I$15”, Relation:=2, FormulaText:=“target1”
Documents you may be interested
Documents you may be interested