Simon Benninga. Financial Modelling 3-rd edition

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

988 Chapter 40

•

A complete explanation of the use of the Set statement is beyond the

scope of this book. For our purposes just preﬁ x the reserved word Set to

all object assignments. If you don’t, the following might ensue:

40.5.3 The Workbooks Collection and the Workbook Object

All the currently open workbooks are represented by a Workbook object

in the Workbooks Collection. The following macro lists all open work-

books in a column of cells starting from the active cell:

Sub ListOpenWorkbooks( )

Dim i As Integer

Dim Element As Workbook

ActiveCell.Item(2, 1).Formula = _

“List of open Workbooks”

ActiveCell.Item(4, 1).Formula = _

“Created on:” & FormatDateTime(Date, _

vbLongDate) _

989 Objects and Add-Ins

This is the worksheet after the macro was run:

& “ At: “ & FormatDateTime(Time, _

vbLongTime)

With ActiveCell.Item(2, 1).Font

.Bold = True

.Name = “Arial”

.Size = 12

End With

i = 5

For Each Element In Workbooks

ActiveCell.Item(i, 1).Formula = _

Element.FullName

i = i + 1

Next Element

End Sub

CBA

LISTOPENWORKBOOKS IN ACTION

ListOpenWorkbooks()

List of open Workbooks

Created on:Saturday, September 08, 2007 At: 4:23:02 PM

C:\simon\WORK\FM3\Disk with book\Chapter files\fm3_chapter 40.xls

C:\simon\WORK\FM3\Disk with book\Chapter files\fm3_chapter38.xls

C:\simon\WORK\FM3\Disk with book\Chapter files\fm3_chapter07.xls

Lines to note:

ActiveCell.Item(4, 1).Formula = _

“Created on:” & FormatDateTime(Date, vbLongDate) _

& “ At:” & FormatDateTime(Time, vbLongTime)

990 Chapter 40

•

The Date function returns the current system date.

•

The Time function returns the current system time.

•

The FormatDateTime function formats Date and Time variables for

display.

For Each Element In Workbooks

ActiveCell.Item(i, 1).Formula = Element.

FullName

i = i + 1

Next Element

The For statement loops over the entire Workbooks Collection. On

each iteration, Element is one of the Workbook objects in the Collection.

FullName is a property of the Workbook object containing the full path

name of the workbook.

The next macro adds a workbook to the Workbooks Collection:

Sub AddWorkbook( )

Dim Wkbk As Workbook

Dim OldWkbk As Workbook

Set OldWkbk = ActiveWorkbook

Set Wkbk = Workbooks.Add

OldWkbk.Activate

MsgBox Wkbk.FullName & “ Added”

End Sub

Adding a workbook to the Workbooks Collection makes the newly

added workbook the active workbook. If we want to stay where we are,

we need to reactivate the old workbook.

40.5.4 The Worksheets Collection and the Worksheet Object

All the worksheets in a workbook are Worksheet objects in the Work-

sheets Collection that is a property of the Workbook object. We can use

991 Objects and Add-Ins

the Worksheets Collection without an object as a short form for

ActiveWorkbook.Worksheets.

40.6 Names

In Excel you can use user-deﬁ ned names to refer to a cell or a range of

cells.

Use easy-to-understand names, such as Products, to refer to hard-

to-understand ranges, such as Sales!C20:C30. Using names can make

formulas easy to read: Compare the formula =sum(‘sheet12’!a10:a10) to

=sum(lastYearSales). See appendix 2 for more examples. This section

deals with the VBA side of names.

40.6.1 Naming a Range Using a Macro

The following macro names the current region:

Sub NameCurrentRegion( )

Dim Rng As Range

Dim MyName As String

Set Rng = ActiveCell.CurrentRegion

MyName = InputBox(“The current region is: “ & _

Rng.Address & Chr(13) & “Enter Name please: “, _

“Namer”, “MyName”)

Names.Add Name:=MyName, RefersTo:=“=” & _

Rng.Address

End Sub

4. And a lot more, but that topic is beyond the scope of this book.

992 Chapter 40

Once we click OK, we can select “MyName” from the names box and

see that it has been applied to the right range.

The interesting line in the macro is

993 Objects and Add-Ins

Names is a Collection of all the names in the active workbook. Add

is a method of the Names Collection used to add members to the collec-

