Excel VBA Advanced

Excel VBA Advanced 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

VBA training in Nepal