ACF Academy
Operations and IT Training Training
Category:
 Operations and IT Training
Targeted Audience:
 Anyone who wants to learn how to expand Excel's capabilities by using VBA
Prerequisites:
 A good working knowledge of Excel
CPE Credits:
 7 hours
Course Level:
 Intermediate
 
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
   
ADO vs. DAO
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

 

 
Accreditation


 

"Excellent overall program… Practical applications were very helpful (modules on trading options)."

– Rafael P.