Transcript Ch12.PowerPoint

Spreadsheet-Based Decision Support Systems
Chapter 12: Recording Macros
Prof. Name
Position
University Name
[email protected]
(123) 456-7890
Overview




12.1 Introduction
12.2 Macros
12.3 Customizing the Ribbon and Quick Access Toolbar
12.4 Summary
2
Introduction

Recording a macro.

Writing simple VBA procedures.

Creating event procedures.

Assigning macros to Form Control objects in Excel.

Customizing the Ribbon and Quick Access Toolbar by assigning macros.
3
Macros

Recording Macros

The VBA Code

Event Procedures
4
Recording Macros

Macros are technically defined as units of VBA code.

Macros can be thought of as a way to automate a series of actions in a
spreadsheet application.

Macros can either be created directly with VBA code in the Visual Basic
Editor, or recorded in Excel.

To record a macro, one must know exactly the actions to perform and
then use the Developer > Code > Record Macro command on the
Ribbon.
5
Figure 12.1

Let us record
a macro which
copies and
pastes data.

A dealer wants
to place the
data
highlighted in
yellow in a
newspaper
advertisement.
6
Preparation

First, review/practice the steps that one should take when recording the
macro
– Select the first three columns (Make, Model, and Year) of the Used Cars
table. While pressing the CTRL key, select the last column (Price)
– Either right-click and choose Copy from the shortcut menu, or use the
shortcut key CTRL-C to copy the selected data.
– Place the cursor in the first cell of the Newspaper Information table (cell C19).
– Paste the data either by right-clicking and choosing Paste from the shortcut
menu, or by using the shortcut key CTRL-V.
7
Record

Now we are ready to record the macro.
Click on: Developer > Code > Record Macro command on the Ribbon

When the Record Macro dialog box appears,
– Enter a name for the macro.
– Define its location.
– Determine any shortcut keys that you wish to associate with it.
8
Stop and Play

Once you begin recording, notice that the Record Macro button
transforms to a Stop Recording button.

After finishing the steps needed to copy and paste the information, you
can stop recording.
Click on: Developer > Code > Stop Recording command.

Once recorded, play the macro to
ensure it works correctly.
– Click on: Developer > Code >
Macros command.
– Select the macro from the list and
click Run.
9
Result

We can see that the macro works correctly.
10
VBA Code

Since we can study the VBA code generated when we record a macro,
we can easily learn how to create a similar macro directly from VBA by
copying the code generated.

We will now learn how to use Sort and Filter functions in VBA by first
recording a macro and then using the generated VBA code to create
similar macros starting in VBA.
11
Figure 12.5

Suppose there is a database for
the Miami Airport which lists Flight
Destinations, Number of Stops,
Class and Price.

We want to filter this information
so that we can do the following:
– View flights to Beijing
– View flights to Hong Kong
– View all flights

We also want to sort the data by:
– Number of Stops
– Price
12
Filtering

Begin by recording three macros to filter the data for specific flight
destinations.

To prepare for recording this macro, let review the steps that should be
performed:
–
–
–
–
Highlight the entire table (cells B3:E15)
Select Data > Sort & Filter > Filter command on the Ribbon.
Click on the filter drop-down button of the “Destination” column.
Select Beijing, Hong Kong, or All respectively from the filter drop-down list:
 1st Macro: “ViewBeijingFlights.”
 2nd Macro: “ViewHongKongFlights”
 3rd Macro: “ViewAllFlights”
13
Excel to VBE


We can check this recorded macro to ensure it works.
Now we can go to VBE to view the code generated.
14
VBA Code





The first line of code for all three macros, Range(“B3:E15”).
Select is an example of the Select Method for the Range Object.
The second line of code, Selection.AutoFilter, uses the Selection as the
object and AutoFilter as the method.
You can view a list of methods and properties associated with the
Selection object by typing Selection and a period.
The third line of code, Selection.AutoFilter Field:=1, Criteria1:=…, is an
example of an argument of a method.
– The Field is the column of data in the selected range that we want to filter from.
– The Criteria is the value of the filter drop-down arrow next to this field.

The last line of code selects a cell to de-highlight the data.
15
Figure 12.7

You will often find that some coding redundancies will happen with the
Macro Recorder.
– You can actually modify these macros directly in the VBA code by deleting
the first Selection.AutoFilter statement from each sub procedure.
16
Sorting

We will now record a macro to sort the data by number of stops.

To prepare for recording a macro to sort the data table, we review the
steps we will perform
– Highlight all data (B3:E15)
– Click on: Data > Sort & Filter > Sort command.
– Specify the sorting criteria and order in the Sort dialog box.
– SortByStops Macro:
 Select “Stops” from the “Sort by” list, select “Values” from the “Sort on” list, and
select “Largest to Smallest” from the “Order” list, then press OK to close the Sort
dialog box.
– SortByPrice Macro:
 Select “Price” from the “Sort by” list, select “Values” from the “Sort on” list, and
select “Largest to Smallest” from the “Order” list, then press OK to close the Sort
dialog box.
17
Figure 12.8

We can now test these macros and return to VBE to view the code
18
VBA Code

The first line of code for these macros selects the range we are interested
in sorting.

The second line of code clears all the SortFields objects in the active
worksheet.

The third line of code creates a new sort field and returns a SortFields
object.

The forth line of code uses the With construct to set several properties of
object Sort.

