Excel Agility: Macro Basics Part 3
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
-
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.
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.
- 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.
Introduction
The Big Sheet Problem - 00:04:07
Replicating - 00:04:07
Resolving - 00:08:25
Automating the Last Cell Reset - 00:11:50
Create a ResetWorksheet Macro Shortcut - 00:18:30
Unhide All Worksheets in a Workbook - 00:22:47
VBE Project Explorer and Properties Windows - 00:29:33
Create a Blank Module Sheet - 00:33:05
Building the Unhide Worksheets Macro - 00:37:35
Understanding For Each Loops - 00:41:56
Create a Module Sheet - Combined Macro - 00:45:34
Making Decisions - 00:52:10
Determining if the Workbook Matches Criteria - 00:56:33
Pasting Data into Combined Worksheet - 01:01:53
Inform User of Completion - 00:07:24
Determining the Color Code for a Cell - 01:15:31
Looping Through Each Row in a Range - 01:19:26
Using the With Statement - 01:23:28
Using Select Case to Make Decisions - 01:26:01
Do Until Loop - 01:31:57
Do While Loop - 01:37:24
A Loop that May Crash Excel - 01:41:37
Halting a Running Macro - 01:42:53
Forcing a Macro to End - 01:43:41
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.
