Understanding and Using VBA 

Description

The Understanding and Using VBA course is an intensive one-day course that gives participants practical expertise in using VBA to enhance the capabilities of Excel.

What really sets our program apart is the way in which we explain, step-by-step, exactly how to harness the power of VBA to create powerful real-world solutions. Our approach demystifies the complexities of using VBA, and gives participants practical skills that they can use to secure immediate benefits.

Participants will have access to a range of fully working VBA examples, and will be able to download these for their own use after the seminar is over.

Learning Outcomes

By attending this course, you will:

  • Explore the key features of VBA for Excel within a logical framework
  • Develop your skills in working with Excel VBA
  • Enhance your abilities to analyse and debug existing VBA code
  • Examine techniques used for interfacing VBA with Excel and VBA with databases

Who Should Attend

Anyone who wants to learn how to expand Excel’s capabilities by using VBA.

CPD Credits

7 hours

Prerequisites

A good working knowledge of Excel.

Seminar Content

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 5/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

Dates and Locations

Date
Date(s): 12 Apr 2019

Location
New York

Category
Modeling and Excel

Other Dates and Locations
Check our course schedule for alternative dates and locations where this course is offered.


  

$1,150.00


ACF specialises in creating tailored courses for our clients. Fill out the details below to obtain more information on how to run a customised version of this course in-house for your firm.

Fields marked with * are required