Excel VBA training

Introduction and the Macro Recorder

• Recording a macro using the Macro Recorder
• Recording a macro using absolute cell references
• The Personal Macro Workbook
• Assigning shortcut keys
• Deleting macros
• Recording macros using relative cell references

Using VBA to Create and Edit Macros

• The concepts of storing macros
• Using the VB Editor
• Using Visual Basic Help
• Closing the Visual Basic Editor
• Understanding Macro Security

Working with Procedures and Functions

• Understanding modules
• Creating a standard module
• Copying and pasting procedure
• Creating a sub procedure
• Calling procedures
• Linking procedures
• Creating a function procedure
• Naming procedures
• Working with the Code Editor

Objects and Dialog Boxes

• Introduction to objects
• Navigating the Excel object hierarchy
• Understanding collections
• Introduction to the object browser
• Working with properties
• Using the With statement
• Working with methods
• Creating an event procedure

Using Expressions, Variables and Intrinsic Functions

• Understanding expressions and statements
• Declaring variables
• Understanding data types
• Working with variable scope
• Using intrinsic functions
• Understanding constants
• Understanding intrinsic constants
• Using message boxes<
• Using input boxes
• Declaring object variables
• Using object variables

Controlling Program Execution

• Understanding control-of-flow structures
• Boolean expressions
• If, Else and Select Case
• Do Loops
• The For... To...Next structure
• The For Each...Next
• Using control-of-flow structures

VBA Forms and Controls

• Creating userforms
• Using the Toolbox
• Working with userform properties, events and methods
• Setting control properties in the Properties window
• The label control
• The text box control
• The command button control
• The combo box control
• The frame control
• Option button controls
• Appearance of controls
• Setting tab order
• Populating a control
• Adding code to controls
• Launching a form in code

Working with the PivotTable Object

• Using worksheet data to create a pivottable
• Working with pivottable objects
• Working with the pivotfield collection
• Assigning a macro to a toolbar or the Quick Access Toolbar

Code debugging

• Looking at errors
• Using the debugging tools
• Setting breakpoints
• Stepping through code
• Using Break mode
• Working out the value of expressions

Error handling

• The VBA error tracking options
• Using the On Error statement to trap errors
• The Err object
• Writing a routine to handle errors
• Inline error items