Simon Benninga. Financial Modelling 3-rd edition

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

1018 Chapter 40

Appendix 1: The Excel Object Model

This appendix presents the Excel object model—the way an Excel appli-

cation looks from the VBA point of view. It shows an overview of the

Excel application and can be used to ﬁ nd the exact name and pedigree

of an object. This extract from the Excel Help ﬁ les presents just the very

top of the tree, and in the original Help ﬁ le clicking on one of the

branches expands it and provides more information about the branch.

1019 Objects and Add-Ins

1020 Chapter 40

Appendix 2: Extracts from the Help Files for Some Methods

The content of this appendix is a lightly edited version of the Excel VBA

Help ﬁ le.

BorderAround Method

Adds a border to a range and sets the Color, LineStyle, and Weight

properties for the new border.

BorderAround (LINESTYLE, WEIGHT, COLORINDEX, COLOR)

linestyle Optional. The line style for the border. Can be one of these

constants:

xlContinuous xlDash xlDashDot xlDashDotDot xlDot

xlDouble xlLineStyleNone xlSlantDashDot xlLineStyleNone

weight Optional. The border weight. Can be one of these constants:

xlHairline xlMedium xlThick xlThin

colorindex Optional. The border color, as an index into the current

color palette or as one of these constants:

xlColorIndexAutomatic xlColorIndexNone

color Optional. The border color, as an RGB value.

Remarks

You must specify either ColorIndex or Color, but not both.

You can specify either LineStyle or Weight, but not both. If you don’t

specify either argument, Microsoft Excel uses the default line style and

weight.

This method outlines the entire range without ﬁ lling it in.

Range—Object and Collection

Represents a cell, a row, a column, a selection of cells containing one or

more contiguous blocks of cells, or a 3-D range. The following properties

and methods for returning a Range object are described in this section:

Range property

Cells property

1021 Objects and Add-Ins

Range and Cells

Offset property

Union method

Use Range(arg), where arg names the range, to return a Range object

that represents a single cell or a range of cells. The following example

places the value of cell A1 in cell A5.

Worksheets(“Sheet1”).Range(“A5”).Value = _

Worksheets(“Sheet1”).Range(“A1”).Value

The following example ﬁ lls the range A1 : H8 with random numbers

by setting the formula for each cell in the range. When it’s used without

an object qualiﬁ er (an object to the left of the period), the Range prop-

erty returns a range on the active sheet. If the active sheet isn’t a work-

sheet, the method fails. Use the Activate method to activate a worksheet

before you use the Range property without an explicit object qualiﬁ er.

Worksheets(“Sheet1”).Activate

Range(“A1:H8”).Formula = “=Rand()” ‘Range on

‘the active sheet

The following example clears the contents of the range named

Criteria.

Worksheets(1).Range(“Criteria”).ClearContents

If you use a text argument for the range address, you must specify the

address in A1-style notation (you cannot use R1C1-style notation).

1022 Chapter 40

Cells Property

Use Cells(row, column) where row is the row index and column is the

column index, to return a single cell. The following example sets the

value of cell A1 to 24.

Worksheets(1).Cells(1, 1).Value = 24

The following example sets the formula for cell A2.

ActiveSheet.Cells(2, 1).Formula = “=Sum(B1:B5)”

Although you can also use Range(“A1”) to return cell A1, there may

be times when the Cells property is more convenient because you can

use a variable for the row or column. The following example creates

column and row headings on Sheet1. Notice that after the worksheet has

been activated, the Cells property can be used without an explicit sheet

declaration (it returns a cell on the active sheet).

Sub SetUpTable()

Worksheets(“Sheet1”).Activate

For TheYear = 1 To 5

Cells(1, TheYear + 1).Value = 1990 + _

TheYear

Next TheYear

For TheQuarter = 1 To 4

Cells(TheQuarter + 1, 1).Value = “Q” & _

TheQuarter

Next TheQuarter

End Sub

Although you could use Visual Basic string functions to alter A1-style

references, it’s much easier (and much better programming practice) to

use the Cells(1, 1) notation.

1023 Objects and Add-Ins

Use expression.Cells(row, column), where expression is an expression

that returns a Range object, and row and column are relative to the

upper-left corner of the range, to return part of a range. The following

example sets the formula for cell C5.

Worksheets(1).Range(“C5:C10”).Cells(1, 1). _

Formula = “=Rand()”

Range and Cells

Use Range(cell1, cell2), where cell1 and cell2 are Range objects that

specify the start and end cells, to return a Range object. The following

example sets the border line style for cells A1 : J10.

With Worksheets(1)

.Range(.Cells(1, 1), _

.Cells(10, 10)).Borders.LineStyle = _

xlThick

End With

Notice the period in front of each occurrence of the Cells property. The

period is required if the result of the preceding With statement is to be

applied to the Cells property—in this case, to indicate that the cells are

on worksheet one (without the period, the Cells property would return

cells on the active sheet).

Offset Property

Use Offset(row, column), where row and column are the row and column

offsets, to return a range at a speciﬁ ed offset to another range. The fol-

lowing example selects the cell three rows down from and one column

to the right of the cell in the upper-left corner of the current selection.

You cannot select a cell that isn’t on the active sheet, so you must ﬁ rst

activate the worksheet.

1024 Chapter 40

Union Method

Use Union(range1, range2, . . .) to return multiple-area ranges—that is,

