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.
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
Introduction
- automating your work with excel macros
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
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
Using VBA
- the vba window and vba components
- creating the personal macro workbook
- closing and updating the personal macro workbook
Recording a Macro in Stages
- recording a simple macro
- expanding a macro with the if statement
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
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