Microsoft Excel 2007 - Illustrated

Август 25, 2022

Главная
Информатика
Microsoft Excel 2007 - Illustrated

Содержание

2. Microsoft Office Excel 2007 - Illustrated View VBA code Analyze VBA code Write VBA code Add
3. Microsoft Office Excel 2007 - Illustrated Prompt the user for data Debug a macro Create a
4. Microsoft Office Excel 2007 - Illustrated Unit Introduction Excel macros are written in a programming language
5. Microsoft Office Excel 2007 - Illustrated Viewing VBA Code View existing VBA code to learn the
6. Microsoft Office Excel 2007 - Illustrated Viewing VBA Code (cont.) Comments Procedure header Keyword
7. Microsoft Office Excel 2007 - Illustrated Viewing VBA Code (cont.) Understanding the Visual Basic Editor A
8. Microsoft Office Excel 2007 - Illustrated Analyzing VBA Code Analyzing VBA code Every element of Excel,
9. Microsoft Office Excel 2007 - Illustrated Analyzing VBA Code (cont.) Selects range object cell A2 Applies
10. Microsoft Office Excel 2007 - Illustrated Writing VBA Code To write your own code, open the
11. Microsoft Office Excel 2007 - Illustrated Writing VBA Code (cont.) Comments begin with apostrophes Information between
12. Microsoft Office Excel 2007 - Illustrated Writing VBA Code (cont.) Entering code using AutoComplete To assist
13. Microsoft Office Excel 2007 - Illustrated Adding a Conditional Statement Sometimes you may want a procedure
14. Microsoft Office Excel 2007 - Illustrated Adding a Conditional Statement (cont.) Elements of the If…then…Else statement
15. Microsoft Office Excel 2007 - Illustrated Prompting the User for Data When automating routine tasks, sometimes
16. Microsoft Office Excel 2007 - Illustrated Prompting the User for Data (cont.) This text will appear
17. Microsoft Office Excel 2007 - Illustrated Debugging a Macro When a macro procedure does not run
18. Microsoft Office Excel 2007 - Illustrated Debugging a Macro (cont.) Indicates that the LeftFooter variable is
19. Microsoft Office Excel 2007 - Illustrated Creating a Main Procedure Combine several macros that you routinely
20. Microsoft Office Excel 2007 - Illustrated Creating a Main Procedure (cont.) MainProcedure calls each procedure in
21. Microsoft Office Excel 2007 - Illustrated Running a Main Procedure Running a main procedure allows you
22. Microsoft Office Excel 2007 - Illustrated Running a Main Procedure (cont.) Current Module button Printing Macro
24. Скачать презентацию

Слайд 2

Microsoft Office Excel 2007 - Illustrated
View VBA code
Analyze VBA code
Write VBA

code
Add a conditional statement

Objectives

Слайд 3

Microsoft Office Excel 2007 - Illustrated
Prompt the user for data
Debug a

macro
Create a main procedure
Run a main procedure

Objectives

Слайд 4

Microsoft Office Excel 2007 - Illustrated
Unit Introduction
Excel macros are written in

a programming language called Visual Basic for Applications, or VBA
Create a macro with the Excel macro recorder
The recorder writes the VBA instructions for you
Enter VBA instructions manually
Sequence of VBA statements is called a procedure

Слайд 5

Microsoft Office Excel 2007 - Illustrated
Viewing VBA Code
View existing VBA code

to learn the language
To view VBA code, open the Visual Basic Editor
Contains a Project Explorer window, a Properties window, and a Code window
VBA code appears in the Code window
The first line of a procedure is called the procedure header
Items displayed in blue are keywords
Green notes explaining the code are called comments

Слайд 6

Microsoft Office Excel 2007 - Illustrated
Viewing VBA Code (cont.)
Comments
Procedure header
Keyword

Слайд 7

Microsoft Office Excel 2007 - Illustrated
Viewing VBA Code (cont.)
Understanding the Visual

Basic Editor
A module is the Visual Basic equivalent of a worksheet
Store macro procedures
A module is stored in a workbook, or project, along with worksheets
View and edit modules in the Visual Basic Editor

Слайд 8

Microsoft Office Excel 2007 - Illustrated
Analyzing VBA Code
Analyzing VBA code
Every element

of Excel, including a range, is considered an object
A range object represents a cell or a range of cells
A property is an attribute of an object that defines one of the object’s characteristics, such as size
The last line in VBA code is the procedure footer

Слайд 9

Microsoft Office Excel 2007 - Illustrated
Analyzing VBA Code (cont.)
Selects range object

cell A2

Applies bold formatting to range A3:F3

Sets width of columns B-F to AutoFit

Слайд 10

Microsoft Office Excel 2007 - Illustrated
Writing VBA Code
To write your own

