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