VBA Programming

Topic-Level Outline

             Days:           5

Prerequisites:           Students taking this course should be familiar with personal computers and Microsoft Office 2000.

             Unit 1 :           Introducing the Visual Basic Editor

           Topic A: 0   Using the Visual Basic Editor

           A-1:           Understanding the Visual Basic Editor interface

           A-2:           Working in Visual Basic Editor

           Topic B: 0   Using the Visual Basic Editor in Excel

           B-1:           Coding a workbook object’s event procedure

           B-2:           Saving and running a workbook object’s event procedure

           B-3:           Coding the GetGenius macro procedure

           Topic C: 0   Using the Visual Basic Editor in Word

           C-1:           Coding the document object’s event procedure

           C-2:           Saving and running document object’s event procedure

           C-3:           Coding the DisplayDate macro procedure

           Topic D: 0   Using the Visual Basic Editor in Access

             D-1:             Coding the form object’s close event procedure

             D-2:             Saving and running the form object’s close event procedure

             D-3:             Coding the CancelAssistant macro procedure

             Unit 2 :           Working with the Object Model

           Topic A: 0   Using the Object Model

           A-1:           Understanding the Object Model

           A-2:           Getting Help in the Object Browser

           A-3:           Using the Immediate window

           Topic B: 0   Using the Object Model in Excel

           B-1:           Using the Object Browser

           B-2:           Using the Immediate window in Excel

           B-3:           Referring to range objects

           Topic C: 0   Using the Object Model in Word

           C-1:           Using the Object Browser in Word

           C-2:           Using the Immediate window

           C-3:           Using the Range object’s select method

           Topic D: 0   Using the Object Model in Access

             D-1:             Using the Object Browser

             D-2:             Using the Immediate window in Access

             Unit 3 :           Working with the object variables

           Topic A: 0   Using object variables

           A-1:           Discussing variables

           A-2:           Discussing object variables

           A-3:           Reserving a procedure-level variable

           A-4:           Using the Set Statement

           Topic B: 0   Creating and using object variables in Excel

           B-1:           Creating the FormatWorksheet macro procedure

           B-2:           Entering a value and formula into a range object

           B-3:           Formatting and previewing worksheet objects

           Topic C: 0   Creating and using object variables in Word

           C-1:           Creating the FormatPromo macro procedure

           C-2:           Using the ApplyTheme instruction

           C-3:           Adding a hyperlink to a document

           Topic D: 0   Creating and using object variables in Access

             D-1:             Creating the DisplayByGrade procedure

             D-2:             Ordering the records in a report

             D-3:             Creating the DisplayByGradeMacro macro

             Unit 4 :           Working with string variables

           Topic A: 0   Using string variables

           A-1:           Reserving a procedure-level string variable

           A-2:           Discussing an assignment statement

           A-3:           Discussing the InputBox function

           A-4:           Concatenating strings

           A-5:           Discussing the Val function

           A-6:           Discussing the Option Explicit statement

           Topic B: 0   Using string variables in Excel

           B-1:           Coding the workbook’s open event procedure

           Topic C: 0   Using string variables in Word

           C-1:           Creating a Facsimile Transmittal sheet

           C-2:           Creating the Fax Transmittal macro procedure

           C-3:           Creating a custom toolbar and button

           Topic D: 0   Using string variables in Access

             D-1:             Creating the SelectFieldOrder procedure

             D-2:             Creating the SelectFieldOrderMacro macro

             Unit 5 :           Working with date variables

           Topic A: 0   Discussing date variables

           A-1:           Reserving a procedure-level date variable

           A-2:           Assigning a value to a date variable

           A-3:           Using VBA’s Date, Time, and Now functions

           A-4:           Using the Format function

           A-5:           Using Dates and Times in calculations

           A-6:           Converting strings to dates

           Topic B: 0   Using date variables in Excel

           B-1:           Creating the CalcHours macro procedure

           B-2:           Using the Offset property

           Topic C: 0   Using date variables in Word

           C-1:           Coding the PrintInvitation procedure

           Topic D: 0   Using date variables in Access

             D-1:             Creating the AssignDates procedure

             D-2:             Creating a custom toolbar and button

             Unit 6 :           Working with numeric variables

           Topic A: 0   Discussing numeric variables

           A-1:           Reserving a procedure-level numeric variable

           A-2:           Using an Assignment statement

           Topic B: 0   Using numeric variables in Excel

           B-1:           Creating a list box

           B-2:           Coding the List Box’s DblClick event procedure

           B-3:           Using the Excel VLookup function in a procedure

           Topic C: 0   Using numeric variables in Word

           C-1:           Coding the UpdateMembership procedure

           C-2:           Searching a table

           Topic D: 0   Using numeric variables in Access

             D-1:             Discussing the ADO Object Model

             D-2:             Coding the PaymentUpdate procedure

             D-3:             Using the Recordset Object’s Find method

             Unit 7 :           Using the selection structure

           Topic A: 0   Discussing the selection structure

           A-1:           Using the If…Then…Else statement

           A-2:           Using the nested If…Then…Else statement

           Topic B: 0   Using the selection structure in Excel

           B-1:           Using the PublishObjects collection

           B-2:           Coding the Publish Calculator procedure

           Topic C: 0   Using the selection structure in Word

           C-1:           Using the TablesOfContents collection

           C-2:           Coding the CreateToc procedure

           Topic D: 0   Using the selection structure in Access

             D-1:             Modifying the PaymentUpdate procedure

             Unit 8 :           Using the Select Case statement and the MsgBox function

           Topic A: 0   Understanding Select Case statement and MsgBox function

           A-1:           Using the Select Case statement

           A-2:           Using the MsgBox function

           Topic B: 0   Using the Select Case statement and the MsgBox function in Excel

           B-1:           Using Microsoft Query

           B-2:           Coding the DisplaySales macro procedure

           Topic C: 0   Using the Select Case statement and the MsgBox function in Word

           C-1:           Coding the GenerateLabels procedure

           Topic D: 0   Using the Select Case statement and the Msgbox function in Access

             D-1:             Coding the DisplayReport procedure

             Unit 9 :           Working with the repetition structure and the With statement

           Topic A: 0   Understanding the repetition structure and the With statement

           A-1:           Discussing the For…Next statement

           A-2:           Discussing the For Each…Next statement

           A-3:           Discussing the With statement

           Topic B: 0   Using the repetition structure and the With statement in Excel

           B-1:           Modifying the DisplaySales procedure

           Topic C: 0   Using the repetition structure and the With statement in Word

           C-1:           Opening, activating, and closing a document

           C-2:           Inserting text in a document

           C-3:           Coding the PrintAndRecordInfo procedure

           Topic D: 0   Using the repetition structure and the With statement in Access

           D-1:           Creating the PostScores procedure

           Unit 10 :           Advanced repetition structure and string functions

           Topic A: 0   Using the string repetition structure and string functions

           A-1:           Using the Do…Loop statement

           A-2:           Using the VBA manipulations functions

           Topic B: 0   Using the string repetition structure and string functions in Excel

           B-1:           Coding the BreakNameApart procedure

           Topic C: 0   Using the string repetition structure and string functions in Word

           C-1:           Coding the SortAndRemoveDuplicates procedure

           Topic D: 0   Using the string repetition structure and string functions in Access

           D-1:           Coding the LocateInstructor procedure

           Unit 11 :           Working with built-in dialog boxes and the Office Assistant

           Topic A: 0   Communicating with the user

           A-1:           Using the dialogs collection

           A-2:           Using the Office Assistant

           Topic B: 0   Using built-in dialog boxes and the Office Assistant in Excel

           B-1:           Coding the SortAndShade procedure

           B-2:           Displaying the Sort dialog box in Excel

           B-3:           Assigning the ColorIndex property

           B-4:           Assigning the Range object’s Cells property

           Topic C: 0   Using built-in dialog boxes and the Office Assistant in Word

           C-1:           Coding the PrintClientList procedure

           C-2:           Using the Range object’s InsertDatabase method

           Topic D: 0   Using the built-in dialog boxes and the Office Assistant in Access

           D-1:           Coding the LocateInstructor procedure

           D-2:           Using the OpenReport method’s WhereCondition argument

           Unit 12 :           Working with custom dialog boxes

           Topic A: 0   Creating custom dialog boxes

           A-1:           Discussing Windows standards

           A-2:           Setting the tab order and providing keyboard access to a control

           A-3:           Adding a form to the project

           A-4:           Using the Toolbox to add a control to the form

           A-5:           Sizing, moving, deleting, saving, and restoring a control

           Topic B: 0   Using custom dialog boxes in Excel

           B-1:           Adding an existing form to a project

           B-2:           Setting control properties

           B-3:           Coding the controls’ event procedures in the Update Inventory dialog box

           Topic C: 0   Using custom dialog boxes in Word

           Topic D: 0   Using custom dialog boxes in Access

           D-1:           Coding the Command button controls

           Unit 13 :           Working with option button, check box, and list box controls

           Topic A: 0   Discussing the dialog box controls

           A-1:           Discussing the dialog box controls

           Topic B: 0   Using the option button, check box, and list box controls in Excel

           B-1:           Adding an option button and a list box

           B-2:           Coding the form’s Initialize event

           B-3:           Using the AddItem method

           B-4:           Coding the Calculate button’s click event procedure

           B-5:           Coding the CalculateSales macro

           Topic C: 0   Using the option button, check box, and list box controls in Word

           C-1:           Coding the form’s Initialize event

           C-2:           Coding the Enter button’s click event procedure

           Topic D: 0   Using the option button, check box, and list box controls in Access

           D-1:           Modifying the Locate button’s click event procedure

           Unit 14 :           Automation

           Topic A: 0   Understanding the Automation process

           A-1:           Understanding the Automation process

           Topic B: 0   Using Automation in Excel

           B-1:           Coding the IssueCertificate procedure

           Topic C: 0   Using Automation in Word

           C-1:           Coding the Enter Information button’s click event procedure

           Topic D: 0   Using Automation in Access

           D-1:           Coding the Create Letter button’s click event procedure

           Unit 15 :           Advanced Automation and error trapping

           Topic A: 0   Discussing Automation and error trapping

           A-1:           Using the GetObject function

           A-2:           Intercepting and handling run-time errors

           A-3:           Using the Resume statement

           A-4:           Preventing multiple instances of an application

           Topic B: 0   Using Automation and error trapping in Excel

           B-1:           Modifying the OpenMemo procedure

           Topic C: 0   Using Automation and error trapping in Word

           C-1:           Modifying the CreateCertificate procedure

           Topic D: 0   Using Automation and error trapping in Access

                        D-1:                        Create Letter button’s click event procedure

                        D-2:                        Modifying the cmdCreate control’s click event procedure