Курсы английского
<<  WSE (world standard english) и современные проблемы искусственного биллингвизма Oracle Database 10g on Windows: Architecture for Performance  >>
Tutorial 12: Enhancing Excel with Visual Basic for Applications
Tutorial 12: Enhancing Excel with Visual Basic for Applications
Objectives
Objectives
Objectives
Objectives
Visual Overview
Visual Overview
The Visual Basic Editor
The Visual Basic Editor
Developing an Excel Application
Developing an Excel Application
Developing an Excel Application
Developing an Excel Application
Working with the Visual Basic Editor
Working with the Visual Basic Editor
Working with the Visual Basic Editor
Working with the Visual Basic Editor
Examining Project Explorer
Examining Project Explorer
Using the Properties Window
Using the Properties Window
Naming Modules
Naming Modules
Viewing the Code Window
Viewing the Code Window
Procedures Supported by Visual Basic
Procedures Supported by Visual Basic
Working with Sub Procedures
Working with Sub Procedures
Working with Sub Procedures
Working with Sub Procedures
Creating a Sub Procedure Using Copy and Paste
Creating a Sub Procedure Using Copy and Paste
Creating a Sub Procedure Using Copy and Paste
Creating a Sub Procedure Using Copy and Paste
Visual Overview
Visual Overview
Visual Basic Objects
Visual Basic Objects
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Modifying Properties
Modifying Properties
Modifying Properties
Modifying Properties
Modifying Properties
Modifying Properties
Applying Methods
Applying Methods
Applying Methods
Applying Methods
Applying Methods
Applying Methods
Working with Variables and Values
Working with Variables and Values
Declaring a Variable
Declaring a Variable
Assigning a Value to a Variable
Assigning a Value to a Variable
Assigning a Value to a Variable
Assigning a Value to a Variable
Writing a Sub Procedure
Writing a Sub Procedure
Creating a Sub Procedure to Switch Defined Names
Creating a Sub Procedure to Switch Defined Names
Retrieving Information from the User
Retrieving Information from the User
Visual Overview
Visual Overview
If Statements and Customization
If Statements and Customization
Working with Conditional Statements
Working with Conditional Statements
Working with Conditional Statements
Working with Conditional Statements
Using the If Statement
Using the If Statement
Using the If-Then-Else Control Structure
Using the If-Then-Else Control Structure
Using the If-Then-ElseIf Control Structure
Using the If-Then-ElseIf Control Structure
Using Comparison and Logical Operators
Using Comparison and Logical Operators
Using Logical Operators
Using Logical Operators
Creating a Message Box
Creating a Message Box
Creating a Message Box
Creating a Message Box
Customizing the Quick Access Toolbar
Customizing the Quick Access Toolbar
Customizing the Quick Access Toolbar
Customizing the Quick Access Toolbar
Customizing a Quick Access Toolbar Button
Customizing a Quick Access Toolbar Button
Customizing Excel Screen Elements
Customizing Excel Screen Elements
Customizing Excel Screen Elements
Customizing Excel Screen Elements
Customizing Excel Screen Elements
Customizing Excel Screen Elements
Saving a Worksheet as a PDF
Saving a Worksheet as a PDF
Introducing Custom Functions
Introducing Custom Functions

Презентация на тему: «Tutorial 12: Enhancing Excel with Visual Basic for Applications». Автор: . Файл: «Tutorial 12: Enhancing Excel with Visual Basic for Applications.pptx». Размер zip-архива: 4214 КБ.

Tutorial 12: Enhancing Excel with Visual Basic for Applications

содержание презентации «Tutorial 12: Enhancing Excel with Visual Basic for Applications.pptx»
СлайдТекст
1 Tutorial 12: Enhancing Excel with Visual Basic for Applications

Tutorial 12: Enhancing Excel with Visual Basic for Applications

2 Objectives

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

New Perspectives on Microsoft Excel 2010

2

2

2

2

3 Objectives

Objectives

