Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

978 Chapter 40

We have already encountered the Address property used in the ﬁ rst line

of the macro to report the current location. The second line of the macro

uses the BorderAround method of the Range Object. As is evident from

its name, this method changes the border around a range.

The third line

uses a property and a method in quick succession; the Cells property

accepts two parameters, a row offset, and a column offset (counting from

1) and returns the Range in that position; the Select method makes the

Range that activates it the current selection, and if this Range is a single

cell range, it also makes it the active cell. Once activated it looks like

this:

Sub ActivecellDemo1( )

MsgBox “You are at:” & ActiveCell.Address

ActiveCell.BorderAround xlDouble, xlThick, 3

ActiveCell.Cells(2, 2).Select

End Sub

If you subsequently click OK on the dialogue box, you will see the former

active cell has a line around it and that the active cell in the worksheet

moved.

2. A full description of its parameters taken for the VBA Help ﬁ les is presented in

appendix 2.

979 Objects and Add-Ins

40.3 The Range Object

Objects have types. One of the most important object types in VBA is

the Range. A worksheet cell and a range of cells are all objects of the

type Range. For example, the VBA object variable ActiveCell that we

encountered in the previous section is of the type Range. This section

demonstrates the use of ranges in VBA and presents some more of the

properties and methods of the Range object.

40.3.1 A Range as a Parameter to a Function

In this subsection we build a function that accepts a Range as a param-

eter. Our new function, named MeanReturn, accepts a column range of

asset prices as a parameter and computes and returns the mean return

of the assets in the column. Recall that the return of an asset for period

t is

−

Price Price

Price

and the mean return of an asset is

∑

An auxiliary function AssetReturn is used to compute r

Function MeanReturn(Rng As Range) As Double

Dim NumRows As Integer

Dim Prices As Variant

Dim T As Double

Dim i As Integer

NumRows = Rng.Rows.Count

Prices = Rng.Value

T = 0

980 Chapter 40

Lines of note:

For i = 2 To NumRows

T = T + AssetReturn(Prices(i - 1, 1), _

Prices(i, 1))

Next i

MeanReturn = T / (NumRows - 1)

End Function

Function AssetReturn(P0 As Variant, P1 As _

Variant) As Double

AssetReturn = (P1 - P0) / P0

End Function

NumRows = Rng.Rows.Count

In this line the Dot operator is used twice. Rng is our Range object. Rows

is property of the Range object, so Rng.Rows is an object of the Collec-

tion type that represents all the rows in our range. Count is a property

of Collection type objects that stores the number of members in the col-

lection, so Rng.Rows.Count is a variable that stores the number of rows

in our range.

Prices = Rng.Value

Value is a property of the Range object containing the values of all the

cells in the range. Value is of the type Variant. If the range is more than

one cell in size, Value is a two-dimensional array. The ﬁ rst index of Value

is the row index starting from 1, and the second index is the column index

starting from 1.

This is the function in action:

981 Objects and Add-Ins

40.3.2 Type Considerations with Variants and Objects as Parameters

Unless we want to use a laborious and very time-consuming loop to

move values from a Range in the worksheet to a function in VBA, we

have to use a Variant variable to store the Range of values.

The sharp-

eyed reader might have wondered why AssetReturn was deﬁ ned with

Variant parameters when we know perfectly well that the values sup-

plied are doubles and a better declaration for the AssetReturn function

would have been

AB C

100.000 0.160 <

=meanreturn(A2:A7)

110.000 0.100 <

=meanreturn(A2:A4)

121.000 0.200 <

=meanreturn(A4:A7)

145.200

174.240

209.088

MEANRETURN IN ACTION

Function AssetReturn(P0 As Double, P1 As

Double) As Double

Unfortunately, when we try to use the function, VBA checks the correct-

ness of our program and ﬁ nds that the Prices(i-1,1) is not a double. As

a consequence the following error is issued:

3. This method is two orders of magnitude faster than the loop way.

982 Chapter 40

So, since we cannot have Prices as an array of doubles, we have to design

AssetReturn to be ﬂ exible and accept Variants as input.

40.3.3 The Range Property

The Range property is one way to access a range on a worksheet. Range

is a property of many Excel objects. When used on its own, as in the next

macro, Range is a short way of writing ActiveSheet.Range.

Sub RangeDemo( )

Range(“A1”).Formula = 23

End Sub

As expected, the macro will set the formula in cell A1 of the active

worksheet to 23. The next macro sets the formula of each cell in the range

A1:B2 of the active worksheet to 23.

Sub RangeDemo1( )

Range(“A1:B2”).Formula = 23

End Sub

983 Objects and Add-Ins

Another way of addressing a range of cells using the Range property

is demonstrated by the next Macro. The Macro sets the formula of each

cell in the range A1:B2 of the active worksheet to 23. The ﬁ rst argument

to Range is the cell in the top left corner of the range, and the second is

