Macros: Part 3

Level: Advanced

Available Durations:

• 90 minutes
• 100 minutes
• 120 minutes

This topic is best presented in 90 minutes or more.

Description:

Part 3 of this series on Excel macros is designed to expand 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 Microsoft 365 (formerly known as 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 webcast.

Microsoft 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.

Topics/Areas Typically Covered:

• Displaying the Project Explorer and Properties windows within Excel’s Visual Basic Editor.
• Providing feedback to a user by way of the MsgBox method within a macro.
• Contrasting Do While and Do Until Loops.
• Exploring three ways to cause a macro that’s running in Excel to halt.
• Streamlining programming code by using the With…End With statement.
• Returning the underlying number for a color assigned to a worksheet cell for use elsewhere.
• Making decisions in Excel by way of IF statements.
• Learning how to use a single line of programming code to unhide all worksheets within a workbook.
• Inserting blank module sheets into Excel workbooks.
• Consolidating data from multiple workbooks into a single worksheet by way of an Excel macro.

Learning Objectives/Why You Should Attend:

• 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.

About the Instructor:

David H. Ringstrom, CPA is the owner of Accounting Advisors, Inc., an Atlanta-based spreadsheet consulting firm that he started in 1991. Throughout his career David has spoken at conferences on Microsoft Excel, and written dozens of freelance articles about spreadsheets. He offers Excel and Access training and consulting services nationwide.
Pricing and Format Options:

Pricing and Format Options:

Click here to learn about the presentation format and view pricing information.

Click here to view other topics.