Create an input box to retrieve information from the user Create and run If-Then control structures Work with comparison and logical operators Create message boxes Customize the Quick Access Toolbar Customize Excel

New Perspectives on Microsoft Excel 2010

3

4 Visual Overview

Visual Overview

New Perspectives on Microsoft Excel 2010

4

5 The Visual Basic Editor

The Visual Basic Editor

New Perspectives on Microsoft Excel 2010

5

6 Developing an Excel Application

Developing an Excel Application

Excel applications use Excel commands, tools, and functions to perform an action Stored as an Excel file Can only be opened from within Excel Excel macro recorder Fastest way to create macros

New Perspectives on Microsoft Excel 2010

6

7 Developing an Excel Application

Developing an Excel Application

Macro buttons provide a quick way to move between worksheets

New Perspectives on Microsoft Excel 2010

7

8 Working with the Visual Basic Editor

Working with the Visual Basic Editor

Visual Basic for Applications (VBA) Common programming language used by all Microsoft Office programs Used to create Excel macros that make it easier to display data on different types of information

New Perspectives on Microsoft Excel 2010

8

9 Working with the Visual Basic Editor

Working with the Visual Basic Editor

Visual Basic Editor displays three windows: Project Explore window Properties window Code window

New Perspectives on Microsoft Excel 2010

9

10 Examining Project Explorer

Examining Project Explorer

Use to manage projects (collection of items that make up a customized application) Contains a hierarchical list of all the objects Is dockable Displays project components in a tree structure

New Perspectives on Microsoft Excel 2010

10

11 Using the Properties Window

Using the Properties Window

To view a list of properties for any object in alphabetical order and by category

New Perspectives on Microsoft Excel 2010

11

12 Naming Modules

Naming Modules

Good practice to rename a module (collection of VBA macros) with a descriptive name that describes the type of macros it contains

New Perspectives on Microsoft Excel 2010

12

13 Viewing the Code Window

Viewing the Code Window

To view contents of the macros in project modules

New Perspectives on Microsoft Excel 2010

13

14 Procedures Supported by Visual Basic

Procedures Supported by Visual Basic

Sub procedures Perform an action on a project or workbook (e.g., formatting a cell or displaying a chart) Function procedures Return a value Often used to create custom functions that can be entered in worksheet cells Property procedures Used to create custom properties for objects in the project

New Perspectives on Microsoft Excel 2010

14

15 Working with Sub Procedures

Working with Sub Procedures

To create other sub procedures: Use the macro recorder Enter new sub procedures into Code window by: Typing VBA commands directly, or Using Insert Procedure command To create a sub procedure without the macro recorder, proper VBA syntax must be used or Excel cannot run the macro

New Perspectives on Microsoft Excel 2010

15

16 Working with Sub Procedures

Working with Sub Procedures

Comment Statement that describes behavior or purpose of a procedure but does not perform any action Must begin with an apostrophe (‘) Appears in a green font Public sub procedures Available to other modules in the project Private sub procedures Hidden from other modules to avoid conflicts in procedure names

New Perspectives on Microsoft Excel 2010

16

17 Creating a Sub Procedure Using Copy and Paste

Creating a Sub Procedure Using Copy and Paste

Add Procedure dialog box Inserted sub procedure

New Perspectives on Microsoft Excel 2010

17

18 Creating a Sub Procedure Using Copy and Paste

Creating a Sub Procedure Using Copy and Paste

Edited sub procedure Test a macro by running it from the workbook or from within Visual Basic Editor

New Perspectives on Microsoft Excel 2010

18

19 Visual Overview

Visual Overview

New Perspectives on Microsoft Excel 2010

19

20 Visual Basic Objects

Visual Basic Objects

New Perspectives on Microsoft Excel 2010

20

21 Visual Basic for Applications

Visual Basic for Applications

Immediate window shows effects of a single command As a command is entered, its effects are instantly applied to the workbook Ideal way to learn VBA syntax and debug programs

New Perspectives on Microsoft Excel 2010

21

22 Visual Basic for Applications