The last line of code selects a cell to de-highlight the data.
19
Figure 12.9

In the updated code, we see that the Key is specified by the range name
of the column that we are sorting by.

The Order is specified by “xlAscending” or “xlDescending.”
20
Figure 12.10

Let us try creating the macro originally from the VBA code and then
we can view its performance in Excel.

Suppose that we also want to filter the Business or Economy Class
flights.
21
Event Procedures

Event Procedures connect events or actions of an Excel object to a
specific macro of VBA code.
– Click
– Change
– Activate

For each Excel object selected in the Project Explorer, there is a list of
associated objects in a drop-down menu in the upper-left hand corner of
the Code Window.

After selecting an object from this list, you can view corresponding events
in the drop-down menu in the upper-right hand corner of the Code
Window.
22
Event Procedures (cont’d)

For example, the Activate event can be used on the Workbook object.

Once an object and event are selected, a sub procedure will be initiated.
23
Event Procedures (cont’d)

An event procedure is just like a sub procedure, except that it is
associated with a particular action of an Excel object.

Also, sub procedures can have almost any name while event procedures
have the name structure object_event.

Event procedures are necessary when building user interfaces.

Suppose we want to have some buttons on the screen that will perform
corresponding macros when clicked. To do this, we first create a button
and then use a Click event procedure.
24
Form and ActiveX Controls

To create a button in Excel, click on: Developer > Controls > Insert dropdown list.

There are several objects that can be placed on the spreadsheet: command
buttons, scroll bars, list boxes, etc.

Let’s choose the Command Button object by clicking on its icon at the
ActiveX Controls dialog box
25
Control Properties

Each control has its own set of properties which can be modified
– Name = name of the control, this will be used to identify it in VBA (ex:
cmdButton)
– Caption = text which appears on the control (ex:“Click Here”)

We can also change its font, size, and color from the list of properties.

When we work with ActiveX Controls, the Developer > Controls >
Design Mode button is activated.

To view the Properties window, click on the Developer > Controls >
Properties command.

The Click event procedure can now be associated with this new object
by titling the sub procedure
– Sub cmdButton_Click()

26
Command Button

Let us add a command button to
perform one of the filtering
macros we previously wrote in
VBA and set the following
properties.

For example:
– Name = cmdHongKong_Click()
– Caption = “View Hong Kong
Flights”
27
Event Procedure

We can now copy and paste the code for this macro to a new sub
procedure as follows.

This title is automatically generated when we select the command button
cmdHongKong from the list of objects and then select Click from the list
of events.
28
Figure 12.16



Return to Excel to test the functionality of this button.
Make sure that we are no longer in Design View. To activate or
deactivate the Design View, click on Developer > Controls > Design
View command.
Click “View Hong Kong Flights” and see that the filtering works correctly.
29
Figure 12.17

Similar Command Buttons can be created for the other macros we have
previously recorded.

Let now use the Form Controls listed on the Insert drop-down dialog box.

Click on the Button object, and
then click somewhere on the
spreadsheet.

The Assign Macro dialog box
appears.
30
Figures 12.18 and 12.19

Right-click on the Button to activate the shortcut menu. Use the Edit Text
option to edit the caption of this button.

Select the Format Control… option on the shortcut menu to modify the
format of this Button.
31
Buttons

Assign a macro
to each new
button
respectively.
32
Customizing the Ribbon and Quick Access Toolbar

We can create new tabs and groups on the Ribbon with icons designated
for each macro.

We can also add macros to the Quick Access Toolbar to reach faster to
macros that we frequently use.

To customize the Ribbon, right-click on the Ribbon, and then select
Customize the Ribbon from the list of options presented.
33
Figure 12.21

Excel gives the options to:
– Add groups of command buttons to an existing tab of the Ribbon.
– Create a new tab.
34
Creating a New Tab

Press the New Tab button in the bottom right side of Excel Options
dialog box.

Select the New Tab (Custom) from the Main Tabs list, and then click-on
the Rename button.

Title the tab “MyMacros”.
35
Figure 12.23

To add two new Groups under this tab: click on the New Group button.

Select the New Group (Custom) listed under MyMacros, and click on
the Rename button to title these groups.
36
Figure 12.24

We can also assign an icon to each command using the selection
provided by Excel.

Use the Move up arrow of Excel Options dialog box to relocate the new
tab right after the Home tab in the Ribbon.
37
Figure 12.25

Customize the Quick Access Toolbar.
– For example, to add the “ViewAllFFlights” macro, right click on MyMacros >
Filter > “All” command.
– Select the Add to Quick Access Toolbar from the list of options presented.
38
Summary



Macros are technically defined as units of VBA code.
Macros can either be created directly with VBA code in the Visual Basic
Editor, or recorded in Excel.
Steps to Record a Macro:
– 1. Click on: Developer tab > Code group > Record Macro button.
– 2. Perform a sequence of actions in Excel.
– 3. Click on: Developer tab > Code group > Stop Recording button.




Events are actions that can take place in the Excel window that cause an
action to occur in the VBA code.
An event procedure is just like a sub procedure, except that it is
associated with a particular action of an Excel object.
Creating buttons as a user interface can greatly aid a user unfamiliar with
macros to perform desired functions.
The user can customize the Ribbon or the Quick Access Toolbar by
adding icons of macros that are frequently used.
39
Additional Links

(place links here)
40