Masters 2 (27 CPE)

• Case Studies

  • We'll apply the VBA Topics we learn to practical case studies:
  • 1 - Insert standard worksheet title
  • 2 - Insert the SUBTOTAL function
  • 3 - Prepare a workbook to deliver to staff
  • 4 - Apply standard PivotTable design
  • 5 - Apply standard chart design
  • 6 - Export journal entries
  • 7 - Update a set of budget workbooks
  • 8 - Create PDF reports from workbooks

• VBA Topics

  • Visual Basic Editor
  • Macro Recorder
  • Excel object model, properties, methods
  • Procedures, subs, functions
  • Collections
  • Variables, constants, scope, type, object variables
  • VBA functions, MsgBox, InputBox
  • Program flow, If Then, Exit, Labels, Select Case
  • Loops, collection loops, For Next, Do While
  • Error handling, On Error, Debugging, Watch/Locals
  • Copy cell, range, table
  • Charts, chart sheets
  • Performance, ScreenUpdating
  • Module variables
  • Workbook, worksheet loops
  • Names, StatusBar, variant/array, Split

• Author and Instructor

  Jeff Lenning CPA CITP

• Subject

  Excel University Masters 2

• Benefits

  Begin Now: begin training immediately
  On-Demand: train at your own pace
  Pause and Resume: pause and resume training as needed
  
  

• Software

  Presented with Excel O365 for Windows. Notes: Excel for Windows O365 or 2016+ are the recommended versions for taking this course, however, VBA is available in previous versions of Excel for Windows as well. Excel for Mac is not supported.

• Format

  On-Demand

• Access Begins

  Immediately

• Duration

  Work at your own pace during the one-year enrollment period.

• Enrollment Period

  Your enrollment is valid for one year.

---

Lesson 1 Lecture Videos (minutes) Overview (23m) Visual Basic Editor Intro  (25m) VBA Briefing 1 (1h 10m) Case Study 1 (42m) Homework VBA Briefing & Case Study Additional Resources Answer Files Suggested Reading / VBA Documentation Extra Credit Solutions (3m)	Lesson 2 Lecture Videos VBA Briefing 2 (1h) Case Study 2 (23m) Homework VBA Briefing & Case Study Additional Resources Answer Files Extra Credit Solutions (1m) Suggested Reading / VBA Documentation
Lesson 3 Lecture Videos VBA Briefing 3 (55m) Case Study 3 (31m) Homework VBA Briefing & Case Study Additional Resources Answer Files Extra Credit Solutions (3m) Suggested Reading / VBA Documentation	Lesson 4 Lecture Videos VBA Briefing 4 (37m) Case Study 4 (1h 3m) Homework VBA Briefing & Case Study Additional Resources Answer Files Extra Credit Solutions (12m) Suggested Reading / VBA Documentation
Lesson 5 Lecture Videos VBA Briefing 5 (33m) Case Study 5 (21m) Homework VBA Briefing & Case Study Additional Resources Answer Files Extra Credit Solutions (14m) Suggested Reading / VBA Documentation	Lesson 6 Lecture Videos VBA Briefing 6 (21m) Case Study 6 (46m) Homework VBA Briefing & Case Study Additional Resources Answer Files Extra Credit Solutions (8m) Suggested Reading / VBA Documentation
Lesson 7 Lecture Videos VBA Briefing 7 (29m) Case Study 7 (1h 2m) Homework VBA Briefing & Case Study Additional Resources Answer Files Extra Credit Solutions (11m) Suggested Reading / VBA Documentation	Lesson 8 Lecture Videos VBA Briefing 8 (16m) Case Study 8 (27m) Homework VBA Briefing & Case Study Additional Resources Answer Files Extra Credit Solutions (7m) Suggested Reading / VBA Documentation

Materials

All course materials are provided digitally inside the course. There are no additional purchases required to complete the course.

 

A digital copy of Excel VBA Programming for dummies is included inside the course.

The course walks through the VBA Topics using the book as a general guide.

The course teaches more than what is covered in the book, and some topics in the book aren't presented in the lectures, however, the book provides an excellent resource to read as you work through the lessons.

 

 

For students looking to dig deeper into the topics presented, these books are suggested additional reading:

 

• Microsoft Excel 2019 VBA and Macros by Bill Jelen & Tracy Syrstad
• Excel 2019 Power Programming with VBA by Michael Alexander & Dick Kusleika

 

Learning Objectives

• Recognize that Application is the top object in the Excel object model
• Recall that the Visual Basic Editor enables you to read, write, and edit VBA code
• Identify that VBA stands for Visual Basic for Applications
• Recall that the Macro recorder translates your actions into VBA code and can be helpful when you know how to complete a task in Excel but not with VBA
• State which Excel tab contains the commands related to programming and VBA

Program Level

Intermediate

Prerequisites

Masters 1 course or comfortable with PivotTables, charts and basic Power Query commands.

Advance Preparation Needed

Ensure you have access to Excel for Windows (recommended version is 2016 or later), so that you can work through the homework exercises. Excel for Mac is not officially supported.

Delivery Method

QAS Self-study

Recommended CPE Credits

27 CPE (earn 16 CPE at the end of lessons 1-4, and 11 at the end of lessons 5-8)

Field of Study

Computer Software & Applications

Expiration

Program enrollment is valid for one year.

Registration

To register, add this course to your cart by clicking the Buy Now button and then complete the checkout process.

Refund Policy

Full 30-day refund; full refund for any course cancellations or registration into upcoming session if preferred.

Complaint Resolution

For information regarding adminstrative policies such as complaint resolution, please contact Jeff Lenning CPA CITP at 949-200-7688 or via email info@excel-university.com

Official Registry Statement

Excel University, Inc. is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.