Презентация на тему: Microsoft Excel 2007 - Programming with Excel

Microsoft Excel 2007 -Illustrated Programming with Excel

Objectives View VBA codeAnalyze VBA codeWrite VBA codeAdd a conditional statement

Objectives Prompt the user for dataDebug a macroCreate a main procedureRun a main procedure

Unit Introduction Excel macros are written in a programming language called Visual Basic for Applications, or VBACreate a macro with the Excel macro recorderThe recorder writes the VBA instructions for youEnter VBA instructions manuallySequence of VBA statements is called a procedure

Viewing VBA Code View existing VBA code to learn the languageTo view VBA code, open the Visual Basic EditorContains a Project Explorer window, a Properties window, and a Code windowVBA code appears in the Code windowThe first line of a procedure is called the procedure headerItems displayed in blue are keywordsGreen notes explaining the code are called comments

Viewing VBA Code (cont.)

Viewing VBA Code (cont.) Understanding the Visual Basic EditorA module is the Visual Basic equivalent of a worksheetStore macro proceduresA module is stored in a workbook, or project, along with worksheetsView and edit modules in the Visual Basic Editor

Analyzing VBA Code Analyzing VBA codeEvery element of Excel, including a range, is considered an objectA range object represents a cell or a range of cellsA property is an attribute of an object that defines one of the object’s characteristics, such as sizeThe last line in VBA code is the procedure footer

Analyzing VBA Code (cont.)

Writing VBA Code To write your own code, open the Visual Basic Editor and add a module to the workbookYou must follow the formatting rules, or syntax, of the VBA programming language exactlyA misspelled keyword of variable name will cause a procedure to fail

Writing VBA Code (cont.)

Writing VBA Code (cont.) Entering code using AutoCompleteTo assist you in entering the VBA code, the Editor often displays a list of words that can be used in the macro statementTypically the list appears after you press period [.]

Adding a Conditional Statement Sometimes you may want a procedure to take an action based on a certain condition or set of conditionsOne way to add this type of statement is by using an If...Then…Else statementThe syntax for this statement is: If condition then statements Else [else statements]

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

Prompting the User for Data When automating routine tasks, sometimes you need to pause a macro for user inputUse the VBA InputBox function to display a dialog box that prompts the user for informationA function is a predefined procedure that returns a value

Prompting the User for Data (cont.)

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

Debugging a Macro (cont.)

Creating a Main Procedure Combine several macros that you routinely run together into a procedureThis is a main procedureTo create a main procedure, type a Call statement for each procedure you want to run

Creating a Main Procedure (cont.)

Running a Main Procedure Running a main procedure allows you to run several macros in sequenceRun a main procedure as you would any other macro

Running a Main Procedure (cont.) Printing Macro Procedures

Summary Learn by viewing and analyzing VBA codeWrite VBA code using the Visual Basic EditorUse If..Then..Else statements for conditional actionsPrompt user for data to automate input tasksUse the “Step Into” feature of the Visual Basic Editor to debug macrosUse Main procedures to combine several macros