Microsoft Office - VBA Essentials

• Navigate, edit and manage Excel workbooks
• Format worksheets and workbooks
• Understand the difference between absolute and relative references
• Understand the basics of Excel formulas and functions, e.g. Sum, text functions
• Understand the concepts of a Macro and its capabilities

Dual monitor setup is required for when attending this course via the QA Attend from Anywhere delivery method.

• Record and edit a Macro
• Use different techniques in order to run any macros available such as keyboard commands, the use of the Quick Access Toolbars or Buttons
• Use VBA and examine the Visual Basic Editor; change the properties of an object; add a module to a project; write the code for a procedure and then run it; and use the Object Browser to search procedures
• Manipulate data by declaring variables of different data types; combine data by using expressions; use functions to accept input and display output; and declare variables and procedures with the appropriate scope
• Use decision structures to create procedures that make decisions; and use loop structures to perform repetitive tasks
• Create an error handling routine in case things go wrong with VBA code

Module 1: Getting started

• Introducing Visual Basic for Applications
• Recording a Macro
• Running a Macro
• Absolute or Relative Cell Referencing
• Saving and Opening Files with Macros
• Adding Macros to Keyboard Shortcuts, Quick Access Toolbar, and Buttons
• Editing a Macro in the Visual Basic Editor
• Understanding the Development Environment
• Customising the Editor
• Tips for General Typing in VBA
• Using Visual Basic Help

Module 2: Working with Procedures

• Program Design
• Understanding Modules
• Naming Rules
• Creating a Module
• Understanding Procedures
• Programming Macro Concepts
• Creating a Subroutine
• Creating a Function
• Understanding Arguments
• Exiting Procedures
• Calling Procedures

Module 3: Understanding Objects, Properties, Methods, and Events

• Understanding Objects Properties, Methods, and Events
• Navigating the Object Hierarchy
• Understanding Collections
• Accessing a Member of a Collection
• Understanding Hierarchy
• Using the Object Browser
• Using the With Statement
• Working with Properties
• Working with Methods
• Creating an Event Procedure
• Reserved Macro Names

Module 4: Using Expressions, Variables and Intrinsic Functions

• Understanding Expressions and Statements
• Declaring Variables
• Understanding Data Types
• Determining the Value of Variables
• Working with Variable Scope
• Using Built-in VBA Functions
• Understanding Constants
• Using Message Boxes
• Controlling the Answer to a Message Box
• Using Input Boxes
• Declaring and Using Object Variables

Module 5: Controlling Program Execution

• Understanding Control-of-Flow Structures
• Using the If...End If Decision Structures
• Nested If Statements
• Using the Select Case ... End Select Structure
• Using the Do ... Loop Structure
• Using the For ... Next Structure
• Using the For Each ... Next Structure
• Guidelines for Use of Control-of-Flow Structures

Module 6: Working with Forms and Controls

• Understanding UserForms
• Creating a Form
• Displaying and Removing a Form
• Aligning and Sizing Controls
• Using the Toolbox
• Working with a Form's Properties, Methods, and Events
• Working with Form and Control Properties, Methods and Event
• Setting the Tab Order
• Populating a Control

Module 7: Debugging and Handling Errors

• Understanding Errors
• Using Debugging Tools
• Setting Breakpoints and Using Break Mode
• Stepping Through Code
• Trapping Errors with the On Error Statement
• Understanding the Err Object
• Working with Inline Error Handling
• Writing an Error-Handling Routine