Advance VBA Corporate Training Course Content

VBA Introduction

Recording Macros

• Recording and Running Macros
• Recording a Macro
• Running a Macro
• Adding a Macro/Procedure to the Quick Access Toolbar
• The Personal Macro Workbook
• Saving the Personal Macros
• Editing a Personal Macro
• Assigning Personal Macros to the Toolbar

Working with the Visual Basic Editor

• Introducing Visual Basic for Applications
• Editing Macros in Visual Basic Editor
• Understanding the Development Environment
• Protect/Lock Excel VBA Code
• Using Help

Developing with Procedures

• Understanding and Creating Modules
• Defining Procedures
• Creating a Sub-Procedure
• Working Using the Code Editor

Managing Program Execution

• Defining Control-Of-Flow structures
• Using the IfEnd If Decision Structures
• Using the Select CaseEnd Select Structure
• Using the DoLoop Structure
• Using The ForNext Structure
• Using the For EachNext Structure
• Guidelines for Use Of Control-Of-Flow Structures

Debugging the Code

• Understanding Errors
• Using Debugging Tools
• Identifying the Value of Expressions
• How to Step Through Code
• Working with Break Mode during Run Mode
• Using the Immediate Window

Understanding Objects

• Defining Objects
• Examining the Excel Object Hierarchy
• Defining Collections
• Referencing Objects in a Collection
• Using the Object Browser
• Working with Properties
• The With Statement
• Working With Methods
• Event Procedures

VBA Intermediate

Using Intrinsic Functions, Variables and Expressions

• Defining Expressions and Statements
• Expressions
• Statements
• How to Declare Variables
• Naming Variables
• Assigning Values To Variables
• Declaring Variables Explicitly
• Determining Data Types
• Programming with Variable Scope
• Harnessing Intrinsic Functions
• Defining Constants and Using Intrinsic Constants
• Using Intrinsic Constants

Creating User Defined Functions

• Creating a Function Procedure
• Calling a UDF
• Using a function within an Excel Workbook

Message Boxes and Input Boxes

• Adding Message Boxes
• Return Values
• Notes Error! Bookmark not defined
• Using Input Boxes
• How to Declare and Use Object Variables

Handling Errors

• Defining VBA’s Error Trapping Options
• Capturing Errors with the On Error Statement
• Determining the Err Object
• Coding an Error-Handling Routine
• Notes Error! Bookmark not defined
• Using Inline Error Handling

Creating Forms and Controls

• Defining UserForms
• Utilising the Toolbox
• Using UserForm Properties, Events And Methods
• Properties
• Events
• Understanding Controls
• Naming Conventions
• Using the Label Control
• Using the Text Box Control
• Using the Command Button Control
• Using the Combo Box Control
• Using the Frame Control
• Using Option Button Controls

VBA Advanced

Working with Ranges

• What is a Range?
• Range Property of the Application
• Cells Property
• The SpecialCells Method
• Naming Ranges
• Working with Collections

Charts

• Creating charts from worksheet data
• Key Properties and methods of the chart object
• Creating Charts from Arrays

PivotTable Object

• Understanding PivotTables
• Creating A PivotTable
• Procedure
• Using the PivotTable Wizard Method
• Using PivotFields

Working with Arrays

• What is an Array
• Array Sizes
• One Dimensional Arrays
• Arrays with Multiple Dimensions
• A word about index numbers
• Ubound and Lbound
• Saving arrays in names

Working with Procedures and Parameters

• Procedure Arguments
• Passing Arguments
• Optional Arguments
• Default Values
• Passing arguments by value and reference

Creating Add-Ins

• VBA Password Protection
• About Macro Security
• Macro security settings and their effects
• Change Macro Security Settings