Instructor for this course
more

If you’d like to expand your ability to write programming code, Part 3 of Excel macros is the ideal presentation for you. Excel expert David Ringstrom shows you how to create a macro that can reset workbooks that have skewed scrollbars, as well as a second macro that can be used to instantly unhide all worksheets in a workbook at once. David goes deeper into making decisions in programming code by comparing If and Select Case statements. He also contrasts three ways of creating loops in Excel: For Each, Do While, and Do Until. In addition, David explains how to troubleshoot problematic loops in Excel, which in certain instances can cause Excel to crash.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.  

Topics Covered:

  • Providing feedback to a user by way of the MsgBox method within a macro.
  • Using a two-word macro to fix an annoying problem in Excel where the used range of Excel expands beyond your actual work area.
  • Consolidating data from multiple workbooks into a single worksheet by way of an Excel macro.
  • Returning the underlying number for a color assigned to a worksheet cell for use elsewhere.
  • Understanding For Each Loops within Excel Workbooks.
  • Making decisions in Excel by way of If statements.
  • Contrasting Do While Loops and Do Until Loops.
  • Inserting blank module sheets into Excel workbooks.
  • Learning how to use a single line of programming code to unhide all worksheets within a workbook.
  • Exploring three ways to cause a macro that’s running in Excel to halt.

Course Series

This course is included in the following series:

4 CoursesExcel Efficiency: Macros

  1. Excel Efficiency: Intro to Macros Part 1
  2. Excel Efficiency: Intro to Macros Part 2
  3. Excel Efficiency: Intro to Macros Part 3
  4. Excel Efficiency: Intro to Macros Part 4

Learning Objectives

  • Identify the keyboard shortcut that takes you to the last-used cell within your workbook.
  • Recognize how to create a macro that will unhide all worksheets in a workbook simultaneously.
  • Discover how to insert blank module sheets into an Excel workbook.
Last updated/reviewed: February 18, 2019

28 Reviews (83 ratings)Reviews

4
Member's Profile
I like the fact that David tells us what he is going to do and then shows it to us. This reinforces the learning. David makes a lot of key stroke errors when he is typing and it is a little distracting from what he is saying. However, it does stress the need for being careful when typing because Excel is going to read exactly what you type.
4
Anonymous Author
The course is good but I do not recommend it until you have mastered Part I and Part II of his macro course. There are a lot to remember and practice might help a lot. This will be quite discouraging for some if you are not comfortable with basic macro first. I think longer time and more examples would be helpful.
5
Member's Profile
I really appreciate the way the presenter provides first the concept of the lesson, then illustrated click-by-click instructions and then finally observable practice. One of my favorite presenters as in each lesson I seem to come away with at least one new trick unrelated to the primary topic.
5
Member's Profile
I really like how David organizes his courses. Detail discussion on a slide with notes and clear indication on the proper order of doing a task. This is also where he goes on any relevant tangents. Then an actual demonstration in Excel. I think this really reinforces the learning.
5
Member's Profile
Great detailed walk through for beginners on how to edit VBA for a specific function. Look forward to taking more courses that offer additional examples of useful VBA code.
5
Member's Profile
Step by Step by approach is awesome .its my first time with excel & it helped immensely now I can learn more by hanging around Pro`s.Great Course to start learning
4
Member's Profile
I enjoyed the information on the Visual Basic Editor. It is intimidating when it pops up and you don't understand what it is. This class helped to demystify it.
4
Member's Profile
Good course - loads of potential to use these techniques to automate daily tasks. Downside - debugging first attempts at creating these macros.
5
Member's Profile
Another good course to continue the Excel macro education. This one gets into the editing of macros and a few more things to watch out for.
5
Member's Profile
Very practical. Also like that David introduces subtopics to illustrate the material, like Goal Seek. I learned 2 new things in one video
4
Anonymous Author
Learned the purpose of developer and how to create a button. I feel I learned a few tricks to save time and idiot proof features.
5
Anonymous Author
Helpful to show how to use input boxes. Good basic information on how to break down the code language.
4
Anonymous Author
Great instructor. Thoroughly explains and demonstrates information for those inexperienced in coding.
4
Anonymous Author
Good course, really liked the information on getting started with visual basic
5
Member's Profile
Very good course, enjoying the process of learning macros. Thank you!
5
Member's Profile
The Macro's course gets better as you go deeper into it. Well Done !!
5
Member's Profile
Lots of good info on leveraging macros to automate tasks.
5
Anonymous Author
Learned about the relationship of macros to Visual Basic.
4
Anonymous Author
Good overview of the basic macro editing functions.
5
Member's Profile
Very informative and thought provoking
4
Member's Profile
Easy to follow. Clear instructions
5
Anonymous Author
Easy to comprehend concept covered
5
Member's Profile
Excellent presentation
5
Anonymous Author
Very useful guidance.
5
Member's Profile
Powerful tools here.
5
Anonymous Author
Great course
5
Member's Profile
Very good.
5
Anonymous Author
Great.

Prerequisites

Course Complexity: Intermediate

No Advanced Preparation or Prerequisites are needed for this course. However, it is recommended to take the other courses in the series prior to completing this one.

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
  8:15Introduction to Excel Efficiency: Intro to Macros Part 3
  10:15Resolving the Big Sheet Problem and Automating the Last Cell Reset
  11:02Create a ResetWorksheet Macro Shortcut and Unhide All Worksheets in a Workbook
  12:18VBE Project Explorer and Properties Windows, Create a Blank Module Sheet and Building the Unhide Worksheets Macro
  10:16Understanding For Each Loops and Create a Module Sheet Combined Macro
  9:46Making Decisions and Determining if the Workbook Matches Criteria
  5:27Pasting Data into Combined Worksheet
  8:04Inform User of Completion
  8:03Determining the Color Code for a Cell and Looping Through Each Row in a Range
  8:30Using the With Statement and Using Select Case to Make Decisions
  9:26Do Until Loop and Do While Loop
  7:01A Loop that May Crash Excel, Halt a Running Macro, Forcing a Macro to End and Breakpoints for Testing...
Continuous Play
  1:48:25Excel Efficiency: Intro to Macros Part 3
SUPPORTING MATERIALS
  PDFSlides: Intro to Macros Part 3
  PDFIntro to Macros Part 3 Glossary/Index
  XLSXWorkbook: Excel Efficiency Intro to Macros Part 3
  XLSB12 Month Income Statement
REVIEW & TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM