Excel Macros from A to Z using VBA

This course refreshes the skills of Advanced users of Excel on the basics of Macros, then builds on the knowledge of participants to enable them to create, vet and edit Macros effectively and accurately using VBA.

Duration
1 day
Format
Instructor-led, in person or live online

What the course covers

Prerequisites

All participants should have a strong working knowledge of Excel.

Overview

  • What is VBA
  • What can be done with VBA
  • Other products containing VBA
  • Storing VBA files

Using VBA

  • How to get use VBA
  • Using the VBA Editor
  • Using the Integrated Development Environment
  • Toolbars
  • Menus
  • VBA Windows
  • VBA Syntax

Using Code Modules

  • Naming Modules
  • How to write code
  • Editor Options
  • Code Options

Excel Macros

  • Creating a macro in Visual Basic
  • Recording a macro,
  • Examining the recorded VBA
  • Editing the recorded VBA

The Excel Object Model

  • Structure of an Excel Application
  • Visual Basic Help
  • Excel Object Browser
  • Types of Excel Objects

Visual Basic Subroutines and Functions

  • Using Windows message boxes
  • Using Windows input boxes
  • Creating VBA subroutines
  • Creating VBA functions

Visual Basic Data Types and Variables

  • What is a variable?
  • Scope of a variable
  • Size of a variable
  • What is a DataType?
  • String DataTypes
  • Numeric DataTypes
  • Date DataTypes

VBA Built-In Functions

  • String manipulation functions
  • Numeric manipulation functions
  • Date manipulation functions
  • Statistical functions

Controlling Excel Objects from VBA

  • using Excel workbooks in VBA
  • using Excel worksheets in VBA
  • using Excel ranges in VBA
  • finding lastrow in range object

VBA Arrays

  • dimensioning arrays
  • filling arrays
  • displaying arrays
  • redefining arrays
  • multi-demensional arrays

Control of Flow in VBA

  • using If statements
  • using Case Select statements
  • using For loops
  • using ForEach loops
  • using While loops
  • interacting through collections

Excel methods, properties and events

  • What are Methods?
  • What are Properties?
  • What are Events?
  • Opening Workbooks with VBA
  • Responding to Excel events, Open,Close

Debugging with VBA

  • How to debug in VBA?
  • using Immediate window
  • using the Watch window
  • using Breakpoints
  • using Single step
  • monitoring variables

UserForms in Excel

  • What is a UserForm?
  • Creating Userforms in Excel
  • Using the Toolbox
  • using Textboxes and Labels
  • Using Comboboxes and Listboxes
  • using CommandButtons
  • Using Option and Checkbox controls

Practical Excel Forms lab

  • Build a project to collect user data
  • Design and Build Booking UserForm
  • Write VBA code to control the UserForm
  • Save the user data in Excel
  • Userform opens when Excel loads

Connecting to other Applications

  • VBA in other applications
  • using Word via Excel VBA
  • using Access via Excel VBA

Practical tips when using VBA

Course dates

There are no public dates for this course yet. We also run it on request for teams. Contact us to arrange a date.