Understanding and Using VBA 

  Intermediate CPD: 7 hours   2 half-days   Virtual

Description

Understanding and Using VBA

The Understanding and Using VBA course is an intensive course run virtually over two half-days 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.

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
  • computer 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 3/A and #VALUE! Errors from user-defined functions
  • computer 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
  • computer 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
  • computer Data retrieval subroutine example
Virtual Learning

Dates and Locations

Date
Date(s): 2 Dec 2020 - 3 Dec 2020

Times:
a.m. sessions – 08:00 to 12:00 (for Asia / EMEA participants)
p.m. sessions – 13:00 to 17:00  (for EMEA / Americas participants)
All times are BST (London time)

Location
Virtual

Category
Data Science

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


   If you live outside the UK, use coupon code VCNOVAT to remove the VAT charge.
Ticket Type Price Spaces
Course Fee - 1 attendee a.m. sessions (inc. VAT) £840.00
Course Fee - 1 attendee p.m. sessions (inc. VAT) £840.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