Course length: 2.0 day(s) August 23rd & August 24th

Course Description

Course Objective: You will automate your job tasks in Microsoft® Office Excel® O365.

Target Student: This course is intended for advanced Microsoft Excel professionals that need to automate Excel spreadsheet tasks using Visual Basic for Applications (VBA).

Prerequisites: Knowledge of the basics of Excel, including how to create, edit, format, and print worksheets that include charts and sorted and filtered data. Students are recommended to first take the following Logical Operations courses or have equivalent knowledge:

• Microsoft® Office Excel® O365: Level 1
• Microsoft® Office Excel® O365: Level 2
• Microsoft® Office Excel® O365: Level 3

LESSON ONE: INTRODUCTION TO VISUAL BASIC FOR APPLICATIONS (VBA) WHAT IS VISUAL BASIC FOR APPLICATIONS (VBA)?

OBJECT-ORIENTED PROGRAMMING PROCEDURES:
OBJECTS
CONTAINERS
COLLECTIONS
PROPERTIES:
METHODS:
EVENTS:
HOW VBA REFERS TO OBJECTS
ACCESSING THE MACRO COMMANDS
SECURITY SETTINGS
SAVING WORKBOOKS THAT CONTAIN MACROS

LESSON TWO: UNDERSTANDING MACROS

WRITING CODE VS RECORDING MACROS
ABSOLUTE VS RELATIVE REFERENCING
USING THE MACRO RECORDER
RECORDING IN RELATIVE MODE
MODIFYING MACROS
METHODS FOR EXECUTING MACROS
ACCESSING MACROS THROUGH THE QUICK ACCESS
TOOLBAR
ACCESSING MACROS THROUGH THE RIBBON
ACCESSING MACROS THROUGH SHAPES OR IMAGES
ACCESSING MACROS THROUGH CONTROLS

LESSON THREE: GETTING TO KNOW THE VISUAL BASIC EDITOR (VBE)

THE MICROSOFT VISUAL BASIC EDITOR
THE PROJECT EXPLORER THE PROPERTIES WINDOW
THE CODE WINDOW
ADDING A NEW MODULE NAMING MODULES
REMOVING A MODULE EXPORTING AND IMPORTING MODULES
PROTECTING CODE
THE OBJECT BROWSER
CUSTOMIZING THE VISUAL BASIC ENVIRONMENT
The Editor Tab
Code Settings
Window Setting
The Editor Format Tab
The General Tab
The Docking Tab
DOCKING WINDOWS

LESSON FOUR: TROUBLESHOOTING AND DEBUGGING ERRORS

TROUBLESHOOTING AND DEBUGGING ERRORS
TYPES OF ERRORS Syntax Errors (aka Compile Error)
Run-Time Errors
Logical Errors
DEBUGGING TECHNIQUES
Debugging Tools
Setting a Breakpoint
The Watch Window
Stepping Through Code
Deleting a Watch Expression
The Immediate Window
The Locals Window

LESSON FIVE: WRITING VBA CODE

ESSENTIALS TO WRITING VBA CODE
Sub Procedures vs Functions
Macro Naming Rules
Comments
SUB PROCEDURES
Using Objects
Using Methods
Using Events
Storing Procedures in the Personal Macro
Workbook
Copy and Paste Procedures
Assigning Macro Buttons to the Quick
Access Toolbar (QAT)
CALLING A SUB PROCEDURE
EXPRESSIONS
VARIABLES
Declaring a Variable
Option Explicit
Declaring Multiple Variables
Assigning a Value to a Variable
DATA TYPES
Using Byte Variables
Using String Variables
Using Currency Variables
Using Date Variables
VARIANTS CONSTANTS SCOPE
Procedure Level Scope
Private Level Scope
Public Level Scope
VBA STANDARD OPERATORS
The Assignment Operator ( = )
The Line Continuation Character ( _)
The Parenthesis ( ) The Comma ( , )
The Colon Operator ( : )
The Ampersand ( & )
Carriage Return Line Feed (vbCrLf)
Defining Parameters (:=)
Arithmetic Operators Comparison
Operators
Logical Operators
THE CELLS OBJECT
The Value Property
FORMATTING DATA
The Range Object
The Select Method
The CurrentRegion Property
The RangeSort Method
MANIPULATING WORKSHEET DATA
The ActiveSheet Property
The Name Property
The Selection Property
The Copy Method
The Paste Method
The Offset Property
The End Property
The Font Property
The With End With Construct
The Call Statement
The Columns and Rows Properties
The AutoFit Method ARRAYS

LESSON SIX: WORKING WITH CONTROL STRUCTURES

CONTROL STRUCTURES
Decision Structures
If…Then
If…Then…Else
Select Case
LOOPS
For Next Loop
Stepping For Each
Do Until Loop
Do While Loop

LESSON SEVEN: WORKING WITH FUNCTION PROCEDURES

FUNCTIONS
Creating a Function
MacroOptions Method
Calling a Function
Calling a Function in a Worksheet
WRITE DATA TO TEXT FILES
USER INTERACTION FUNCTIONS
Working with Message Boxes
Working with Input Boxes
Return Values

LESSON EIGHT: ERROR HANDLING

ERROR HANDLING
The Error GoTo Statement
The On Error Resume Next
The Error Object

LESSON NINE: USER FORMS

USER FORMS
Displaying a UserForm
Printing a Form
Hiding a Form
Closing a Form
FORM PROPERTIES
Name Location Size
CONTROLS
Aligning Controls
Tab Order
Borders
Font
Control Colors
Control Visibility
Control Availability
Focus
Creating a User Form
Writing Code to Show the User Form
Event Procedures for UserForm Controls
Attaching Events to the Form Controls
Dependent Combo Boxes

LESSON TEN: MANAGING SHEETS

MANIPULATING WORKSHEETS
Sheet Types
Worksheet Reference Options
The Add Method
The Delete Method
The Copy Method
The Move Method
CREATING A CHART SHEET
The Declared Range Object
The Set Statement

REFERENCE

GLOSSARY
VISUAL BASIC LANGUAGE KEYWORDS
Reserved Keywords
Unreserved Keywords
OBJECT NAMING CONVENTIONS
CODE SNIPPETS
COMMON EXCEL VBA STATEMENTS
EXCEL VBA FUNCTIONS
VBA KEYBOARD SHORTCUTS