Excel: Macros in Depth (Microsoft 365)

2h 4mIntermediate2020-01-29

Authors

Dennis Taylor

Excel expert with 25+ years of experience

Course details

Learn about how to use macros to automate tasks in Excel and see how to create simple macros of your own. Veteran Excel trainer Dennis Taylor shows how to record a macro in stages, share macros between workbooks, set up keyboard shortcuts to run macros quickly, and use Visual Basic for Applications (VBA) to code macros that can't be recorded. The course wraps up with a macro project that brings together each of the elements in a real-world scenario: converting a mailing list into a database-friendly format.

Topics include:
- Explain automation using macros.
- Analyze the macro from the Developer tab or the View tab.
- Identify the use of macros from a keystroke shortcut.
- Examine the VBA window and VBA components.
- Distinguish between absolute and relative references.
- Apply best practices when testing a macro in step mode.

Skills covered

Microsoft OfficeOffice 365Microsoft 365SpreadsheetsMicrosoft ExcelBusiness Software and ToolsMicrosoftDeep Dive (X:Y)

Concepts

0. Introduction

01 - Automating your work with Excel macros

1. Getting Started

02 - Identifying applicable situations for using macros
03 - How to play back, run, or execute a macro
04 - Recording and testing a macro
05 - Overview of VBA - Altering macro code
06 - Understanding security

2. Running a Macro

07 - Running a macro from the Developer tab or the View tab
08 - Record a macro and play it back from the Quick Access Toolbar
09 - Run a macro from a keystroke shortcut
10 - Run a macro from a button or Clip Art image

3. Using VBA

11 - The VBA window and VBA components
12 - Creating the Personal Macro Workbook
13 - Closing and updating the Personal Macro Workbook

4. Recording a Macro in Stages

14 - Recording a simple macro
15 - Expanding a macro with the If statement

5. Creating Nonrecordable VBA Code

16 - Understanding when to use absolute or relative references
17 - Using For and ForNext statements
18 - Using Do While and Do Until statements
19 - Creating interactive macros

6. Macro Project - Converting a Mailing List into a Database List

20 - Planning a macro
21 - Recording partial code
22 - Using loop structures in context
23 - Testing a macro in step mode
24 - Pulling the data together
25 - Joining two macros
26 - Streamlining macros

Conclusion

27 - Next steps