In this session Excel expert David H. Ringstrom, CPA builds on the concepts in Part 1 of this series to help you go beyond Excel’s Macro Recorder. David leads off with an introduction of Excel’s Goal Seek feature, and then uses that feature as the basis to explain the concepts of prompting a user for information, and then handling the various actions that could cause a macro to crash. The session then covers debugging techniques for identifying and resolving problems in your code, and then closes with expanding on the accounting report macro recorded in Part 2. You’ll see how to ask users yes/no questions, allow the user to select a file for a macro to process, and then offer a completion notification at the end of the macro.
David teaches from Excel 2010 in this session, but covers differences in Excel 2007, 2013, and 2016 where applicable. Excel 2003 is not addressed specifically other than onscreen notes on certain slides.
Topics typically covered:
• Add error handling features to Excel macros to compensate for unexpected or missing data entry inputs.
• By-pass the filter drop-down lists by filtering based on cell contents with a single keystroke or mouse click.
• Gain an understanding of Excel’s Goal Seek feature, which is useful in simplifying what-if analyses.
• Learn how to play back macros that you’ve recorded so as to automate repetitive or tedious tasks.
• Learn the nuances of Macro-Enabled workbooks in Excel 2007 and later.
• Learn what to do next if a Debug prompt appears.
• See how to enable the Developer tab in Excel 2007 and later.
• See how to use Excel’s Macro Recorder to create simple macros without any programming knowledge required.
• Understand the nuances of assigning keyboard shortcuts to macros in Excel and avoid superseding shortcuts for built-in features.
• Use form controls to simplify data entry but also control user’s inputs.
• Use the Text to Columns feature to eliminate unwanted text without retyping, or to separate text into separate columns.
Delivery Method: Online QAS Self Study.
Version: David teaches from Excel 2010 in this session, but covers differences in Excel 2007, 2013, and 2016 where applicable. Excel 2003 is not addressed specifically other than onscreen notes on certain slides.
Prerequisites: Practitioners that have some experience with macros, or that have attended David’s Excel for Beginners Part 1 course.
Advanced Preparation: None