Microsoft Excel VBA Training

Course Duration: 2 Days

This Course Covers Version(s): Microsoft Excel 2013, 2016 and 2019 and Office 365 (Does not cover Excel for Mac OS)

  • Live Class with Instructor
  • Digital Course Manual
  • Hands-on Labs
  • One Year Access to Recorded Course

In this Microsoft Excel VBA Training course, students will learn to use VBA in Excel to create interactive worksheets and automate common worksheet tasks. Students will see how to generate VBA code from existing macros, gain an understanding of the different parts of the Visual Basic Editor, learn how code is organized in Projects, Modules and Procedures and become familiar using Objects, Properties and Methods in their code. In addition, this course teaches students how to debug their VBA code to find and fix logic errors, interact with the user using dialog boxes, control formatting of the worksheet using VBA code and how to automate the addition and deletion of workbooks. Finally, students will see how to perform calculations in code and use built-in functions in Excel in VBA to quickly calculate large amounts of data.

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.

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

This course is intended for advanced Microsoft Excel users that need to automate Excel spreadsheet tasks using Visual Basic for Applications (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

This course does not align to a specific exam or certification.

Live Instructor-Led Virtual Class
Click here to load this Caspio Online Database.
Questions? View Our Live, Instructor-Led Virtual Class FAQs
Virtual or In-Person Group Training

  • For Private Groups as small as 2 people.
  • Live, Instructor-led Online or Onsite Class for your group.
  • Customizable to your needs.
Click here to load this Caspio Online Database.

Questions? Contact Us
Click here to load this Caspio Online Database.