Simon Benninga. Financial Modelling 3-rd edition

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

Macros and User Interaction

38.1 Overview

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

operations in Excel. Macros are also called subroutines; we use the

names interchangeably. Some of the VBA user interaction routines are

covered in sections 38.3 and 38.4. Modules (brieﬂ y mentioned in Chapter

36) are given fuller coverage in section 38.5.

38.2 Macro Subroutines

The ﬁ rst line of a macro subroutine gives the macro a name and lists the

parameters if any. It is very similar to the ﬁ rst line of a function:

Sub MacroName()

The last line, put in automatically by VBA, indicates the end of the

macro and so looks (very appropriately) like this:

End Sub

Separating the ﬁ rst and last line are the statements that the macro

executes. The following is a very simple macro that puts a message on

the screen.

Sub SayHi()

MsgBox “Hi”, , “I say Hi”

End Sub

The subroutine introduces a built-in VBA macro called MsgBox. It

also introduces the way a macro is activated (called) from a VBA routine.

MsgBox is named as a command on a line followed by its list of argu-

ments separated by commas. Notice the syntax:

920 Chapter 38

MsgBox “Hi”, , “I say Hi”

There are three arguments:

•

“Hi” is the message that will be displayed.

•

The second argument is empty: notice the space between the commas.

This argument can be used to deﬁ ne buttons for the message box. This

topic is discussed in section 38.3.

•

The third argument is “I say Hi”—this is the message box title.

A macro can be activated (run) from an Excel worksheet in various

ways. The simplest way of running a macro is from the tools menu

(Tools|Macro|Macros), or by using the keyboard shortcut [Alt + F8].

Either way, the macro selection box appears. The box lists all available

macros alphabetically. Find our macro, click on its name, and click the

run button.

921 Macros and User Interaction

And this is what you will see:

At this point Excel is locked up. You have to click the OK button

before you can proceed.

38.2.1 Keyboard Shortcut for Macros

Using a keyboard shortcut is a faster way to make a macro run. To attach

a shortcut to our macro:

1. Select the Options button from the macro selection box.

2. Type a character in the provided space, and click OK.

3. Close the macro selection box using the corner X.

You can now activate the macro using the shortcut ([Ctrl + h] in our

case).

922 Chapter 38

38.2.2 Attaching Macros to a Toolbar

You can attach macros to a button on a toolbar, and later activate the

macro by clicking the button. To attach SayHi to a button, open the

toolbars menu with View|Toolbars|Customize and select the Commands

pane.

Scroll down the Categories subwindow to the Macros item.

923 Macros and User Interaction

Select the Custom Button and drag it to a toolbar. (Keep the left

mouse button pressed while dragging.)

924 Chapter 38

Right click on the newly inserted button and select AssignMacro from

the menu.

The button will be inserted here. At this

point the left mouse button has not been

released

925 Macros and User Interaction

Select our macro from the list and click the OK button. Click the Close

button. SayHi is now attached to the smiling button on the toolbar.

926 Chapter 38

The image on the button and the tool tip attached to it can also be

customized. For details refer to the Excel Help ﬁ le.

38.3 User Output and the MsgBox Function

The MsgBox function displays a message on the screen and returns a

value based on the button clicked. Some of the different options avail-

able with this function are demonstrated in the following macros:

927 Macros and User Interaction

Sub MsgBoxDefault()

Dim Temp As Integer

Temp = MsgBox(“Default Message”, ,

“Default Title”)

MsgBox “The value returned by MsgBox is:“ _

& Temp

End Sub

Note The default conﬁ guration of MsgBox produces one OK button.

The Default title is “Microsoft Excel.” Clicking the OK button makes

MsgBox return the value 1.

Sub MsgBoxOKCancel()

Dim Temp As Integer

Temp = MsgBox(“Default Message”,

vbOKCancel)

MsgBox “The value returned by MsgBox is:“ _

& Temp

End Sub