We work closely with PG&E, Sandia National Labs and other clients. Our courses are open to all of our clients and we look forward to seeing you in class.
Upcoming Classes
- This event has passed.
Excel VBA (Macros) – Two Day Course (September 16th and 17th) SYST-0026
September 16, 2021 @ 8:30 am - September 17, 2021 @ 4:00 pm
$438.00Course length: 2.0 day(s) September 16th and 17th
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