Visual Basic for Applications

VBA: an object-oriented programming language Tasks are performed by manipulating objects VBA objects in Excel

New Perspectives on Microsoft Excel 2010

22

23 Visual Basic for Applications

Visual Basic for Applications

Objects are often grouped into collection objects, which are themselves objects Examples of object collections

New Perspectives on Microsoft Excel 2010

23

24 Visual Basic for Applications

Visual Basic for Applications

Objects and object collections are organized in a hierarchy with Excel at the top and individual cells of a workbook at the bottom Often referred to as the Excel Object Model

New Perspectives on Microsoft Excel 2010

24

25 Visual Basic for Applications

Visual Basic for Applications

VBA provides special object names to refer directly to certain objects Special object names

New Perspectives on Microsoft Excel 2010

25

26 Modifying Properties

Modifying Properties

VBA language alters objects by either: Modifying the object’s properties (attributes that characterize the object), or Applying a method to the object

New Perspectives on Microsoft Excel 2010

26

27 Modifying Properties

Modifying Properties

To change the property of an object Examples of changing a property’s value

New Perspectives on Microsoft Excel 2010

27

28 Modifying Properties

Modifying Properties

List of properties and methods Completed VBA command to set the cell value

New Perspectives on Microsoft Excel 2010

28

29 Applying Methods

Applying Methods

Method: action that can be performed on an object Objects and their methods

New Perspectives on Microsoft Excel 2010

29

30 Applying Methods

Applying Methods

To apply parameter values to a method Code to apply a method with parameters

New Perspectives on Microsoft Excel 2010

30

31 Applying Methods

Applying Methods

Select and Move methods

New Perspectives on Microsoft Excel 2010

31

32 Working with Variables and Values

Working with Variables and Values

Power of VBA begins when you start using variables Variables are case sensitive

New Perspectives on Microsoft Excel 2010

32

33 Declaring a Variable

Declaring a Variable

When you declare a variable, allocate storage space for it by “dimensioning” it Can define exactly what type of data can be stored VBA supports a wide range of data types

New Perspectives on Microsoft Excel 2010

33

34 Assigning a Value to a Variable

Assigning a Value to a Variable

After a variable is declared, data can be stored in it Variables can also store objects Variables can be used to create general procedures that apply to several objects

New Perspectives on Microsoft Excel 2010

34

35 Assigning a Value to a Variable

Assigning a Value to a Variable

VBA statements in which variables are assigned values or are used to store objects

New Perspectives on Microsoft Excel 2010

35

36 Writing a Sub Procedure

Writing a Sub Procedure

Charts and statistics in a workbook are based on defined names rather than cell references To display different data, change definition of the defined name

New Perspectives on Microsoft Excel 2010

36

37 Creating a Sub Procedure to Switch Defined Names

Creating a Sub Procedure to Switch Defined Names

Defined names are stored in the Names object collection To modify the definition of a name, use either the Value property or the RefersTo property Create a dialog box with VBA to prompt the user; Excel automatically switches to that type Common types of program errors Syntax errors Run-time errors Logical errors

New Perspectives on Microsoft Excel 2010

37

38 Retrieving Information from the User

Retrieving Information from the User

To prompt user for the value of the variable InputBox function

New Perspectives on Microsoft Excel 2010

38

39 Visual Overview

Visual Overview

New Perspectives on Microsoft Excel 2010

39

40 If Statements and Customization

If Statements and Customization

New Perspectives on Microsoft Excel 2010

40

41 Working with Conditional Statements

Working with Conditional Statements

An error value means that Excel cannot find the defined name used in the formula A macro can be modified to handle this problem by creating a control structure Control structures “make decisions” based on the type of information the user enters

New Perspectives on Microsoft Excel 2010

41

42 Working with Conditional Statements

Working with Conditional Statements

Control structure for the Change_Type macro

New Perspectives on Microsoft Excel 2010

42

43 Using the If Statement

Using the If Statement

