On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Finance, Human Resources, Taxation and Accounting
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
In Part 2 of his Macro Basics series, Excel expert David Ringstrom, CPA, takes you beyond the basics of Excel’s Macro Record feature. He demonstrates how you can enhance the code you’ve created with the Macro Record feature by adding error-handling functions, decision-making capabilities, and interactive features. David also shares techniques, features, and keyboard shortcuts that enable you to eliminate repetitive tasks and become more proficient using Excel macros.
David teaches from the version of Excel in use by the majority of attendees, but he explains any differences in Excel 2007, 2010, 2013, and 2016. His webcasts are fast-paced, and attendees often are surprised at the amount of ground he covers in a session. He welcomes participants’ questions, so come ready to pick his brain. David’s detailed handouts serve as reference tools you can fall back on after participating in one of his webcasts. In addition, he provides an Excel workbook that includes a majority of the examples he uses during each session.
Topics Typically Covered:
- Add error-handling features to Excel macros to compensate for unexpected or missing data entry inputs.
- Bypass filter drop-down lists by filtering based on cell contents with a single keystroke or mouse click.
- Discover how to enable the Developer tab in Excel 2007 and later.
- Gain an understanding of Excel’s Goal Seek feature, which is useful in simplifying what-if analyses.
- Learn how to play back macros you’ve recorded so as to eliminate repetitive or tedious tasks.
- Learn how to use Excel’s Macro Record feature to create simple macros, without having any knowledge of programming.
- Learn the nuances of the Excel Macro-Enabled Workbook in versions 2007 and later.
- Learn what to do if a Debug prompt appears.
- Understand the nuances of assigning keyboard shortcuts to macros in Excel and avoid superseding shortcuts for built-in features.
- Use form controls to simplify data entry as well as to control the input of other users.
- Use the Text to Columns feature to eliminate unwanted text without retyping or to separate text into columns.
Learning Objectives:
- Apply your enhanced understanding of Visual Basic for Applications (VBA) macros.
- List techniques, features, and keyboard shortcuts that will help you eliminate repetitive tasks.
- Identify how to enhance the code you’ve created with the Macro Record feature.
Level: Advancecd
- Introduction
- Enabling the Developer Menu - 00:03:25
- Macro Definition and Uses - 00:07:53
- Viewing a Recorded Macro - 00:19:44
- Viewing the Code - 00:22:30
- Deconstructing a Macro - 00:29:05
- Adding a Macro Button - 00:33:24
- Making Your Macro Interactive - 00:36:25
- Understanding InputBox - 00:41:32
- Coding With Variables - 00:46:13
- Dealing With Errors / Debugging - 00:50:11
- Handling the Cancel Button - 00:54:07
- Handling Invalid Inputs - 00:58:12
- Error Handling - 01:01:57
- Understanding MsgBox - 01:05:10
- Stepping Through a Macro - 01:10:19
- Compile Errors - 01:17:03
- Syntax Errors - 01:20:13
- Additional Interactivity - 01:22:55
- Saving a Macro-Enabled Workbook - 01:40:08
- Close - 01:41:43
Cancel Button: 00:54:07
Compile Errors: 01:17:03
Debug: 00:50:11
Error Handling: 01:01:57
Goal Seek: 00:08:20, 0029:05, 00:36:25
Input Box: 00:41:32
Invalid Inputs: 00:58:12
Macro Button: 00:33:24
MsgBox: 01:05:10
Recorded Macro - 00:19:44
Relative References: 00:06:30
Saving: 01:40:08
Syntax Errors: 01:20:13
Variable - 00:46:13
Visual Basic for Applications - 00:29:15
.XLSM : The .XLSM file extension signifies a Macro-Enabled Excel Workbook. Such workbooks may contain programming code that can automate repetitive tasks in Excel. If prompted, do not enable macros in .XLSM workbooks of unknown provenance because viruses and malware are sometimes transmitted by tricking users into opening such workbooks.
Compile Errors : Compile errors result when you use a reserved word incorrectly in your programming code, such as an object, method, or property name. Many compile errors result in using a reserved word as a macro or variable name. Adding prefixes to macro and variable names can prevent many compile errors.
Goal Seek : A feature in Excel that simplifies repetitive data entry by enabling the user to automatically solve for a specific amount.
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.
Macro Button : A form control that you can add to any Excel worksheet that allows you to run a macro with a single mouse click.
Macro Recorder: A feature in Excel that allows you to transcribe actions you take in Excel into programming code.
Macro Security: By default, you must grant permission to run macros within Excel workbooks. Users often do this on a case-by-case basis by way of an Enable Macros prompt, but in Excel 2010 and later, you can mark a document as trusted or use the Trust Center in Excel’s Options to manage Excel’s macro security settings.
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.
Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.
Relative References: When you reference a worksheet cell in an Excel formula, this defaults to a relative reference, meaning the column letters and row numbers change automatically when you copy or move the formula. Conversely, unless you turn on the Relative Reference feature, macros you record will capture the absolute address of any cells that you effect during the recording process. Relative References instruct the Macro Recorder to transcribe the number of rows or columns to move to next rather than to a specific cell address.
Syntax Errors : Syntax errors typically signify misplaced or missing commas and parentheses but, sometimes, misspellings or omitted arguments as well. Programming code that contains a syntax error will have a red font until the error is corrected.
Variables: Temporary placeholders for information gathered while a macro is running.
Visual Basic Editor : Excel’s development interface that can be used to edit and create macros, user forms, class modules, custom worksheet functions, and other enhancements to Excel’s functionality. The programming interface for Microsoft Excel that can be accessed by way of the Visual Basic command on Excel’s Developer tab or by pressing Alt-F11.