code, open the Visual Basic Editor and add a module to the workbook
You must follow the formatting rules, or syntax, of the VBA programming language exactly
A misspelled keyword of variable name will cause a procedure to fail

Слайд 11

Microsoft Office Excel 2007 - Illustrated
Writing VBA Code (cont.)
Comments begin with

apostrophes

Information between quotes will be inserted in the active cell

Слайд 12

Microsoft Office Excel 2007 - Illustrated
Writing VBA Code (cont.)
Entering code using

AutoComplete
To assist you in entering the VBA code, the Editor often displays a list of words that can be used in the macro statement
Typically the list appears after you press period [.]

Слайд 13

Microsoft Office Excel 2007 - Illustrated
Adding a Conditional Statement
Sometimes you may

want a procedure to take an action based on a certain condition or set of conditions
One way to add this type of statement is by using an If...Then…Else statement
The syntax for this statement is: If condition then statements Else [else statements]

Слайд 14

Microsoft Office Excel 2007 - Illustrated
Adding a Conditional Statement (cont.)
Elements of

the If…then…Else statement appear in blue

Слайд 15

Microsoft Office Excel 2007 - Illustrated
Prompting the User for Data
When automating

routine tasks, sometimes you need to pause a macro for user input
Use the VBA InputBox function to display a dialog box that prompts the user for information
A function is a predefined procedure that returns a value

Слайд 16

Microsoft Office Excel 2007 - Illustrated
Prompting the User for Data (cont.)
This

text will appear in a dialog box

Comment points out error in next line of the procedure

Слайд 17

Microsoft Office Excel 2007 - Illustrated
Debugging a Macro
When a macro procedure

does not run properly, it can be due to an error, called a bug, in the code
To help you find bugs in a procedure, the Visual Basic Editor steps through the procedure’s code one line at a time
When you locate an error, you can debug, or correct it

Слайд 18

Microsoft Office Excel 2007 - Illustrated
Debugging a Macro (cont.)
Indicates that the

LeftFooter variable is empty

Слайд 19

Microsoft Office Excel 2007 - Illustrated
Creating a Main Procedure
Combine several macros

that you routinely run together into a procedure
This is a main procedure
To create a main procedure, type a Call statement for each procedure you want to run

Слайд 20

Microsoft Office Excel 2007 - Illustrated
Creating a Main Procedure (cont.)
MainProcedure calls

each procedure in the order shown

Слайд 21

Microsoft Office Excel 2007 - Illustrated
Running a Main Procedure
Running a main

procedure allows you to run several macros in sequence
Run a main procedure as you would any other macro

Слайд 22

Microsoft Office Excel 2007 - Illustrated
Running a Main Procedure (cont.)
Current
Module
button
Printing

Macro Procedures

Current Project
button

Microsoft Excel 2007 - Illustrated

Содержание

Microsoft Office Excel 2007 - Illustrated View VBA codeAnalyze VBA codeWrite VBA

Microsoft Office Excel 2007 - Illustrated Prompt the user for dataDebug a

Microsoft Office Excel 2007 - Illustrated Unit IntroductionExcel macros are written in

Microsoft Office Excel 2007 - Illustrated Viewing VBA CodeView existing VBA code

Microsoft Office Excel 2007 - Illustrated Viewing VBA Code (cont.)CommentsProcedure headerKeyword

Microsoft Office Excel 2007 - Illustrated Viewing VBA Code (cont.)Understanding the Visual

Microsoft Office Excel 2007 - Illustrated Analyzing VBA CodeAnalyzing VBA codeEvery element

Microsoft Office Excel 2007 - Illustrated Analyzing VBA Code (cont.)Selects range object

Microsoft Office Excel 2007 - Illustrated Writing VBA CodeTo write your own

Microsoft Office Excel 2007 - Illustrated Writing VBA Code (cont.)Comments begin with

Microsoft Office Excel 2007 - Illustrated Writing VBA Code (cont.)Entering code using

Microsoft Office Excel 2007 - Illustrated Adding a Conditional StatementSometimes you may

Microsoft Office Excel 2007 - Illustrated Adding a Conditional Statement (cont.)Elements of

Microsoft Office Excel 2007 - Illustrated Prompting the User for DataWhen automating

Microsoft Office Excel 2007 - Illustrated Prompting the User for Data (cont.)This

Microsoft Office Excel 2007 - Illustrated Debugging a MacroWhen a macro procedure

Microsoft Office Excel 2007 - Illustrated Debugging a Macro (cont.)Indicates that the

Microsoft Office Excel 2007 - Illustrated Creating a Main ProcedureCombine several macros

Microsoft Office Excel 2007 - Illustrated Creating a Main Procedure (cont.)MainProcedure calls

Microsoft Office Excel 2007 - Illustrated Running a Main ProcedureRunning a main

Microsoft Office Excel 2007 - Illustrated Running a Main Procedure (cont.)Current ModulebuttonPrinting

Похожие презентации