Transcript Lecture Business Reporting

Introducing VBA Macros
Applications of Spreadsheets
C51BR
1
Automating A Workbook with Macros
►A
macro enables you to combine multiple
commands and keystrokes and execute
them as a single command.
 If you perform a particular task frequently,
creating a macro to use can be time-saving and
improves the consistency and accuracy of
repetitive procedures.
C51BR
2
Automating A Workbook with Macros
► The
macro recorder looks at the results of
your keystrokes and creates a subroutine in
Visual Basic for Applications (VBA) which
produces the same results as your
keystrokes.
► Using a macro, you can execute a sequence
of instructions by simply clicking a button,
pressing a key combination, or selecting a
name from a list box.
C51BR
3
Automating Tasks with Macros
► Macros
are viewed and edited using the
Visual Basic Editor.
 fine-tune the macro code
 delete lines from incorrect keystrokes
 create programs from scratch
C51BR
4
Preparing to Record a Macro
► Before
you can record
a macro, you need to
decide what to name
the macro and where
to store it.
► A macro name must
start with a letter and
can contain numbers,
letters and the
underscore character –
but no spaces!
C51BR
5
Preparing to Record a Macro
► You
may store your macro
 In the current workbook
 In a new workbook
 In the personal macro workbook so that it is available to
use in any existing or future workbook.
► You
can assign a shortcut key to your macro.
 Type a letter in the Shortcut key text box within the
Record Macro dialog box.
C51BR
6
Recording a Macro
Provide a descriptive
one-word name for the
macro
Provide a shortcut
key for executing
the macro quickly
Enter a description
specifying the purpose of
the macro. Excel 2003
enters a description like this
one automatically
Select a storage
location for the
macro. Where you
store a macro
determines its
availability to other
workbooks
C51BR
7
Completing the Record Macro
Dialog Box
Select a storage
location for the
macro. Where you
store a macro
determines its
availability to other
workbooks.
C51BR
8
Recording & Playing a Macro
► Once
you’ve established a name and
storage location for your macro, you can
begin the recording process.
► Once you are recording, just perform the
task as you normally would.
► When finished, stop recording.
► After a macro has been recorded, you can
run (or play) it at any time.
C51BR
9
Recording Cell Formatting
Commands
Stop Recording
toolbar
“Recording” status
C51BR
10
More About Macros
► To
run a macro using the shortcut key
 Press and hold the Control key and press the
letter you assigned to the macro.
 If your letter is uppercase, you need to press
both the control key and the shift key and then
press the letter.
► To
delete a macro, select the macro from
the list in the Macro dialog box and click the
Delete button.
C51BR
11
Playing Back a Macro
All of the macros stored
in the Macros in
selection appear in this
list box
Select which macros
to view in the list box
A description of the
currently selected
macro is displayed
C51BR
12
Executing a Macro to Enter a
Business Name and Address
All of the macros stored
in the Macros in
selection appear in this
list box.
C51BR
13
Protecting Yourself From Macro
Viruses
Most users will specify
the “Medium” security
setting if they also have
antivirus software
installed on their system.
If you do not have a virus
scanner, consider
selecting the High or Very
High security
For more information
about security settings,
trusted publishers, and
digital signing, click the
Help button in the Title
bar
This computer has a
virus scanner installed
C51BR
14
Opening a Workbook Containing
Macros
C51BR
15
Introducing the VBA Environment
► Microsoft
Visual Basic for Applications (VBA) is the
shared and fully integrated macro development
environment available in Microsoft Office System
2003.
► VBA is itself an independent software program.
► VBA provides a subset of the features found in the
Microsoft Visual Basic programming language.
 Visual Basic creates stand-alone applications
 VBA must be run within a host application
C51BR
16
Visual Basic Editor - VBE
C51BR
17
Touring the Visual Basic Editor
Project Explorer
window
Properties window
Code window
Work area
C51BR
18
The Project Explorer Window
Project file for
the “VBA Tour”
workbook
Microsoft Excel 2003
workbook and
worksheet objects
VBA code module object
C51BR
19
The Properties Window
Click a tab to display
the properties in
alphabetical order or
grouped by category
Displays the name and
type of the selected
object
Double-click a property
box to set its value
Select a property by
clicking its name
C51BR
20
The Code Window
Macro name
Object box
Procedure box
View buttons
C51BR
21
Tiling Code Windows in the VBE
C51BR
22
Editing a Recorded Macro
Macro name
Code window
Comment
The Code window
contains the VBA
programming code
C51BR
23
Editing VBA Programming Code
C51BR
24
Stepping Through a Macro
The yellow arrow and
code highlighting indicate
the line to be executed
next
Margin
Indicator bar
C51BR
25
Setting a Breakpoint
The red circle and
highlighting indicate that a
breakpoint has been set
Margin
Indicator bar
C51BR
26
Printing Your Macros
Specify whether to print
the selected code, the
entire module, or all the
modules in the current
project
When printing a
custom form, you can
specify whether to
print its image, in
addition to its code
C51BR
27
Creating a Macro Button in a
Workbook
► Excel
provides four methods to run a macro
 choosing a menu sequence
 entering the assigned shortcut keystrokes
 clicking a macro button embedded in a
