Transcript EIN 4905/ESI 6912 Decision Support Systems Excel

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 toolbars and menu options
12.4 Summary
2
Introduction

Recording a macro.

Writing simple VBA procedures.

Creating event procedures.

Assigning macros to drawing objects in Excel.

Customizing toolbars and menu items 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, we must know exactly the actions we wish to perform
and then use the Macro Recorder.
5
Figure 12.1

Let us record a macro which copies and pastes data.

A dealer wants to send the information from the first three columns and
last column of the data table to the newspaper.
6
Preparation

First we should review/practice the steps we will take when recording the
macro
–
–
–
–
–
–
–
–
Highlight the first three columns of data with the cursor (C4:E13)
Copy (using CTL+C, right-click and Copy, or the Edit option)
Place the cursor in cell C19
Paste (using CTL+P, right-click and Paste, or Edit option)
Highlight the last column (K4:K13)
Copy
Place cursor in cell F19
Paste
7
Record

Now we are ready to record the macro
– Tools > Macro > Record New Macro
– Record button from VBE Toolbar

When the Record Macro dialog box appears, we enter a name for the
macro.
8
Stop

Once you begin recording, notice that the Record button transforms to a
Stop button.

After finishing the steps needed to copy and paste the information, you
can stop recording.
– Tool > Macro > Stop Recording Macro
– Stop button from the VBE Toolbar
9
Play

Once you have recorded a macro, you can play it to ensure it works
correctly.
– Tools > Macros > Macros
– Play button from VBE Toolbar
10
Result

We can see that the macro works correctly.
11
VBA Code

As we learned earlier, each time a macro is recorded in Excel, VBA code
is automatically generated.

Let us review what code was generated for this macro
– Go the VBE window
– A module has been created in the Project Explorer
– Select the module to see the code in the Code Window
12
VBA Code (cont)
Sub CarCopyPaste()

Range("C5:E14").Select
Selection.Copy
Range("C20").Select
ActiveSheet.Paste
Range("K5:K14").Select
Selection.Copy
Range("F20").Select
ActiveSheet.Paste
Range("A1").Select
End Sub

13
This is the initial statement of every
sub procedure.
– The Select method of the
Range object selects the
specified range of cells.
– The Copy method is used on the
Selection object.
– The Paste method is used on
the ActiveSheet object.
– These steps are repeated to
copy and paste the last column.
– This last cell selection can be
added to avoid leaving the cells
highlighted.
This is the last statement of every
sub procedure.
Starting in VBA

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.
14
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 be able 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 be able to sort
the data by
– Number of Stops
– Price
15
Filtering

We will begin by recording the macro to filter the data for viewing Beijing
flights only.

To prepare for recording a macro to filter the data table, we review the
steps we will perform
–
–
–
–
–
Highlight all data (B3:E15)
Data > Filter > AutoFilter
Select the drop-down button for “Destination” column
Filter for Beijing flights
Select cell A1
16
Excel to VBE


We can check this recorded macro to ensure it works.
Now we can go to VBE to view the code generated.
17
Creating New Code

Now that we know the basic structure of the code, we can simply modify
it to accomplish the other filtering macros.
Sub ViewBeijingFlights()
Range("B3:E15").Select
Selection.AutoFilter Field:=1,
Criteria1:="Beijing"
Range("A1").Select
End Sub

The Sub titles will change for each new
macro
– The AutoFilter method will be
used on the same selection to
generate the filtering drop-down
arrows
– Field:=1 denotes the “Destination”
column, we can modify this value
– Criteria1:=“Beijing” is the value
which is filtered, we can also
modify this value
18
New Macros from VBA

We can now create a macro to view Hong Kong flights and view All
flights by using the code from the recorded macro.
-----------------------------------------------------Sub ViewHongKongFlights()
Range("B3:E15").Select
Selection.AutoFilter Field:=1, Criteria1:="Hong Kong"
Range("A1").Select
End Sub
-----------------------------------------------------Sub ViewAllFlights()
Range("B3:E15").Select
Selection.AutoFilter Field:=1
Range("A1").Select
End Sub
------------------------------------------------------
19
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)
Data > Sort
Select “Stops” from the Sort by list
Select Descending as the order
Select cell A1
20
Excel to VBE

We can check this recorded
macro to ensure it works.

