Microsoft Excel VBA Training

This course is intended for advanced Microsoft Excel users that need to automate Excel spreadsheet tasks using Visual Basic for Applications (VBA).

Upon successful completion of this course, students will be able to:

• Create, record, edit and debug a macro.
• Convert a Macro to VBA code.
• Format worksheets using VBA.
• Create an interactive worksheet with VBA.
• Perform custom calculations using VBA.

1. Generating VBA from Macros
   • Create a Macro Using the Macro Recorder
     • Visual Basic for Applications
     • Object-Oriented Programming
     • Macros
     • Macro Options
     • The Macro Recorder
     • Personal Macro Workbooks
     • How to Create a Macro Using the Macro Recorder
   • Edit a Macro
     • The Visual Basic Editor
     • Projects
     • Modules
     • The Modules Folder
     • Objects
     • Properties
     • Methods
     • VBA Comments
     • How to Edit a Macro
   • Debug a Macro
     • The Debugging Process
     • Debugging Tools
     • How to Debug a Macro
   • Customize the Quick Access Toolbar and Hotkeys
     • How to Customize the Quick Access Toolbar and Hotkeys
   • Set Macro Security
     • Digital Certificates
     • Digital Signatures
     • Macro Security Settings
     • How to Set Macro Security
2. Formatting Worksheets Using VBA
   • Insert Text
     • The Selection Property
     • The ActiveSheet Property
     • The Name Property
     • The Value Property
     • Concatenation
     • How to Insert Text
   • Format Text
     • How to Format Text
   • Sort Data
     • The Range Object
     • The Select Method
     • The CurrentRegion Property
     • How to Sort Data
   • Duplicate Data
     • Data Types
     • Variables
     • Variable Naming Rules
     • Operators
     • The Assignment Operator
     • Arithmetic Operators
     • Comparison Operators
     • Logical Operators
     • The For Next Loop
     • Do Loops
     • The Worksheets Object
     • The Count Property
     • The Offset Property
     • The Copy Method
     • The Paste Method
     • How to Duplicate Data
   • Generate a Report
     • The Columns Property
     • The AutoFit Method
     • The Address Property
     • The Call Statement
     • The Font Property
     • The End Property
     • How to Generate a Report
3. Creating an Interactive Worksheet with VBA
   • Determine the Dialog Box Type
     • Message Boxes
     • Input Boxes
   • Capture User Input
     • The InputBox Function
     • Constants
     • The MsgBox Function
     • The Code Continuation Character
     • The vbCrLf Constant
     • Decision Structures
     • The Select Case Statement
     • The If Then Structure
     • The Else Clause
     • How to Capture User Input
4. Working with Multiple Worksheets in VBA
   • Insert, Copy, and Delete Worksheets
     • The Add Method
     • The Copy Method
     • The Delete Method
     • How to Insert, Copy, and Delete Worksheets
   • Rename Worksheets
     • Expressions
     • The DateSerial Function
     • The Format Function
     • How to Rename Worksheets
   • Modify the Order of Worksheets
     • The Move Method
     • How to Modify the Order of Worksheets
   • Print Worksheets
     • The PrintPreview Method
     • The PrintOut Method
     • How to Print Worksheets
5. Performing Calculations with VBA
   • Create User-Defined Functions
     • User-Defined Functions
     • Types of Functions
     • Arguments
     • How to Create User-Defined Functions
   • Automate SUM Functions
     • Declared Range Objects
     • The Set Statement
     • Range Object Cell Addressing
     • The Rows Property
     • The Formula Property
     • The Columns Property
     • Address Property Cell Reference Settings
     • How to Automate SUM Functions

Students should have completed, or have experience with, topics covered in the Microsoft Excel Introduction, Intermediate and Advanced Training courses.