worksheet
 clicking a macro button added to a new or
existing toolbar.
C51BR
28
Working with ActiveX Controls
► Formerly
known as OLE controls, ActiveX controls
are prebuilt, reusable software components that
you can use to add interface elements and
functionality to your workbooks.
 command buttons
 drop-down list boxes, etc.
► You
place these controls directly on a worksheet
or display them in a custom user form.
► There are thousands of ActiveX controls, produced
by Microsoft and third-party developers, that you
can take advantage of in Excel 2003.
C51BR
29
The Control Toolbox Toolbar
Design Mode
Properties
View Mode
Check Box
Text Box
Command Button
Option Button
List Box
Combo Box
Toggle Button
Spin Button
Scroll Bar
Label
Image
More Controls
C51BR
30
Creating a Macro Button in a
Workbook
► Clicking
a macro button in a worksheet
executes the macro assigned to the button.
► You can resize a macro button by using its
handles.
 If they are not in view, press the control key while
left clicking the button.
► Change
the characteristics of a macro button
by right-clicking the button and selecting
Format Control from the shortcut menu.
C51BR
31
Creating a Macro Button in a
Workbook
► Move
the macro button by right-clicking the
button and dragging it by its shaded border
to the new location.
► Remove a macro button in the worksheet by
right-clicking it and selecting Cut from the
shortcut menu.
► If you embed a macro button in a worksheet,
the macro assigned to that button can only
be applied to that worksheet.
 Create a macro button on a new or existing
toolbar so that it can be applied to any workbook.
C51BR
32
Placing a Command Button On the
Worksheet
C51BR
33
Placing a Command Button On the
Worksheet
Displaying the
Properties
window for the
selected control
The Command
Button ActiveX
control is
selected in
Design mode
C51BR
34
Running a Macro Procedure in the
Command Button’s Click Event
C51BR
35
Running the Macro Procedure
from the Command Button
C51BR
36
Linking Controls and Cells
C51BR
37
Testing a Combo Box Control
Values displayed in this
combo box control are
stored in the MonthNames
range on the Lists
worksheet
C51BR
38
Understanding the VBA Language
The declaration line
specifies the name and
type of the procedure
Comments appear green
in the Code window and
are preceded by an
apostrophe
Keywords appear blue
among the program
statements
C51BR
39
Excel 2003’s Object Model
C51BR
40
Declaring & Assigning Values to
Variables
Declaring variables
and data types
Assigning values
to variables
C51BR
41
Writing VBA Code
Adding a
procedure
to a
module
You must specify the
type of procedure that
you want to create
The code module’s
Name property has
been changed to
“MyCode”
C51BR
42
Using AutoList to Enter Code
When you type a period
in the With structure, the
Editor displays a list of
the properties and
methods that apply to
the object
C51BR
43
The Toggle Display Sub Procedure
C51BR
44
Running the Toggle Display
Sub Procedure
The display of gridlines
and row and column
headings is toggled off
C51BR
45
Working with Excel Objects
C51BR
46
Maximizing the Code Window
The Procedure box
displays the active
procedure’s name
The insertion point is
positioned in the active
procedure
Click the Procedure
View button to view the
active procedure only
C51BR
47
Displaying the Application Object’s AutoList
Window
C51BR
48
Completing the AppInfo
SubProcedure
C51BR
49
Controlling Your Procedures
► One
of the greatest benefits of writing your own
procedures is the ability to control how, when, and
what code executes.
► VBA provides several control structures that allow
you to test conditions for processing and to
perform looping operations, which run the same
lines of code repeatedly.
► These structures let you work within a dynamic
environment, engage the user, and make
processing decisions within your code.
C51BR
50
Making Decisions with IF…THEN
C51BR
51
Creating the “CalcPayment” Sub
Procedure
A Sub procedure always
begins with “Sub” and ends
with “End Sub”
Inserting a new code
module into the project
file
The Code window is
maximized in the work
area
C51BR
52
The Completed Calcpayment Sub
Procedure
C51BR
53
Making Decisions with Select…Case
C51BR
54
Looping with For…Next
The “c” represents each
cell in the rTasks range
object
C51BR
55
Looping with Do…While
C51BR
56
Interacting with the User
► VBA
provides two functions for use in
programming user interaction.
 MsgBox
 InputBox
► These
functions enable you to display and
collect information using built-in dialog box
forms.
C51BR
57
Using the MSGBOX Function
C51BR
58
Using the MSGBOX Function
C51BR
59
Running the DisplayMessage Sub
Procedure
msgText
msgTitle
msgButtons
Concatenated
expression
C51BR
60
Using the INPUTBOX Function
C51BR
61
Using the INPUTBOX Function
C51BR
62
Running the YearBorn Sub Procedure
Assuming that the current
year is 2004, this dialog box
appears when “24” is
entered
C51BR
63