Now we can go to VBE to view
the code generated.
21
Creating New Code

Now that we know the basic structure of the code, we can simply modify
it to accomplish the other sorting macros.
Sub SortByStops()

Range("B3:E15").Select
Selection.Sort
Key1:=Range("C4"),
Order1:=xlDescending
Range("A1").Select
End Sub
22
The Sub titles will change for each new
macro
– The Sort method will be used on
the same selection
– Key1:=Range(“C4”) denotes the
“Stops” column
– Order1:= xlDescending is the
order in which the data will be
sorted
New Macro from VBA

We can now create a macro to sort by price by using the code from
the recorded macro.
-----------------------------------------------------Sub SortByPrice()
Range("B3:E15").Select
Selection.Sort Key1:=Range("E4"), Order1:=xlDescending
Range("A1").Select
End Sub
------------------------------------------------------
23
Event Procedures

Event Procedures connect events or actions of an Excel object to a
specific macro of VBA code.
– Click
– Change
– Activate

To find a list of available/applicable event procedures in VBE, look at the
top of the Code Window.
– Choose and object from the object list
– Choose an event from the event list
24
Event Procedures (cont)

For example, the Activate event can be used on the Worksheet object.

Once an object and event are selected, a sub procedure will be initiated.
25
Control Toolbox

The Control Toolbox is a set of objects to which
event procedures are usually assigned.
– View > Toolbars > Control Toolbox
– Icon from VBA Toolbar
– Click and drag from toolbar to spreadsheet

There are many controls available to put on the
spreadsheet
–
–
–
–
Text, labels
Spin buttons, toggle buttons
List box, combo box
Check boxes, option buttons
26
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”)

The Click event procedure can now be associated with this new object by
titling are sub procedure
– Sub cmdButton_Click()
27
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.
– Name = cmdHongKong
– Caption = “View Hong Kong
Flights”
28
Event Procedure

We can now copy and paste the code for this macro to a new sub
procedure as follows.
Sub cmdHongKong_Click()
Range("B3:E15").Select
Selection.AutoFilter Field:=1, Criteria1:="Hong Kong"
Range("A1").Select
End Sub

This title can be automatically generated if we select the command
button cmdHongKong from the list of objects and then select Click from
the list of events.
29
Drawing Controls

We can also make a button instead of using a command button from the
control toolbox.

Create rectangle and then add text to it by right-clicking and choosing
Add Text; then format the rectangle as desired.
30
Assigning the Macro

You can now simply right-click on the button you created and select
Assign Macro from the drop-down list.

A list of all macros in the open workbooks will appear, select one and
your button has become activated.
31
Buttons


We can now create buttons for each of the macros we previously wrote.
Assign a macro to each new button respectively.
32
Customizing Toolbars and Menu Options

Create a toolbar with icons associated with a particular macro.
– View > Toolbars > Customize

There are three tabs in the Customize window:
– Toolbars = list of all current toolbars, option to create a New toolbar
– Commands = list of commands to add to a toolbar, option to assign macros,
option to create New Menu
– Options = general options
33
Custom Toolbar

Let us create a new toolbar and place some icons to associate with the
filtering macros we wrote.
– First, choose New from the Toolbars tab of the Customize window.
– Enter the name “Filter”
34
Custom Toolbar (cont)

Now go to the Commands tab and find the Macros category.
– Since we are creating a toolbar, we select Custom Button.
– Drag this button to the new toolbar.
35
Custom Toolbar (cont)

We now use the Modify Selection button to
– Name the button
– Change the button image
– Assign a macro
36
Customizing Menu Options

Customizing a menu option is done in a similar way.

We can ignore the Toolbars tab and move to the Commands tab and
choose New Menu from the Categories list.
37
Custom Menu

Let us create a new menu option for our sorting macros.

We select New Menu and then use Modify Selection to give a name to
the new menu item.
38
Custom Menu (cont)

We now again choose Macros from the Commands tab and this time
select Custom Menu Item.

Again use Modify Selection to give a name and new icon to the new
menu item, then assign a macro.
39
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. Choose Tools > Macros > Record New Macro or choose the Record icon
from the VBA toolbar;
– 2. Perform a sequence of actions in Excel;
– 3. Choose Tools > Macros > Stop Recording or choose the Stop icon from
the VBA toolbar.


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.
40
Additional Links

(place links here)
41