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: August 7, 2023
Course Search
4 Reviews (23 ratings)

Reviews

5
Anonymous Author
This course gives a great introduction to VBA. I learned a lot about this complicated topic. Lenny's "Bot" explanation was very helpful. I enjoy his courses. I don't have to do forms usually, but it is great to see that Excel365 makes those so much simpler.
4
Anonymous Author
Was always a bit unfamiliar with VBA and this course gives a good introduction to some of the relevant concepts. Also provides examples of times when not to use macros, which is also something I found useful.
5
Anonymous Author
VBA has always seemed nebulous to me, but this class helped me understand it more than any other VBA CE course I've taken. I still need more work...
4
Anonymous Author
Allotted time is not sufficient or fair. It should be twice or three times. thx
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 series courses by Lenny Wu.
Excel Magic 1: Building Dynamic Formulas
Excel Magic 2: Building Your Report Generation "Bot"
Excel Magic 3: Automating Data Entry with Data Feeds
Excel Magic 4: Ultimate API Reporting in Excel
Excel Magic 5: Building Interactive Budgeting Model in Excel
Excel Magic 6: Excel's 7 Languages
Excel Magic 7: Creating Excel Charts like a PRO
Excel Magic 8: Transforming a Pro-forma into a Dashboard
Excel Magic 9: Building a Project Tracking “Gantt” Chart

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 .
Instructor for this course
  Course Overview CPE
Course Syllabus
INTRODUCTION AND OVERVIEW
  Introduction to Excel Magic 2: Building Your Own Report Generating “Bot”0:32
  Recap0:53
  Hierarchy of Excel Techniques1:54
  Instructor Introduction 1:10
  Comparison with Other Similar Courses 1:29
  What You will Get from This Course? 0:30
VBA MACRO INTRODUCTION
  VBA Basics3:10
  VBA Environment12:08
How to build a "Bot" to automatically generate 30
  Challenge: Generating 30 reports for 30 locations1:30
  Building your own “Bot” to generate reports - Step 14:57
  Building your own “Bot” to generate reports - Steps 2 to 46:50
  Building your own “Bot” to generate reports - Step 57:14
  Building your own “Bot” to generate reports - Step 62:22
  How effective is this "Bot"?1:15
Actual Cases to AVOID using macros
  When there is a better way: above 4th Gen!8:03
  When the whole process does NOT make sense in the first place!3:05
VBA Best Practices
  How to activate the macro?2:54
  Add notes for your codes1:32
  Add message box and make bite-sized macros1:06
CONCLUSION
  Takeaways 1:07
  Next Course and Q&A0:26
CONTINUOUS PLAY
  Excel Magic 2: Building Your Own Report Generating “Bot”1:04:06
SUPPORTING MATERIAL
  Slides: Excel Magic 2: Building Your Own Report Generating “Bot”PDF
  Excel Magic 2: Building Your Own Report Generating “Bot” Glossary/ IndexPDF
  Workbook: Session 10 - Reporting Bot XLSM
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam