Excel Tutorial 12 Expanding Excel with Visual Basic for Applications - PowerPoint PPT Presentation

1 / 27
About This Presentation
Title:

Excel Tutorial 12 Expanding Excel with Visual Basic for Applications

Description:

... Developer tab in the Ribbon check box to insert a check mark, and then click the ... A sub procedure performs an action on your project or workbook, such as ... – PowerPoint PPT presentation

Number of Views:248
Avg rating:3.0/5.0
Slides: 28
Provided by: course166
Category:

less

Transcript and Presenter's Notes

Title: Excel Tutorial 12 Expanding Excel with Visual Basic for Applications


1
Excel Tutorial 12Expanding Excel with Visual
Basic for Applications
2
Objectives
  • 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

3
Objectives
  • Create and run If-Then control structures
  • Work with comparison and logical operators
  • Create message boxes
  • Customize the Quick Access Toolbar
  • Customize Excel

4
Developing 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

5
Working 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

6
Working 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

7
Working 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

8
Working with the Visual Basic Editor
9
Working 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

10
Working with Sub Procedures
11
Referring to Objects
  • VBA is an object-oriented programming language,
    in which tasks are performed by manipulating
    objects

12
Referring to Objects
  • Objects are often grouped into collections, which
    are themselves objects, called collection objects

13
Referring 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

14
Referring to Objects
15
Applying 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

16
Working 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

17
Retrieving Information from the User
18
Working with Conditional Statements
19
Working with Conditional Statements
20
Working with Conditional Statements
21
Working with Conditional Statements
22
Creating a Message Box
  • MsgBox Prompt, Buttons, Title

23
Customizing 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

24
Customizing the Quick Access Toolbar
25
Customizing 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

26
Excel Customization Options
27
Using 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
Write a Comment
User Comments (0)
About PowerShow.com