Title: Excel Tutorial 12 Expanding Excel with Visual Basic for Applications
1Excel Tutorial 12Expanding Excel with Visual
Basic for Applications
2Objectives
- Create a macro using the macro recorder
- Work with the Project Explorer and Properties
window of the VBA Editor - Edit a sub procedure
- Run a sub procedure
- Work with VBA objects, properties, and methods
- Create an input box to retrieve information from
the user
3Objectives
- Create and run If-Then control structures
- Work with comparison and logical operators
- Create message boxes
- Customize the Quick Access Toolbar
- Customize Excel
4Developing an Excel Application
- If the Excel Developer tab is not on the Ribbon,
click the Office Button, click the Excel Options
button, click Popular in the Excel Options dialog
box (if necessary), click the Show Developer tab
in the Ribbon check box to insert a check mark,
and then click the OK button
5Working with the Visual Basic Editor
- In the Code group on the Developer tab, click the
Macros button - Click the macro name in the Macro name box, if
necessary, and then click the Edit button
6Working with the Visual Basic Editor
- A project is a collection of macros, worksheets,
data-entry forms, and other items that make up
the customized application youre trying to
create - Project Explorer is the window in the Visual
Basic Editor that displays a hierarchical list of
all currently open projects and their contents - The Project Explorer window is dockable
- An object is any element within the Excel working
environment such as a worksheet, cell, workbook,
or even Excel itself
7Working with the Visual Basic Editor
- A property is an attribute of an object that
defines one of its characteristics, such as its
name, size, color, or location on the screen - You can view a list of properties for any object
in the Properties window - A module is a collection of VBA macros
- The Code window displays the VBA macro code
associated with any item in Project Explorer
8Working with the Visual Basic Editor
9Working with Sub Procedures
- A sub procedure performs an action on your
project or workbook, such as formatting a cell or
displaying a chart - A function procedure returns a value
- A property procedure is used to create custom
properties for the objects in your project - Syntax refers to the set of rules that specify
how you must enter certain commands so that VBA
interprets them correctly - A comment is a statement that describes the
behavior or purpose of a procedure, but does not
perform any action
10Working with Sub Procedures
11Referring to Objects
- VBA is an object-oriented programming language,
in which tasks are performed by manipulating
objects
12Referring to Objects
- Objects are often grouped into collections, which
are themselves objects, called collection objects
13Referring to Objects
- VBA organizes objects and object collections in a
hierarchy with the Excel application at the top
and the individual cells of a workbook at the
bottom
14Referring to Objects
15Applying Methods
- A method is an action that can be performed on an
object, such as closing a workbook or printing
the contents of a worksheet
16Working with Variables and Values
- A variable is a named element in a program that
can be used to store and retrieve information - Every variable is identified by a unique variable
name - Dim variable as type
17Retrieving Information from the User
18Working with Conditional Statements
19Working with Conditional Statements
20Working with Conditional Statements
21Working with Conditional Statements
22Creating a Message Box
- MsgBox Prompt, Buttons, Title
23Customizing the Quick Access Toolbar
- Click the Office Button, and then click Excel
Options - Click Customize in the Excel Options list
- Click the Customize Quick Access Toolbar arrow,
and select whether to customize the toolbar for
all documents or a specific workbook - To add a command to the Quick Access Toolbar,
click the command in the left list box, and then
click the Add button - To remove a command from the Quick Access
Toolbar, click the command in the right list box,
and then click the Remove button - Click the OK button
24Customizing the Quick Access Toolbar
25Customizing Excel Screen Elements
- Excel screen elements fall into three general
categories with elements that are (1) part of
the Excel program, (2) part of the Excel workbook
window, and (3) part of the Excel worksheet
26Excel Customization Options
27Using the Save As PDF Add-In
- PDF (Portable Document Format) is a file format
developed by Adobe Systems that supports all of
the elements of a printed document but in an
electronic format that is easily shared - Add-in available from microsoft.com