Introduction to VBA
63
SolverOk SetCell:=“$I$16”, MaxMinVal:=2, ValueOf:=“0”, ByChange:=“$I$11:$I$12”
SolverSolve
End Sub
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
A
B
C
D
E
F
G
H
I
J
Efficient Frontier points using Solver (no constraints on weights) Eff1
Eff1
Asset Data
Exp Ret
t
Std Dev
TBills
0.6%
4.3%
Target exp return
rn
7.0%
target1
1
Bonds
2.1%
10.1%
Shares
9.0%
20.8%
Correlation Matrix
TBills
Bonds
Shares
Efficient frontier portfolio
o
TBills
1.00
0.63
0.09
TBills
-5.6%
Bonds
0.63
1.00
0.23
Bonds
35.8%
change1
1
Shares
0.09
0.23
1.00
Shares
69.8%
VCV matrix
TBills
Bonds
Shares
TBills
0.0018
0.0027
0.0008
Exp Ret
7.0%
portret1
1
Bonds
0.0027
0.0102
0.0048
Std Dev
15.7%
portsd1
1
Shares
0.0008
0.0048
0.0433
Figure 3.6 Optimum portfolio weights for expected return of 7% produced by Solver
The recorded code hints at some of the special VBA functions in the Solver add-in. In
particular, the SolverAdd function (for specifying a constraint) has three arguments: the
first is a cell reference for the constraint ‘LH side’, the second an integer code for the
relation (2 for D) and the third either a cell reference or a single number. Similarly,
the arguments in the SolverOk function specify the particular optimisation problem to
be solved.
The recorded code can be improved firstly by writing the functions and their arguments
with brackets together with comma delimited arguments, and secondly by employing
range names in preference to cell addresses. The Call keyword used with each Solver
function highlights the fact that control has been passed to a Function procedure. (Also,
it suppresses the value returned by the function.) After editing, the code for the first
optimisation is:
Sub Eff0()
’toreturn an efficient portfolio for given target return
SolverReset
Call SolverAdd(Range(“portret1”),2, Range(“target1”))
Call SolverOk(Range(“portsd1”), 2, 0, Range(“change1”))
Call SolverSolve(True)
SolverFinish
End Sub
The SolverAdd function ensures that the expected return (in the cell named ‘portret1’)
meets the specified target, in cell‘target1’. The SolverOk function controls the optimisation
Pdf metadata viewer - 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
add metadata to pdf file; acrobat pdf additional metadata
Pdf metadata viewer - 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
batch update pdf metadata; adding metadata to pdf files
64
Advanced Modelling in Finance
task, ensuring that the entries in cell range ‘change1’ are such that the portfolio standard
deviation (in cell ‘portsd1’) is minimised. The SolverFinish function is equivalent to
selecting options and clicking OK in the Solver Results dialog shown after solution.
Effectively, with this statement, the Solver Results dialog box is not displayed under
macro control.
The efficient frontier requires the choice of optimal portfolio weights for a range
of target returns. Suppose a range of different targets is set up starting with an initial
target of 1% (min
tgt in general) increasing in steps of 2% (incr) as many times as
specified (niter). The different target returns are produced in a Do While...Loop which
repeats niter times. For each target return, Solver has to produce optimal weights.
The only part of the problem specification that changes on each iteration is the target
return. Thus, in the code, most of the Solver specification is set up before the repeated
optimisation. Only the SolverChange function that changes the target return sits inside
the loop.
SubEff1()
’ repeated optimisation withgiven min
target & increment
’ initialisation
Dim target1: Dim incr
Dim iter As Integer, niter As Integer
target1 = Range(“min
tgt”).Value
’value taken from spreadsheet cell
incr = Range(“incr”).Value
’value taken from spreadsheet cell
niter = Range(“niter”).Value
’value takenfrom spreadsheet cell
iter = 1
’initial value for loop counter
’ code to clearout previous results
ClearPreviousResults
’a subroutine
’ set up Solver
SolverReset
Call SolverAdd(Range(“portret1”), 2, Range(“target1”))
Call SolverOk(Range(“portsd1”), 2,0, Range(“change1”))
’ repeated part
Application.ScreenUpdating = False
’turns off screen recalculation
Do While iter <= niter
Range(“target1”).Value = target1
’put current value of target1 in cell
Call SolverChange(Range(“portret1”), 2, Range(“target1”))
Call SolverSolve(True)
SolverFinish
code to copy & paste results in sheet
ReadWrite
’a ReadWrite subroutine
target1 = target1 + incr
’update value of variable target1
iter = iter +1
’increment counter
Loop
Range(“target1”).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
’turn screen updating back on
EndSub
After writing the code, you can add a description for the macro and assign a keyboard
shortcut, just as occurs when you record a macro. To do this, start in the Excel window,
choose Tools then Macro then Macros. In the Macro dialog box, select the macro name
(Eff1) then click the Options button.
VB.NET PDF - WPF PDF Viewer for VB.NET Program
C#.NET PDF Create, C#.NET PDF Document Viewer, C#.NET PDF Windows Viewer, C#.NET search text in PDF, C#.NET edit PDF bookmark, C#.NET edit PDF metadata, C#.NET
pdf metadata viewer; extract pdf metadata
VB.NET PDF- View PDF Online with VB.NET HTML5 PDF Viewer
C#.NET PDF Create, C#.NET PDF Document Viewer, C#.NET PDF Windows Viewer, C#.NET search text in PDF, C#.NET edit PDF bookmark, C#.NET edit PDF metadata, C#.NET
endnote pdf metadata; add metadata to pdf
Introduction to VBA
65
For reference, the macros to generate the efficient frontier are in ModuleS of VBSUB
and can be run from the Eff1 sheet. Before you use any macro containing Solver, you
must establish a reference to the Solver add-in. With a Visual Basic module active,
click References on the Tools menu, then Browse and find Solver.xla (usually in the
nOfficenLibrary subfolder).
SUMMARY
VBA is an object-oriented version of the Basic programming language. As well as familiar
programming ideas concerning variables and coding, there are also methods and properties
for use with Excel objects.
You automate operations in Excel first by writing the code for subroutines and functions
in a VBA Module within the workbook, then by running your macros. The Visual Basic
Environment provides some tools for debugging macros and access to VBA libraries and
online help.
The purpose of VBA subroutines is to carry out actions: in contrast, VBA user-defined
functions return values. In the context of financial modelling, we have found that functions
are more useful than subroutines. The exceptions are operations that require the production
of charts, and automating optimisation tasks using Solver. Subroutines are frequently
useful for one-off administrative tasks, which require lots of repetition.
The macro recorder can be used to translate your actions into VBA code. Recording
keystrokes in macros is sometimes useful as a starting point, for getting coding clues and
insights. Whereas the recorder tends to generate verbose code for simple operations, it
can produce highly sophisticated code for many menu-based procedures.
Most Excel functions can be included in VBA macros and there are some special VBA
functions for use in macros. This is a useful way of leveraging the knowledge of the
experienced Excel model builder.
There are advantages to mastering VBA. The replacement of calculations by functions
can lead to more powerful cell formulas and makes calculation sequences more robust
and faster. Skilful use of macros to control repeated operations can remove other sources
of error from spreadsheet operations.
REFERENCES
Green, J., 1999, Excel 2000 VBA Programmer’s Reference, Wrox Press Ltd., Birmingham.
Leonhard,W.,L.HudspethandT.J.Lee,1997,Excel97Annoyances,O’Reilly& Associates,Inc.,Sebastopol, CA.
Walkenbach, J., 1999, Excel 2000 Power Programming with VBA, IDG Books, Foster City, CA.
Wells, E. and S. Harshbarger, 1997, Microsoft Excel 97 Developer’s Handbook, Microsoft Press.
APPENDIX 3A THE VISUAL BASIC EDITOR
The Visual Basic Editor was substantially changed for Excel 97 (and later versions) and
this appendix details some of its features. It can be activated by pressing the VBE button
(on the VB toolbar) or by pressing AltCF11 in the Excel window. Once activated, you
can toggle between the Excel and the Visual Basic windows with AltCTab in the usual
manner. The Visual Basic Editor can also be opened via the Excel menubar by choosing
Tools then Macro then Visual Basic Editor.
The Visual Basic window should look something like that shown in Figure 3.7, which
includes a menubar and a toolbar across the top; the Project Explorer and the Properties
windows on the left-hand side and the Code window on the right. In the illustration, the
How to C#: Modify Image Metadata (tag)
C#.NET PDF Create, C#.NET PDF Document Viewer, C#.NET PDF Windows Viewer, C#.NET search text in PDF, C#.NET edit PDF bookmark, C#.NET edit PDF metadata, C#.NET
pdf remove metadata; read pdf metadata java
C# PDF Library SDK to view, edit, convert, process PDF file for C#
RasterEdge WPF PDF Viewer provides C# users abilities to view, annotate, convert and create PDF in WPF application. C#.NET: Edit PDF Metadata.
get pdf metadata; metadata in pdf documents
66
Advanced Modelling in Finance
Code window contains the code in Module1, in fact recorded code for the data entry
operations described in section 3.3.5.
Figure 3.7 Visual Basic window with Project Explorer, Properties and Code windows open
The toolbar and windows described may not be visible when you first activate the Visual
Basic Editor. If the toolbar is not visible, use View Toolbars and click once on the Standard
option. Similarly, choose View then Project Explorer and View then Properties window
to display the two left-hand windows. To display the Code module on the right, double
click on the relevant Module (here Module1) in the Project Explorer window.
As the name suggests, the code modules contain the VBA code for procedures. These
modules are inserted by choosing Insert then Module from the menubar. (These are
Modules, not Class Modules, which are more advanced and will not be covered in this
text.) You can insert as many modules as you like in the workbook. One practice is to
put related macros into the same module.
The Project Explorer shows the component parts of all the open ‘projects’. Its main
use is to act as a navigation tool, from which the user can open new modules in which to
store code or remove (i.e. delete) a module or to activate the Code windows for existing
modules. Selecting a module sheet, the File menu allow you to Remove the module sheet
(i.e. delete it) and also to Print out its contents. If you want to copy VBA code from one
Module to another, avoid the Export File option. Copying code from one module sheet
C# WPF PDF Viewer SDK to view, annotate, convert and print PDF in
PDF Online. Convert PDF Online. WPF PDF Viewer. View PDF in Image to PDF. Image: Remove Image from PDF Page. Edit URL. Bookmark: Edit Bookmark. Metadata: Edit, Delete
google search pdf metadata; delete metadata from pdf
C# TIFF: TIFF Metadata Editor, How to Write & Read TIFF Metadata
C# TIFF - Edit TIFF Metadata in C#.NET. Allow Users to Read and Edit Metadata Stored in Tiff Image in C#.NET Application. How to Get TIFF XMP Metadata in C#.NET.
edit pdf metadata; edit pdf metadata online
Introduction to VBA
67
to another one in the same workbook or any other is best achieved via the Code window
with the usual copy and paste operation.
Each Excel object shown in the Project Explorer for a particular ‘project’ has its
own set of ‘properties’, for example, each worksheet in an active workbook has the
‘name’ property. These properties can be changed via the Properties window. However,
since modifying properties is more relevant for worksheets than for modules, we will
not expand on the features of this window further in the context of macros, except
to note that the name of each code module can be modified here. (For example, we
frequently change the module names to M, 0 and 1, collecting together any macros in
ModuleM, functions with base 0 in Module0, and the remaining functions with base 1
in Module1.) In practice, when developing macros, it is more convenient to close the
Properties window and possibly the Project Explorer to maximise the size of the Code
window.
The Visual Basic window has its own Standard toolbar, as shown in Figure 3.8, with
the View Excel button at the left-hand which returns the user to the Excel window. The
middle set of buttons concern editing tasks, running and testing out macros, and the right-
hand group open other windows such as the Immediate window (for testing individual
statements) and the Object Browser (for checking the objects, methods and properties
available in VBA). Note in particular the Run Macro button (a right pointing triangle),
the Reset button (a square) and the Object Browser (third from the end).
Figure 3.8 Standard toolbar in VB window
There are two types of procedure which we code in VBA: subroutines (or macros)
and functions. Only subroutines (or macros) can be recorded. Usually they will require
some editing in the Code window and possibly testing using Excel’s debugging tools.
Since functions cannot be recorded, they must be written in a code module. Therefore the
recording tools are of little use in developing functions.
As outlined in the main text, subroutines can be run from the Excel window (via menu
commands or by clicking the Run button on the VB toolbar or by using a chosen keyboard
shortcut). They can also be run from the Code window (with the pointer positioned
anywhere in the body of the routine’s code, by clicking the Run button on the VB Standard
toolbar underneath the VB window menu). Possibly more user-friendly than either of
these ways, subroutines can be attached to buttons. However, since our workbooks rely
on functions rather than macros, it is best to refer the reader to other texts for details of
attaching macros to buttons. (For example, see Leonhard et al., 1997 for further details
on the VBE and developing macros.)
Note that when you try to run subroutines and the VBE intervenes to tell you of an
error preventing compilation, macro operation is suspended in so-called ‘debug mode’,
the subroutine name being illuminated in yellow. With the cursor pointing to the offending
statement, you can edit simple mistakes, then easily jump out of debug mode via Run
C# HTML5 PDF Viewer SDK to view PDF document online in C#.NET
PDF Online. Convert PDF Online. WPF PDF Viewer. View PDF in Image to PDF. Image: Remove Image from PDF Page. Edit URL. Bookmark: Edit Bookmark. Metadata: Edit, Delete
remove metadata from pdf online; pdf xmp metadata editor
VB.NET PDF Library SDK to view, edit, convert, process PDF file
PDF Metadata Edit. Offer professional PDF document metadata editing APIs, using which VB.NET developers can redact, delete, view and save PDF metadata.
search pdf metadata; remove metadata from pdf acrobat
68
Advanced Modelling in Finance
Reset on the Run menu in the VBE command bar or by clicking the Reset button (with
the black square). The yellow illumination disappears and the macro can be re-run.
Stepping Through a Macro and Using Other Debug Tools
If your macros don’t work as you expect, it helps to step slowly through them operation-
by-operation. If you run the macro from the menu, choosing Tools then Macros then
selecting the ‘macro name’, you need to click the Step Into button on the Macro dialog
box instead of choosing Run. This allows you to step through the macro. Alterna-
tively, provided the cursor is not in a Code window, you can click the Run button
on the VBA toolbar, choose the macro to be tested then click the Step Into button.
The VBA Code window opens with the first statement of the chosen macro highlighted
(in yellow).
Click the F8 function key (or if visible, the small Step Into button) to move to the
next line of code and so on. If you want to see the actions in the spreadsheet window,
reduce the size of the VBE window so you can see the Excel sheet underneath or use the
AltCTab combination to move between windows and watch the macro working line-by-
line in the Code window. Click F8 (or the Step Into button) repeatedly to step through
the macro. There is a Step Out button to escape from step mode. Alternatively, the VBE
Editor Window command bar Run menu has a Reset choice which gets you out of step
or debug mode.
The Debug toolbar in Figure 3.9 contains the Step Into, Step Over and Step Out
buttons for proceeding through the macro code. A macro being ‘stepped through’ line-
by-line is said to be in ‘break mode’. If the macro calls other secondary routines,
the Step Over button executes these secondary routines without stepping through them
line-by-line.
Figure 3.9 The Debug toolbar, with Step In, Step Out and Reset buttons shown separately
To illustrate, display the Debug toolbar in the VB window (using View then Tool-
bars) and try stepping through the Factorial macro (whose code is in ModuleA of the
VBSUB workbook). Starting from the IntroEx sheet, choose Tools then Macro then
Macros, select the Factorial macro as shown in Figure 3.10, and click the Step Into
button. The VB window opens with the first code line of the Factorial macro high-
lighted (in yellow). Continue stepping through with the Step Into button. When the Input
Box asking for the number appears, enter 3 say and click OK. Go on stepping through
(checking that the loop is executed three times) until the MsgBox dialog appears with the
answer 6.
At any stage, you can jump out of step mode by clicking Reset (or via Run then Reset
from the VBE standard command bar).
Introduction to VBA
69
Figure 3.10 Entering step mode for the Factorial macro
As a further illustration, step through the Factorial macro once again after clicking the
Locals window button (see the Debug toolbar, fifth button from the right in Figure 3.9).
In the new window, the values of variables num, fac and i are shown as successive lines
of the macro code are executed.
Another useful facility in debugging macros is to insert breakpoints, especially in
longer subroutines. When the macro runs, operations cease when a breakpoint is reached.
Execution jumps to break mode, and the macro can be explored in step mode. To insert
a breakpoint, select the position in the code and click in the adjacent grey margin or
simply click the breakpoint button (shown in Figure 3.9 with a small hand). To remove
the breakpoint, simply click in the grey margin once again or toggle the breakpoint
button. With the Factorial macro, try making the MsgBox statement a breakpoint, then
run the macro to check that it runs normally until the point at which the results should be
displayed. Then execution changes to ‘step mode’ for the remaining code lines and macro
operations proceed by pressing the F8 key. (Breakpoints can also be used in debugging
functions, since they provide a means of changing into step mode to examine the operation
of the function code.)
APPENDIX 3B RECORDING KEYSTROKES IN ‘RELATIVE
REFERENCES’ MODE
The recorder translates keystrokes into VBA code and has two modes when interpreting
cell addresses: its default mode (absolute references) and the relative references mode.
In section 3.3.5, the macro entering data into worksheet cells was recorded using abso-
lute references. Here, we look at the code generated when recording in relative refer-
ences mode.
70
Advanced Modelling in Finance
Start with the pointer on cell B8. The macro recorder is invoked from the Excel window
by choosing Tools, then Macro then Record New Macro. Give the macro a name, say
RelEntries.ThistimewhentheStopRecordingbuttonappearsonscreen,clicktheRela-
tive References button. It should appear as if ‘pressed in’. Then just as before, key in
the names of the months. The code when you’ve finished should look different from the
previous macro:
SubRelEntries()
’recorded with relative references
ActiveCell.FormulaR1C1 = “JAN”
ActiveCell.Offset(1,0).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “FEB”
ActiveCell.Offset(1,0).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “Total”
ActiveCell.Offset(-2,1).Range(“A1”).Select
ActiveCell.FormulaR1C1= “300”
ActiveCell.Offset(1,0).Range(“A1”).Select
ActiveCell.FormulaR1C1= “400”
ActiveCell.Offset(1,0).Range(“A1”).Select
ActiveCell.FormulaR1C1= “=SUM(R[-2]C:R[-1]C)”
ActiveCell.Offset(1,0).Range(“A1”).Select
EndSub
Try running the macro from different cells in the spreadsheet. The entries are made relative
to the pointer position (the ActiveCell referred to as Range(“A1”)), which itself moves
down one row at a time, then to the adjacent column. The Offset method, say Offset(1,
0), returns a range object, the cell in the same column, but one row below relative to the
current position. The cell selected is always referenced as “A1” (the ActiveCell), despite
the fact that the keystrokes did not involve cell A1 in any way.
To get most assistance from the recorder, it is worth thinking out in advance which
mode is likely to be more helpful: absolute or relative references. Usually, it is abso-
lute references, the default mode of recording. However, if you are trying to automate
operations that repeat themselves row-by-row down the worksheet, the relative references
mode is likely to throw up more clues as to coding. For example, if you want your macro
to select a specific cell, perform an action, then select another cell relative to the active
cell, record with relative references. However, you need to remember to switch off the
Relative References button to return to absolute referencing.
The RelEntries macro can be edited to make it less verbose. Recorded code always
includes much ‘selecting’ of cells, however when writing code, it is not necessary to
explicitly select cells to enter data into them. The following edited version of the code
performs the same operations but is more concise:
SubConciseEntries()
’entering data using relativepositions
ActiveCell.FormulaR1C1 = “JAN”
ActiveCell.Offset(1, 0).FormulaR1C1 = “FEB”
ActiveCell.Offset(1, 0).FormulaR1C1 = “Total”
ActiveCell.Offset(-2, 1).FormulaR1C1 = “300”
ActiveCell.Offset(1, 0).FormulaR1C1 = “400”
ActiveCell.Offset(1, 0).FormulaR1C1= “=SUM(R[-2]C:R[-1]C)”
EndSub
Introduction to VBA
71
In fact for a single cell range object, it turns out to be unnecessary to specify the Formula
property, so the code is simply:
Sub ConciseEntries()
’entering datausing relative positions
ActiveCell.Offset(0, 0) = “JAN”
ActiveCell.Offset(1, 0) = “FEB”
ActiveCell.Offset(2, 0) = “Total”
ActiveCell.Offset(0, 1) = “300”
ActiveCell.Offset(1, 1) = “400”
ActiveCell.Offset(2, 1) = “=SUM(R[-2]C:R[-1]C)”
End Sub
Apractical way to tidy up generated code is to comment out (add apostrophes in front
of possibly) redundant statements, then test by running to see the effect.
4
Writing VBA User-defined Functions
As well as automating spreadsheet operations, VBA code can be used to write functions
which work in the same way as Excel’s ‘built-in’ functions. Functions are particu-
larly useful when they automate calculation tasks that are required repeatedly. Func-
tion calculations are carried out ‘off sheet’, allowing leaner and cleaner layouts to be
devised, and functions are portable so that once programmed they can be copied to other
workbooks.
Whereas a VBA subroutine usually performs one or more actions, a VBA function is
aset of instructions that returns a single value (similar to the SUM function) or an array
of values (like the LINEST function). User-defined functions combine real programming
(complete with loops and conditional branching) together with Excel functions (such as
NORMSDIST and MMULT).
Functions are simplest to write when they operate on single numerical inputs (scalar
inputs) and when they return single cell values. This chapter starts by developing a
simple sales commission function with one input and one output to illustrate the steps
involved in writing and using functions. Functions with several scalar inputs are written
in much the same way. As an illustration, a mathematically more challenging function
based on the Black–Scholes option value formula is used. Function writing becomes more
exacting when the inputs are arrays rather than single values. To illustrate some aspects
of handling arrays, functions for the expected value and variance of a set of cash flows
and for the variance of a portfolio of assets are developed. There are further examples
where both inputs and outputs are arrays of values. The functions are available in the
VBFNS.xls workbook, and the reader is encouraged to experiment with these examples
and the exercises set at the end of the chapter.
4.1 A SIMPLE SALES COMMISSION FUNCTION
Suppose rates of commission on sales depend on the month’s total sales, the rates being
as shown in cells D5:E7 of the spreadsheet in Figure 4.1.
1
2
3
4
5
6
7
A
B
C
D
E
VBFns1.xls
Sales Commission Function
Sales
Commission
Sales >=
CommRate
25000
3000
0
8.0%
9999
799.92
10000
10.5%
10000
1050
20000
12.0%
Figure 4.1 Commission rates on sales in SalesCom sheet of VBFNS.xls
Documents you may be interested
Documents you may be interested