Instructor for this course
more

The course is presented in five parts.

First, the course relates earlier course of Top 5 Excel Skills to drive the concept home: Building your own “Bot” is your ticket to full automation in Excel Modeling!

Next, the course brings the topic of what is a VBA macro and let students consider when to and when NOT to build a macro.

Third, we illustrate 6 required steps to build your own report generating “Bot”, including:

  • Define the file path to make file path dynamic
  • Create a message box to communicate with users
  • Use For … Next … Loop for iteration

Fourth, the course illustrates 4 real cases that should NOT require building a VBA macro. These are cases from some VBA courses that are still teaching obsolete Excel skills.

Last, the course explores best practices in building VBA macros. They include:

  • How to activate the macro
  • Add notes for your codes
  • Add a message box

 

 

Course Key Concepts: Macro, VBA, Objects, Properties, Methods, Developer tab, Debugging, Message box, For Next Loop.

Learning Objectives

  • Discover and understand VBA macro as 4th Gen of Excel modeling techniques.
  • Identify 3 elements of a VBA macro.
  • Recognize when to build a VBA macro and when not to.
  • Discover 5 elements of a VBA environment.
  • Explore 5 steps required to build a reporting generating “Bot”.
  • Discover and understand 4 best practices of building a VBA macro.
Last updated/reviewed: October 04, 2020

Prerequisites

Course Complexity: Intermediate

Basic Excel knowledge.
Example: Be able to open one Excel file and connect to external data files, etc.
It is recommended to take other Excel Magic courses by Lenny Wu.
Excel Magic: Building Dynamic Formulas

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
INTRODUCTION AND OVERVIEW
  0:32Introduction to Excel Magic 2: Building Your Own Report Generating “Bot”
  0:53Recap
  1:54Hierarchy of Excel Techniques
  1:10Instructor Introduction
  1:29Comparison with Other Similar Courses
  0:30What You will Get from This Course?
VBA MACRO INTRODUCTION
  3:10VBA Basics
  12:08VBA Environment
How to build a "Bot" to automatically generate 30
  1:30Challenge: Generating 30 reports for 30 locations
  4:57Building your own “Bot” to generate reports - Step 1
  6:50Building your own “Bot” to generate reports - Steps 2 to 4
  7:14Building your own “Bot” to generate reports - Step 5
  2:22Building your own “Bot” to generate reports - Step 6
  1:15How effective is this "Bot"?
Actual Cases to AVOID using macros
  8:03When there is a better way: above 4th Gen!
  3:05When the whole process does NOT make sense in the first place!
VBA Best Practices
  2:54How to activate the macro?
  1:32Add notes for your codes
  1:06Add message box and make bite-sized macros
CONCLUSION
  1:07Takeaways
  0:26Next Course and Q&A
CONTINUOUS PLAY
  1:04:06Excel Magic 2: Building Your Own Report Generating “Bot”
SUPPORTING MATERIAL
  PDFSlides: Excel Magic 2: Building Your Own Report Generating “Bot”
  PDFExcel Magic 2: Building Your Own Report Generating “Bot” Glossary/ Index
  XLSMWorkbook: Session 10 - Reporting Bot
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM