Excel Macro Basics Part 1
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Software
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
During this informative webcast, Excel expert David H. Ringstrom, CPA, introduces you to the concept of Excel macros as well as to the Visual Basic for Applications (VBA) macro. In addition, he demonstrates how to use Excel’s Record Macro feature to automate repetitive tasks, such as cleaning up reports exported from an accounting package, scrubbing data, and creating your own keyboard shortcuts. While the Macro Record feature is easy to use, those who aren’t aware of its nuances can become frustrated with its vagaries. David discusses the ins and outs of macros so you can begin implementing them immediately.
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. He also provides an Excel workbook that includes a majority of the examples he uses during each session.
Topics Typically Covered:
- Convert .XLS workbooks to the up-to-date .XLSX format with just two keystrokes.
- Decipher and manage Excel’s macro security prompts.
- Discover how to use Excel’s Personal Macro Workbook.
- Learn how to use Excel’s Macro Record feature to create simple macros, without having any knowledge of programming.
- Learn the risks and frustrations of using merged cells in Excel worksheets.
- Understand how macros are used in Excel, along with the risks and benefits associated with them.
- Use the Text to Columns feature to eliminate unwanted text without retyping or to separate text into columns.
Learning Objectives:
- Identify how to prevent macros from being discarded from workbooks when saving.
- Apply the Unhide command to locate and manage Excel's Personal Macro Workbook.
- Define how to create general-use macros with the Relative Reference setting for Excel's Macro Recorder.
Introduction
Definition of a Macro - 00:02:03
Macro Recorder - 00:04:07
Merge Cells - 00:11:01
Shortcuts for Running a Macro - 00:18:06
Automating Center Across Selection - 00:23:49
Manually Running Our New Macro - 00:26:56
Creating an Icon for a Macro - 00:28:23
What are the Risks? - 00:32:48
Personal Macro Workbook - 00:37:35
Deleting a Macro - 00:40:55
Path to Personal Macro Workbook - 00:43:32
Re-recording with Relative References - 00:54:06
Playing Back the Recording Macro - 00:58:04
Planning to Automate a Repetitive Task - 00:59:50
Accounting Report Macro
Clean Up - 01:01:15
Filtering - 01:05:01
Text to Columns - 01:07:16
Stop Recording - 01:10:45
Creating a Clickable Shape - 01:14:51
Saving Workbooks That Contain Macros - 01:17:55
Page Footer Macro
Recording - 01:23:12
Viewing - 01:28:20
Editing - 01:31:20
Creating a Phone List Clean-Up Macro - 01:33:08
Speaker Wrap Up - 01:41:22
Automate a Repetitive Task: 00:59:50
Center Across Selection: 00:13:48, 00:23:49
Deleting a Macro: 00:40:55
Macro Recorder: 00:01:00, 00:02:17, 00:04:07
Macro Security: 01:20:30
Merge Cells: 00:11:01
Personal Macro Workbook: 00:37:35, 00:43:32
Quick Access Toolbar: 00:29:15
Relative References: 00:54:06
Saving Workbooks: 01:17:55
Shortcuts for Running a Macro: 00:18:06
Text Columns: 01:38:43
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 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.
Personal Macro Workbook: A hidden workbook that typically serves as a repository for macros you wish to always be available in any Excel workbook you have open.
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.