Most basic way to run a VBA command in response to a particular condition In this type of control structure, if a certain condition is met, the program executes a specified command When the condition of the If statement is not true, the macro does nothing

New Perspectives on Microsoft Excel 2010

43

44 Using the If-Then-Else Control Structure

Using the If-Then-Else Control Structure

Use when macro needs to run an alternate command when the condition is false

New Perspectives on Microsoft Excel 2010

44

45 Using the If-Then-ElseIf Control Structure

Using the If-Then-ElseIf Control Structure

Use if control structure has several conditions Runs commands in response to each condition

New Perspectives on Microsoft Excel 2010

45

46 Using Comparison and Logical Operators

Using Comparison and Logical Operators

Comparison operators Determine whether the expression used in the condition is true or false Expression must contain a comparison operator (e.g., <, >, =, <=, >=, <>, and is) Logical operators Combine expressions within a condition Most common logical operators: And and Or Other control structures supported by VBA: For-Next, Do-While, and Do-Until

New Perspectives on Microsoft Excel 2010

46

47 Using Logical Operators

Using Logical Operators

Condition with the And logical operator Condition using the Or logical operator

New Perspectives on Microsoft Excel 2010

47

48 Creating a Message Box

Creating a Message Box

A dialog box that includes buttons and an informative message for the user Similar to an input box, but does not contain a text box for user to enter values Create with the MsgBox function

New Perspectives on Microsoft Excel 2010

48

49 Creating a Message Box

Creating a Message Box

Some button styles merely inform, some ask a question, and others provide an alert to a problem

New Perspectives on Microsoft Excel 2010

49

50 Customizing the Quick Access Toolbar

Customizing the Quick Access Toolbar

Makes macros accessible from any sheet in the workbook Add commands to the Quick Access Toolbar Three commands included by default Save Undo Redo

New Perspectives on Microsoft Excel 2010

50

51 Customizing the Quick Access Toolbar

Customizing the Quick Access Toolbar

Specify whether changes affect all workbooks or a specific workbook; can create a different Quick Access Toolbar for each workbook Each macro button on the toolbar uses the same icon or symbol; different macro names appear in each button’s ScreenTip

New Perspectives on Microsoft Excel 2010

51

52 Customizing a Quick Access Toolbar Button

Customizing a Quick Access Toolbar Button

Modify buttons so each has a distinct image Enter more descriptive ScreenTip text

New Perspectives on Microsoft Excel 2010

52

53 Customizing Excel Screen Elements

Customizing Excel Screen Elements

Three general categories Those that are part of the Excel program Those that are part of the Excel workbook window Those that are part of the Excel worksheet Difference between these categories affects where a screen element can be hidden

New Perspectives on Microsoft Excel 2010

53

54 Customizing Excel Screen Elements

Customizing Excel Screen Elements

Workbook with hidden Excel elements

New Perspectives on Microsoft Excel 2010

54

55 Customizing Excel Screen Elements

Customizing Excel Screen Elements

Excel customization options

New Perspectives on Microsoft Excel 2010

55

56 Saving a Worksheet as a PDF

Saving a Worksheet as a PDF

PDF (Portable Document Format) File format developed by Adobe Systems that supports all elements of a printed document in an electronic format that is easily shared Excel provides two options for publishing PDFs Standard option optimizes the PDF for use with online publishing and printing Minimum size option is used strictly for online publishing, but not for printing

New Perspectives on Microsoft Excel 2010

56

57 Introducing Custom Functions

Introducing Custom Functions

A function procedure returns a value rather than performing an action (like a sub procedure)

New Perspectives on Microsoft Excel 2010

57

«Tutorial 12: Enhancing Excel with Visual Basic for Applications»
http://900igr.net/prezentacija/anglijskij-jazyk/tutorial-12-enhancing-excel-with-visual-basic-for-applications-140047.html
cсылка на страницу
Урок

Английский язык

29 тем
Слайды
900igr.net > Презентации по английскому языку > Курсы английского > Tutorial 12: Enhancing Excel with Visual Basic for Applications