Excel: Macros in Depth (365/2019)

Explore ways to save time by automating tasks with macros in Microsoft Excel.

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.

Introduction

• Automating your work with Excel macros

1. Getting Started

• Identifying applicable situations for using macros
• How to play back, run, or execute a macro
• Recording and testing a macro
• Overview of VBA: Altering macro code
• Understanding security

2. Running a Macro

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

3. Using VBA

• The VBA window and VBA components
• Creating the Personal Macro Workbook
• Closing and updating the Personal Macro Workbook

4. Recording a Macro in Stages

• Recording a simple macro
• Expanding a macro with the If statement

5. Creating Nonrecordable VBA Code

• Understanding when to use absolute or relative references
• Using For and For...Next statements
• Using Do While and Do Until statements
• Creating interactive macros

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

• Planning a macro
• Recording partial code
• Using loop structures in context
• Testing a macro in step mode
• Pulling the data together
• Joining two macros
• Streamlining macros

Conclusion

• Next steps