Рабочие листы
к вашим урокам
Скачать
1 слайд
Microsoft Excel 2007 -
Illustrated
Programming with Excel
2 слайд
2
Microsoft Office Excel 2007 - Illustrated
View VBA code
Analyze VBA code
Write VBA code
Add a conditional statement
Objectives
3 слайд
3
Microsoft Office Excel 2007 - Illustrated
Prompt the user for data
Debug a macro
Create a main procedure
Run a main procedure
Objectives
4 слайд
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 слайд
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 слайд
6
Microsoft Office Excel 2007 - Illustrated
Viewing VBA Code (cont.)
Comments
Procedure header
Keyword
7 слайд
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 слайд
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 слайд
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 слайд
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 слайд
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 слайд
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 слайд
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 слайд
14
Microsoft Office Excel 2007 - Illustrated
Adding a Conditional
Statement (cont.)
Elements of the If…then…Else statement appear in blue
15 слайд
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 слайд
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 слайд
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 слайд
18
Microsoft Office Excel 2007 - Illustrated
Debugging a Macro (cont.)
Indicates that the LeftFooter variable is empty
19 слайд
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 слайд
20
Microsoft Office Excel 2007 - Illustrated
Creating a Main Procedure (cont.)
MainProcedure calls each procedure in the order shown
21 слайд
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 слайд
22
Microsoft Office Excel 2007 - Illustrated
Running a Main Procedure (cont.)
Current
Module
button
Printing Macro Procedures
Current Project
button
23 слайд
23
Microsoft Office Excel 2007 - Illustrated
Summary
Learn by viewing and analyzing VBA code
Write VBA code using the Visual Basic Editor
Use If..Then..Else statements for conditional actions
Prompt user for data to automate input tasks
Use the “Step Into” feature of the Visual Basic Editor to debug macros
Use Main procedures to combine several macros
Рабочие листы
к вашим урокам
Скачать
6 651 448 материалов в базе
Настоящий материал опубликован пользователем Попкова Жанна Валентиновна. Инфоурок является информационным посредником и предоставляет пользователям возможность размещать на сайте методические материалы. Всю ответственность за опубликованные материалы, содержащиеся в них сведения, а также за соблюдение авторских прав несут пользователи, загрузившие материал на сайт
Если Вы считаете, что материал нарушает авторские права либо по каким-то другим причинам должен быть удален с сайта, Вы можете оставить жалобу на материал.
Удалить материал
Ваша скидка на курсы
40%
Курс профессиональной переподготовки
300/600 ч.
Курс профессиональной переподготовки
300/600 ч.
Курс повышения квалификации
72/180 ч.
Мини-курс
5 ч.
Оставьте свой комментарий
Авторизуйтесь, чтобы задавать вопросы.