the cell in the bottom right corner of the range.

Sub RangeDemo2( )

Range(“A1”, “B2”).Formula = 23

End Sub

Range is also a property of the Range object. The range returned by

Range when used this way is relative to the Range object. The next

macro sets the formula of the cell C2 of the active worksheet to 23.

Sub RangeDemo3( )

Range(“B1”).Range(“B2”).Formula = 23

End Sub

Note Range(“B1”) returns the range (or cell) B1 of the active work-

sheet. Range(“B1”).Range(“B2”) returns the cell B2 of the range that

has B1 as the top left corner. In worksheet terms, Range(“B1”).

Range(“B2”) returns the cell C2.

The next macro sets the formula of each cell in the range C2:D3 of

the active worksheet to 23. The macro uses the cell C2 as a starting

point.

Sub RangeDemo4( )

Range(“C2”).Range(“A1”, “B2”).Formula = 23

End Sub

984 Chapter 40

40.4 The With Statement

The With statement allows you to perform a series of statements on a

speciﬁ ed object without restating the obvious (the object’s name and its

pedigree, which can be very long). If you have more than one property

to change or more than one method to use for a single object, use the

With statement. With statements make your procedures run faster and

help you avoid repetitive typing. The following, somewhat contrived,

macro sets some properties of the font of the cell in the top left-hand

corner of the current region of the active cell. The font is set to be Arial,

bold, red, and 15 points in size.

Sub WithoutDemo( )

ActiveCell.CurrentRegion.Range(“A1”).Font _

.Bold = True

ActiveCell.CurrentRegion.Range(“A1”).Font _

.ColorIndex = 3

ActiveCell.CurrentRegion.Range(“A1”).Font _

.Name = “Arial”

ActiveCell.CurrentRegion.Range(“A1”).Font _

.Size = 15

End Sub

And here is the same macro using the With statement:

Sub WithDemo( )

With ActiveCell.CurrentRegion.Range(“A1”).

Font

.Bold = True

.ColorIndex = 3

.Name = “Arial”

.Size = 15

End With

End Sub

985 Objects and Add-Ins

Notice the dot (.) operator before the properties in the With

statement.

40.5 Collections

A Collection is an ordered set of items that can be referred to as a unit.

The Collection object provides a convenient way to refer to a related

group of items as a single object. The items, or members, in a Collection

need only be related by the fact that they exist in the Collection. Members

of a Collection don’t have to share the same data type.

A collection can be created the same way other objects are

created. Members can be added using the Add method and removed

using the Remove method. Speciﬁ c members can be referred to using an

integer index. The number of members currently in a Collection is avail-

able via the Count method. Our use of Collections will be restricted to

using the (quite numerous) arsenal of Collections that are part of the

Excel Object Model, like the Rows Collection mentioned in section

40.3.1.

40.5.1 The For Each Statement in Use with Arrays and Collections

The For Each statement is a variation of the For loop unique to VBA.

This statement comes in two distinct ﬂ avors. The ﬁ rst variation uses the

statement to loop over an array as demonstrated in the following

function:

Function ForEachSum(Rng As Range) As Double

Dim Element As Variant

Dim Sum As Double

Sum = 0

For Each Element In Rng.Value

Sum = Sum + Element

Next Element

ForEachSum = Sum

End Function

986 Chapter 40

Points to note:

•

The current member of the array is available to the statements within

the loop body through the loop variable (Element in the preceding

function).

•

The loop variable (Element in the preceding example) has to be of the

type Variant irrespective of the array type.

•

Changes to Element will not be reﬂ ected in the actual array.

•

You don’t need to know the number of dimensions or the range of

indices to loop over the array. The preceding function works on column

ranges (as in cell E2), row ranges (as in cell E3), and rectangular ranges

(as in cell E4) in the same fashion.

40.5.2 The For Each Statement in Use with Collections

The second version of the For Each statement loops over Collections:

ABCD E

1 4 7 6 <-- =ForEachSum(A2:A4)

2 5 8 12 <-- =ForEachSum(A2:C2)

3 6 9 45 <-- =ForEachSum(A2:C4)

FOREACHSUM DEMO

Sub ZeroRange( )

Dim Rng As Range

Dim Cell As Variant

Set Rng = ActiveCell.CurrentRegion

MsgBox “The current region is: ” & Rng.Address

For Each Cell In Rng

Cell.Formula = 0

Next Cell

End Sub

Here is what happens when you run the macro:

987 Objects and Add-Ins

And when OK is clicked:

Points to note:

•

Cell is a variable used to iterate over all the members of the

collection.

•

Cell has to be one of the following types: Variant, Object, or the speciﬁ c

type of element the Collection is made of.

•

Cell refers to the actual member of the Collection, and changes to Cell

will be reﬂ ected in the Collection.

•

CurrentRegion is a Range object that represents the current region.

The current region is a range bounded by any combination of blank rows

and blank columns.