Microsoft Excel®: Excel Visual Basic for Applications (VBA)

 

12 Hours over 2 Days

This class is a programming class. Students should be proficient in using Microsoft Excel before taking this class.

 

Chapter 1 - Getting started

• Introducing Excel VBA

• Macros

• The Developer Ribbon

• Recording a macro using Absolute and Relative references

• Recording an Absolute Macro

• Running a macro using the ribbon

• Running a macro using a shortcut key

• Creating an icon on the Quick Access Toolbar to run a macro

• Adding a command button to run a macro

• Recording a Relative Macro

• Viewing the Visual Basic for Applications (VBA) code

• Editing a macro in the VBA Editor     

• Understanding the development environment

• The Object Browser   

• Using Visual Basic Help

• Closing the Visual Basic Editor

Chapter 2 - Working with procedures and functions

• Understanding modules

• Creating a Standard Module

• Understanding procedures    

• Creating a Sub Procedure

• Calling Procedures from inside other procedures

• Using the Immediate Window

• Creating a Function Procedure

• Naming procedures   

• Working with the Code Editor

• Colors used in coding

• Using capitalization while coding     

• Setting Code Editor Options

• Code settings

  • Window settings

  • Guidelines for editing code

  • Commenting code

  • Finding code 

  • Complete Word feature

Chapter 3 - Understanding objects

• Understanding objects

  • Navigating the Excel Object Hierarchy

  • Understanding collections

  • Using the Object Browser

  • Working with properties

  • Using the With Statement

  • Working with methods

  • Creating an Event Procedure

Chapter 4 - Using expressions, variables, and intrinsic functions

• Understanding expressions and statements

• Declaring variables

• Explicit vs. implicit variable declaration

• Working with Declaration Statements

• Working with variable scope

• Naming variables

• Understanding Data Types    

• Creating an Assignment Statement

• Using intrinsic functions

• Understanding constants

• Using intrinsic constants

• Using Message Boxes

• Using Input Boxes

• Declaring and using Object Variables

Chapter 5 – Controlling program execution

• Understanding control-of-flow structures

• Working with Boolean expressions

• Comparison Operators and Logical Operators (Keyword Operators)           

• Using the If…End If decision structures

• If…Then Statements

• If…Then…Else Statements

• Using the Select Case…End Select structure

• Using the Do…Loop structure

• Using the For…To…Next structure

• For…Next Statements

• Using the For Each…Next structure

• Guidelines for use of control-of-flow structures

Chapter 6 - Working with forms and controls

• Understanding UserForms

• Using the Toolbox

• Working with UserForm properties, events and methods

• Understanding controls

• Setting control properties in the Properties Window

• Working with the Label Control

• Working with the Text Box Control

• Working with the Command Button Control

• Working with the Combo Box Control

• Populating a control

• Working with the Frame Control

• Working with Option Button Controls

• Working with control appearance

• Setting the tab order

• Adding code to controls

• Launching a form in code

 

Chapter 7 - Working with the PivotTable object

• Understanding PivotTables

• Creating a PivotTable

• Working with the PivotTable Wizard method

• Working with PivotFields

• Assigning a procedure to a custom toolbar

Chapter 8 - Debugging Code

• Understanding errors

• Using debugging tools

• Setting Breakpoints

• Stepping through code

• Using Break Mode during Run Mode

• Determining the value of expressions

Chapter 9 - Handling errors

• Understanding error handling

• Understanding VBA’s error trapping options

• Trapping errors with the On Error statement

• Understanding the Err object

• Writing an error-handling routine

• Working with inline error handling