Excel Agility: Macro Basics Part 3

On Demand Webinar

Webinar Details $219

  • Rated:
  • Webinar Length: 100 Minutes
  • Guest Speaker:   David Ringstrom
  • Topic:   Business Administration, Business Skills, Software, Taxation and Accounting, Finance
  • Credit:   ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
All Access Membership

Excel expert David Ringstrom continues his coverage of macros in part 3 of his series. David 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 compares 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.  

Who Would Be Interested in This Course:
Practitioners with experience in writing or editing macros in Excel seeking to incorporate decision-making capabilities into Excel macros.

Level: Advanced

Your Benefits of Attending:

  • 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.
Learning Objectives:
  • Define the purpose of the word “With” in the context of an Excel macro.
  • State which line of programming code would count the number of rows in a contiguous block of cells.
  • Define how to insert blank module sheets into an Excel workbook.


On-Demand Webcast

Instructional Method:

NASBA Field of Study:
Computer Software & Applications (2 hours)

Program Prerequisites:
Parts 1 and 2 of this macros series, or equivalent prior experience with writing or editing programming code in Microsoft Excel.

Advance Preparation:

  1. Introduction

  2. The Big Sheet Problem - 00:04:07

    1. Replicating - 00:04:07

    2. Resolving - 00:08:25

  3. Automating the Last Cell Reset - 00:11:50

  4. Create a ResetWorksheet Macro Shortcut - 00:18:30

  5. Unhide All Worksheets in a Workbook - 00:22:47

  6. VBE Project Explorer and Properties Windows - 00:29:33

  7. Create a Blank Module Sheet - 00:33:05

  8. Building the Unhide Worksheets Macro - 00:37:35

  9. Understanding For Each Loops - 00:41:56

  10. Create a Module Sheet - Combined Macro - 00:45:34

  11. Making Decisions - 00:52:10

  12. Determining if the Workbook Matches Criteria - 00:56:33

  13. Pasting Data into Combined Worksheet - 01:01:53

  14. Inform User of Completion - 00:07:24

  15. Determining the Color Code for a Cell - 01:15:31

  16. Looping Through Each Row in a Range - 01:19:26

  17. Using the With Statement - 01:23:28

  18. Using Select Case to Make Decisions - 01:26:01

  19. Do Until Loop - 01:31:57

  20. Do While Loop - 01:37:24

  21. A Loop that May Crash Excel - 01:41:37

  22. Halting a Running Macro - 01:42:53

  23. Forcing a Macro to End - 01:43:41

  24. Breakpoints for Testing Programming Code - 01:44:23

Color Code - 01:15:31
Combined Worksheet - 01:01:53
Modules - 00:33:05, 00:45:34
Quick Access - 00:20:01
Project Explorer - 00:29:33
Select Case - 01:26:01
With Statement - 01:23:28

Breakpoint: Breakpoints are used to temporarily halt execution of an Excel macro. Press the F9 key on a line of code within the Visual Basic Editor to set or remove a breakpoint. Upon execution macros halt at a breakpoint, giving you a chance to review the state of the spreadsheet or step through a section of code that you may be debugging.

Macro: One or more lines of programming code that automate tasks. The Macro Recorder allows users to automate tasks without seeing the underlying programming code.

Module: Modules, sometimes referred to as module sheets, are objects that can be added to Excel workbooks to store programming code known as macros.

MsgBox: The MsgBox function in Excel's Visual Basic for Applications programming language enables you to create dialog boxes that can display a message to the user. MsgBox pauses a macro until the user clicks a button and can return a result so that you can determine which button the user clicked, if applicable.

Project Explorer: The Project Explorer is part of Excel's Visual Basic Editor. It provides a hierarchical list of all open documents and any objects within each document, such as worksheets, module sheets, user forms, and class modules. Choose View, and then Project Explorer within the Visual Basic Editor to display this window.

Used Range: The Used Range of an Excel spreadsheet starts at cell A1 and extends down and/or across to the last cell that has been edited any point in the life of the spreadsheet, even if that cell has since been erased.

Guest Speaker

  • David H. Ringstrom, CPA

ATAAA Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.

ATATX Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.

ATAOP Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or training@auroratrainingadvantage.com.

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.