Introduction to Excel Macros: Part 3 (Currently Unavailable)

Author: David H. Ringstrom

Part 3 of this series on Excel macros expands your ability to write programming code. Excel expert David Ringstrom shows you how to create a macro that can reset workbooks that have skewed scrollbars, along with 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 contrasting 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 the subscription-based Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) 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 course.

Office 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.

Publication Date: November 2018

Designed For
Practitioners who wish to learn how to create and implement macros to automate their work in Excel.

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 and Do Until Loops.
• Streamlining programming code by using the With...End With statement.
• Comparing Do While 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

• State the keyboard shortcut that takes you to the last used cell within your workbook.
• State which line of programming code would count the number of rows in a contiguous block of cells.
• Define the purpose of the word "With" in the context of an Excel macro.

Level
Basic

Instructional Method
Self-Study

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

Program Prerequisites
None

Advance Preparation
None