ACF Academy
Operations and IT Training Training
 Operations and IT Training
Targeted Audience:
 Anyone who wants to learn how to expand Excel's capabilities by using VBA
 A good working knowledge of Excel
CPE Credits:
 7 hours
Course Level:
Date  DurationCostVenueRegister
28 Jan 2019  1 day$895.00New York
13 Feb 2019  1 day$895.00New York

Understanding and Using VBA

The principal objectives of this intensive one-day seminar are to:
  Present the key features of VBA for Excel in a logical framework
  Improve participants’ skills in working with Excel VBA
  Enhance delegates’ abilities to analyze and debug existing VBA code
  Explore the techniques used for interfacing VBA with Excel and VBA with database
Course Outline

 Introduction to VBA
Key uses for VBA
Projects, Modules, and Procedures
Functions vs. Subroutines
Types of variable: integer, double, string, variant, and others
Passing parameters to functions and subroutines
Passing by value and passing by reference
Objects, properties, methods, and events
Programming Structure
Declaring variables
Scope and lifetime of variables
Use of STATIC declaration
Declaring arrays
Conditional statements
Looping structures
Defining constants
Developing a user-defined function
 The VBA Environment
Project Explorer
Properties Window
Code Window
Running and Debugging code
Setting breakpoints
Stepping through the code
Stepping into / over / out / run to cursor / set next statement
Immediate Window
Watch window
Adding a Watch
Locals Window
Call Stack
Error handling
Error handling within error handling procedures
Returning #N/A and #VALUE! Errors from user-defined functions
Debugging errors in a UDF
 Interfacing VBA with Excel
Recording and running macros
Excel objects, properties, and methods
Working with the methods of specific Excel objects
Assigning macros to menus, toolbars and buttons
Workbook and Worksheet events
Retrieving data from a worksheet
Writing data to a worksheet
Creating a subroutine / worksheet connection
 Interfacing VBA with Databases
Creating the connection string
Connecting to the database
Retrieving recordsets and transferring to Excel
Transferring Excel data to the database
Data retrieval subroutine example


NB All practical sessions are highlighted like this:
means a Workshop or Simulation
means a Case study




"As a bank regulator, I found the seminar provided me with an in-depth knowledge of various hard decisions bank managers make on a daily basis."

– Gary H.