Introduction to Excel Macros Part 2

Author: David H. Ringstrom

CPE Credit:  3 hours for CPAs

In this session Excel expert David H. Ringstrom, CPA helps you go beyond the Macro Recorder. You’ll see how to create Visual Basic routines that make decisions, as well as how to create loops that repeat the same task a specified number of times. You’ll also see how to create user forms in Excel, which allow you to create custom input screens. Finally, you’ll get advice on ironing out problems in your programming code.

David’s materials cover Excel 2016, 2013, 2010, and 2007. When applicable, some slides include alternate instructions for Excel 2003. In this presentation, he demonstrates techniques in Excel 2010, as he finds it’s presently the version most widely utilized by Excel users. David’s detailed handouts serve as reference tools you can fall back on after completing one of his courses. He also provides an Excel workbook that includes a majority of the examples he uses during each session.

Publication Date: October 2015

Designed For
Practitioners that have some experience with macros, or that have taken David’s Introduction to Excel Macros Part 1 course.

Topics Covered

  • Add error handling features to Excel macros to compensate for unexpected or missing data entry inputs.
  • Excel's Goal Seek feature, which is useful in simplifying what-if analyses.
  • How to play back macros that you've recorded so as to automate repetitive or tedious tasks.
  • The nuances of Macro-Enabled workbooks in Excel 2007 and later.
  • 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.
  • The nuances of assigning keyboard shortcuts to macros in Excel and avoid superseding shortcuts for built-in features.
  • Create macro buttons so that automation routines can be initiated with a single mouse click.
  • Use the Text to Columns feature to eliminate unwanted text without retyping, or to separate text into separate columns.

Learning Objectives

  • Illustrate how the Goal Seek feature is used
  • Recognize how to get help on a term within the Visual Basic Editor
  • Recognize which tab on the Excel ribbon is the Macro Button command located
  • Identify the Reset button within Excel's Visual Basic Editor
  • Differentiate which keyboard shortcut enables you to step through a macro one line at a time
  • Recognize how comments within a subroutine in the Visual Basic Editor appear as
  • Identify which ribbon tab or menu in Excel the Text to Columns command appears on
  • Describe the methods you must include in the Application prefix
  • Recognize which lines of code will categorically turn off Excel's AutoFilter feature within a worksheet
  • Differentiate where else the Macros command available
  • Identify which method is used in Excel
  • Differentiate the commands not used in Visual Basic for Applications object
  • Recognize how to run a macro
  • Identify the location of where in Visual Basic for Applications, information that you gather while a macro is running can be used later in the macro when stored
  • Illustrate where the tab on the Excel ribbon is the Insert | Macro Button command is located
  • Recognize which line of code instructs Excel to skip over any lines of code that trigger an error
  • Differentiate which highlighted color is used when stepping through a macro, the next line of programming code that Excel will execute at your direction
  • Identify which lines of code that contain syntax errors appear as what color until you resolve the issue
  • Describe the affect when working with filtered data, changes such as row deletions, formatting, etc.
  • Recognize which Visual Basic command provides the easiest way to ask a yes or no question of a user
  • Describe examples of a Boolean value in Excel
  • Differentiate what would be displayed by a yellow exclamation mark icon within a message box prompt displayed by a macro
  • Identify which line of code will temporarily suppress Excel-based prompts that might cause a macro to trigger an error if the user makes an invalid choice
  • Recognize which modifier allows you to safely use any letter of the alphabet as a shortcut key for your macros

Level
Intermediate

Format
On-demand

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (3 hours)

Program Prerequisites
None

Advance Preparation
None

Registration Options
Quantity

Fees
Regular Fee$59
Value Pass Fee$0

Add to Cart Email a Friend View More Self-Study Courses