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

Visual Basic.jpg