Excel Macros from A to Z using VBA

Course Description

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.
1 day
Contact us for pricing
 

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

ExcelExcel MacroExcel Macro ProgrammingExcel VBAMicrosoft ExcelMS ExcelVisual Basic for ApplicationsExcel 365Office 365Advanced ExcelDashboard ReportingExcel DashboardsMicrosoft ExcelMicrosoft ExcelMS ExcelExcel 365Excel 2021Microsoft Excel 2021