ranges composed of two or more contiguous blocks of cells. The follow-

ing example creates an object deﬁ ned as the union of ranges A1 : B2 and

C3 : D4, and then selects the deﬁ ned range.

Worksheets(“Sheet1”).Activate

‘Can’t select unless the sheet is active

Selection.Offset(3, 1).Range(“A1”).Select

Dim r1 As Range, r2 As Range, myMultiAreaRange _

As Range

Worksheets(“sheet1”).Activate

Set r1 = Range(“A1:B2”)

Set r2 = Range(“C3:D4”)

Set myMultiAreaRange = Union(r1, r2)

myMultiAreaRange.Select

If you work with selections that contain more than one area, the Areas

property is very useful. It divides a multiple-area selection into individual

Range objects and then returns the objects as a collection. You can use

the Count property on the returned collection to check for a selection

that contains more than one area, as shown in the following example.

Sub NoMultiAreaSelection()

NumberOfSelectedAreas = Selection.Areas.Count

If NumberOfSelectedAreas > 1 Then

MsgBox “You cannot carry out this command “ & _

“on multi-area selections”

End If

End Sub

1025 Objects and Add-Ins

Names in Excel—From the Help Files

A deﬁ ned name in a formula can make it easier to understand

the purpose of the formula. For example, the formula =SUM

(FirstQuarterSales) might be easier to identify than =SUM(C20:

C30).

Names are available to any sheet. For example, if the name Pro-

jectedSales refers to the range A20 : A30 on the ﬁ rst worksheet in a

workbook, you can use the name ProjectedSales on any other sheet

in the same workbook to refer to range A20 : A30 on the ﬁ rst

worksheet.

Names can also be used to represent formulas or values that do not

change (constants). For example, you can use the name SalesTax to rep-

resent the sales tax amount (such as 6.2 percent) applied to sales

transactions.

You can also link to a deﬁ ned name in another workbook, or deﬁ ne a

name that refers to cells in another workbook. For example, the formula

=SUM(Sales.xls!ProjectedSales) refers to the named range

ProjectedSales in the workbook named Sales.

Note By default, names use absolute cell references. (Absolute cell

reference: In a formula, the exact address of a cell, regardless of the

position of the cell that contains the formula. An absolute cell reference

takes the form $A$1.)

Guidelines for Names

•

What characters are allowed? The ﬁ rst character of a name must be a

letter, an underscore character (_), or a backslash (\). Remaining char-

acters in the name can be letters, numbers, periods, and underscore

characters.

•

Can names be cell references? Names cannot be the same as a cell

reference, such as Z$100 or R1C1.

•

Can more than one word be used? Yes, but spaces are not allowed.

Underscore characters and periods may be used as word separators—for

example, Sales_Tax or First.Quarter.

•

How many characters can be used? A name can contain up to 255

characters.

1026 Chapter 40

Note If a name deﬁ ned for a range contains more than 253 characters,

you cannot select it from the Name box.

•

Are names case sensitive? Names can contain uppercase and lowercase

letters. Microsoft Excel does not distinguish between uppercase and

lowercase characters in names. For example, if you have created the

name Sales and then create another name called SALES in the same

workbook, the second name will replace the ﬁ rst one.

Using Existing Row and Column Labels as Names

When you create a formula that refers to data in a worksheet, you can

use the column and row labels in the worksheet to refer to the data. For

example, to calculate the total value for the Product column, use the

formula =SUM(Product).

Using a Label

Or if you need to refer to the Product 3 amount for the East division

(that is, the value 110.00), you can use the formula =Product 3 East.

The space in the formula between “Product 3” and “East” is the intersec-

tion operator. (Operator: A sign or symbol that speciﬁ es the type of cal-

culation to perform within an expression. There are mathematical,

comparison, logical, and reference operators.) This operator designates

that Microsoft Excel should ﬁ nd and return the value in the cell at the

intersection of the row labeled East and the column labeled Product 3.

Note By default, Excel does not recognize labels in formulas. To use

labels in formulas, click Options on the Tools menu, and then click the

1027 Objects and Add-Ins

Calculation tab. Under Workbook options, select the Accept labels in

formulas check box.

Stacked Labels

When you use labels for the columns and rows on your worksheet, you

can use those labels to create formulas that refer to data on the work-

sheet. If your worksheet contains stacked column labels—in which a

label in one cell is followed by one or more labels below it—you can use

the stacked labels in formulas to refer to data on the worksheet. For

example, if the label West is in cell E5 and the label Projected is in cell

E6, the formula =SUM(West Projected) returns the total value for

the West Projected column.

the order for stacked labels When you refer to information by using

stacked labels, you refer to the information in the order in which the

labels appear, from top to bottom. If the label West is in cell F5 and the

label Actual is in cell F6, you can refer to the actual ﬁ gures for West

by using West Actual in a formula. For example, to calculate the average

of the actual ﬁ gures for West, use the formula =AVERAGE(West

Actual).

Using Dates as Labels

When you label a range by using the Label Ranges dialogue box and the

range contains a year or date as a label, Excel deﬁ nes the date as a label

by placing single quotation marks around the label when you type the

label in a formula. For example, suppose your worksheet contains

the labels 2007 and 2008 and you have speciﬁ ed these labels by using the

Label Ranges dialogue box. When you type the formula =SUM(2008),

Excel automatically updates the formula to =SUM(‘2008’).