tion. We use only the ﬁ rst two parameters of the method. The ﬁ rst param-

eter MyName is the name to add to the Names Collection. The second

parameter is a string containing the address to which the added name is

to refer, preceded by “=”.

40.6.2 Looking for Deﬁ ned Names

The name “Ben” has been deﬁ ned in the “IsName” worksheet as dem-

onstrated by the insert-name dialogue presented in the next screen

capture:

Names.Add MyName, “=” & Rng.Address

The following function looks for a deﬁ ned name in the current work-

book. The function returns the Boolean value “True” if the name is

deﬁ ned, and “False” if it is not part of the Names Collection.

994 Chapter 40

Note that

•

Names are case sensitive. Thus the function recognizes “Ben” as a

range name but not “ben.”

•

The Exit For statement can be used with For Each.

40.6.3 Referring to a Named Range

The following function, modeled on MeanReturn of section 40.3.1, com-

putes the average return on a named range of asset prices.

Function IsName(Name As String) As Boolean

Dim Element As Variant

Dim Flag As Boolean

Flag = False

For Each Element In Names

If Name = Element.Name Then

Flag = True

Exit For

End If

Next Element

IsName = Flag

End Function

AB C

Name IsName

Ben TRUE <-- =IsName(A3)

Simon FALSE <-- =IsName(A4)

ben FALSE <-- =IsName(A5)

ISNAME IN ACTION

995 Objects and Add-Ins

Note the new way to use the Range property introduced in the macro:

Function NamedMeanReturn(RangeName As String)

As Double

Dim Rng As Range

Dim Prices As Variant

Dim Temp As Double

Dim i As Integer

Set Rng = Range(RangeName)

Prices = Rng.Value

Temp = 0

For i = 2 To UBound(Prices, 1)

Temp = Temp + _

(Prices(i, 1) - Prices(i - 1, 1)) / _

Prices(i - 1, 1)

Next i

NamedMeanReturn = Temp / (UBound(Prices,

1) - 1)

End Function

AB C D

Asset1 Asset2

100 100 0.1 <-- =meanreturn(A3:A7)

110 120 0.2 <-- =meanreturn(B3:B7)

121 144 0.1 <-- =NamedMeanReturn("Asset1")

133.1 172.8 0.2 <-- =NamedMeanReturn("Asset2")

146.41 207.36 0.2 <-- =NamedMeanReturn("aSsEt2")

NAMEDMEANRETURN IN ACTION

Set Rng = Range(RangeName)

40.7 Using the Object Browser

The Object Browser is a convenient way to learn about the different

objects that are available for use in VBA. The object browser can be

activated by pressing its button on the VBA toolbar or from the menu

(View|Object Browser). The toolbar button looks like this:

996 Chapter 40

The object browser window is made of two panes, a selection box, and

a few buttons. From the selection box you can choose a master set of

objects to look at (two master sets, VBA and Excel, are always available).

Select the VBA master set to look at objects that are internal to the

working of VBA. The Excel master set deals with objects that are speciﬁ c

to Excel. (It’s all part of a Microsoft master plan, no pun intended, where

VBA is a center of many applications, not just Excel.)

997 Objects and Add-Ins

Once a master set of objects is selected, the two other panes change

their contents to reﬂ ect the master set selected. The left pane is a list of

categories (sorted in ascending alphabetical order). The right pane is a

list of Properties or Methods attributable to the selected object in the

left pane. Clicking the question-mark button causes the appropriate help

screen to be displayed. The paste button causes the complete “calling

sequence” for the selected Method or Property to be pasted at the

current insertion point in the Module.

40.8 References to External Functions in Excel

All the macros and functions we have written so far were directly avail-

able only in the workbook in which they were written. Referring to a

function in another workbook is almost like referring to a cell in another

workbook, but unlike a cell reference the workbook needs to be open

for the link to work. In later versions of Excel, using referents from

external workbooks generates a few security warnings when you open a

workbook containing the external reference. In light of this fact, we are

going to use two separate workbooks for this section. The ﬁ rst workbook,

fm3_chapter40_server.xls, will contain the externally referred to cells

and functions, and from now on shall be referred to as the Server. The

second workbook, fm3_chapter40_client.xls, will make use of the func-

tions and cells, and from now on shall be referred to as the Client. The

next screen shot demonstrates the two workbooks opened simultane-

ously in Excel: