Презентация на тему Programming with Excel

VBA code
Write VBA code
Add a conditional statement
Objectives

for data
Debug a macro
Create a main procedure
Run a main

procedure

Objectives

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

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

(cont.)
Comments
Procedure header
Keyword

(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

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

(cont.)
Selects range object cell A2
Applies bold formatting to range

A3:F3

Sets width of columns B-F to AutoFit

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

(cont.)
Comments begin with apostrophes
Information between quotes will be inserted

in the active cell

(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 [.]

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]

Statement (cont.)
Elements of the If…then…Else statement appear in blue

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

for Data (cont.)
This text will appear in a dialog

box

Comment points out error in next line of the procedure

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

(cont.)
Indicates that the LeftFooter variable is empty

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

Procedure (cont.)
MainProcedure calls each procedure in the order shown

Procedure
Running a main procedure allows you to run several

macros in sequence
Run a main procedure as you would any other macro

Procedure (cont.)
Current
Module
button
Printing Macro Procedures
Current Project
button