Simon Benninga. Financial Modelling 3-rd edition

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

928 Chapter 38

As previously noted, the second argument to MsgBox determines

which buttons are displayed. This incarnation of the demo macro uses

the constant vbOKCancel to produce the two buttons OK and Cancel.

Note that if the Cancel button is clicked, MsgBox returns the value 2.

The following is a list of some of the constants that can be used as

the second argument of MsgBox, together with the message box they

produce:

VbOKOnly

VbOKCancel

VbAbortRetryIgnore

VbYesNoCancel

VbYesNo

VbRetryCancel

VbCritical

VbQuestion

VbExclamation

930 Chapter 38

The values that can be returned by MsgBox are as follows:

VbInformation

Constant Value Description

vbOK 1 OK button clicked

vbCancel 2 Cancel button clicked

vbAbort 3 Abort button clicked

vbRetry 4 Retry button clicked

vbIgnore 5 Ignore button clicked

vbYes 6 Yes button clicked

vbNo 7 No button clicked

38.4 User Input and the InputBox Function

InputBox is an internal VBA function used to get textual information

from the user into a variable in a subroutine (a macro by any other

name . . .). The workings of the function are demonstrated in the follow-

ing present-value calculator macro. The macro PVCalculator calculates

(. )105

∑

, where CF is a number entered by the user:

931 Macros and User Interaction

Note the syntax:

Sub PVCalculator ()

Dim CF

CF = InputBox (“Enter the cash ﬂ ow value please”, _

“PV calculator”, “100”)

MsgBox “The present value of: ” & CF & _

“ At 5% for 10 periods is: ” & _

Round (Application.PV (0.05, 10, -CF), 2), _

vbInformation, “PV calculator”

End Sub

CF = InputBox (“Enter the cash ﬂ ow value please”, _

“PV calculator”, “100”)

•

“Enter . . . please,” the ﬁ rst argument to InputBox, is the message to

display.

•

“PV calculator,” the second argument, is the title for the box.

•

“100,” the third argument, is the default string to place in the box. If

you do not replace this by some other value, running the macro should

result in the following:

932 Chapter 38

At this point you can replace “100” with some other number. (In this

example, we’ve chosen to leave it.) Clicking on the OK box results in the

following box:

The macro also introduces a new VBA operator &. This operator is

used to concatenate (combine) its two operands into one string of char-

acters. It merits a small demo function on its own. Notice that nonstring

operands are converted into strings.

Function ConcatDemo(Parameter1, Parameter2)

ConcatDemo = Parameter1 & Parameter2

End Function

ABC D

Parameter1 Parameter2

1212<

=ConcatDemo(A3,B3)

Ben Jerry BenJerry <

=ConcatDemo(A4,B4)

Ben 1 Ben1 <

=ConcatDemo(A5,B5)

Jerry 2 Jerry2 <

=ConcatDemo(A6,B6)

CONCATDEMO IN ACTION

38.5 Modules

VBA organizes user-deﬁ ned functions and subroutines in units called

modules. We can (and sometimes should) have more than one module

in a VBA project (that is, the part of the workbook that has our functions

933 Macros and User Interaction

and subroutines). Modules have names: By default VBA uses the name

“Module” followed by a number to indicate the module’s name, but you

might ﬁ nd it useful (as we have done on the workbook accompanying

this book) to give them somewhat more descriptive names.

To rename a module (in the VBA editor), select the module on the

project explorer pane.

If the project explorer pane is not visible, select Project Explorer from

the View menu.

934 Chapter 38

Once a module is selected, the module’s list of properties should

appear in the properties pane. If the properties pane is not visible, select

Properties Window from the View menu. Click on the module’s name

(it should be the only property available) and change it (use one word

only, and only digits and alphabetic characters).

Once you tap the Enter key, the name is changed. Notice the change

in the project explorer.

935 Macros and User Interaction

Hint Try to give your modules unique names. If a module called Tom

has a function called Tom in it, the function Tom will not be available to

the workbook. One common practice is to start module names (and only

module names) with M_.

38.6 Summary

A macro is a VBA user routine used to automate routine or repetitive

operations in Excel. VBA provides two important and very ﬂ exible func-

tions for user interaction: MsgBox and InputBox. VBA groups macros

and functions in units called modules. Keeping related functions and

macros grouped is useful when dealing with large projects. All these

topics, explored in this chapter, will help you in ﬁ nancial programming

in Excel.

Exercises

1. Write a macro that displays the following message box. The message box should be

on top of all other windows, and it should prevent the user from doing anything in

any application until one of the buttons is clicked.

Hint You need to use some options of MsgBox that were not covered in the text.

Use the VBA Help system.

2. Write a present-value calculator macro similar to the one that appears in section

38.4. However—as illustrated here—your macro should ask the user for the cash

ﬂ ow value, the interest rate, and the number of periods. It should then display the

result in a message box. Sensible default values should be supplied for all arguments.

Do not use the Excel function PV; write your own present value function and use

it. A reminder:

PV CF r n

(,,)

()

∑

936 Chapter 38

You can use the PV function provided by Excel, as we did, to verify the correctness

of your macro.

3. Rewrite the macro in the previous exercise so that the user interface is as demon-

strated in the following screen shots. Some of the functions needed to write the

macro were not covered in the text. We used the following functions:

•

Val—A function used to convert a string of digits to a number.

•

Left—A function used to return the left part of a string.

•

Right—A function used to return the right part of a string.

•

FormatPercent—A function used to format a number.

•

FormatCurrency—A function used to format a number.

More information about these functions is available from the VBA Help ﬁ le. We

recommend you use it.

937 Macros and User Interaction

Note Your computer might display a different currency symbol.

4. Rewrite the macro in the previous exercise so it deals properly with the Cancel

button.

•

A simple version of the new macro will abort the macro if Cancel is clicked in

any stage.

•

A more sophisticated version of the new macro will allow the user to reenter the

data from scratch.

•

The most sophisticated version of the new macro will allow reentering the data

using the old data as a default.

Note The last version is a slightly more complicated exercise using loops within

loops.

5. Write a payment schedule calculator macro. The macro is to ask the user for the

sum of the loan, the number of payments, and the interest rate. Assume payment at

the end of the period. The output should look like the following example:

Hints

•

You may want to use the worksheet function PMT.

•

The following macro and its output might be of interest.