Microsoft Excel VBA Advanced (2 Days) & CPD Certificate - Nationwide

Infero Training Ltd

Microsoft Excel VBA Advanced Course Content

Lesson 1: Using Expressions, Variables, and Intrinsic Functions

• Understanding Expressions and Statements
• Declaring Variables
• Understanding Data Types
• Working with Variable Scope
• Using Intrinsic Functions
• Understanding Constants
• Using Intrinsic Constants
• Using Message Boxes
• Using Input Boxes
• Declaring and Using Object Variables

Lesson 2: Controlling Program Execution

• Understanding Control-of-Flow Structures
• Working with Boolean Expressions
• Using the If...End If Decision Structures
• Using the Select Case...End Select Structure
• Using the Do...Loop Structure
• Using the For...To...Next Structure
• Using the For Each...Next Structure
• Guidelines for use of Control-of-Flow Structures

Lesson 3: Working with Forms and Controls

• Understanding UserForms
• Using the Toolbox
• Working with UserForm Properties, Events, and Methods
• Understanding Controls
• Setting Control Properties in the Properties Window
• Working with the Label Control
• Working with the Text Box Control
• Working with the Command Button Control
• Working with the Combo Box Control
• Working with the Frame Control
• Working with Option Button Controls
• Working with Control Appearance
• Setting the Tab Order
• Populating a Control
• Adding Code to Controls
• Launching a Form in Code

Lesson 4: Working with the PivotTable Object

• Understanding PivotTables
• Creating a PivotTable Using Worksheet Data
• Working with the PivotTable Objects
• Working with the PivotFields Collection
• Assigning a Macro to the Quick Access Toolbar

Lesson 5: Debugging Code

• Understanding Errors
• Using Debugging Tools
• Setting Breakpoints
• Stepping through Code
• Using Break Mode during Run mode
• Determining the Value of Expressions

Lesson 6: Handling Errors

• Understanding Error Handling
• Understanding VBA's Error Trapping Options
• Trapping Errors with the On Error Statement
• Understanding the Err Object
• Writing an Error-Handling Routine
• Working with Inline Error Handling

.

Microsoft Excel VBA Advanced Course Objectives

Upon successful completion of the Excel VBA course, you will have gained the skills necessary to:

• Apply VBA to develop macros
• Format worksheets
• Create user-interactive macros
• Work with multiple worksheets
• Perform calculations

This Microsoft Excel VBA course will appeal to students looking to gain the skills necessary to apply VBA to develop macros, format worksheets, create user-interactive macros, work with multiple worksheets, and perform calculations. It may also appeal to students who already have knowledge of the basics of Excel or VBA, including how to create, edit, format, and print worksheets that include charts and sorted and filtered data.

Before taking this Microsoft Excel VBA course, students are recommended to have equivalent proficient knowledge of Microsoft Excel and basic understanding of macros. Knowledge of Pivot Tables is helpful but not required.

Being proficient in Microsoft Excel VBA can open the door to many career opportunities such as: Visual Basic Developer and other related career paths.