Learn macros for automating tasks

• Record a macro for a simple task.
• Create a button to run a macro.
• Build a macro that imports a text file, fills in missing data and inserts formulas.
• Structure a long macro into shorter, more understandable procedures.
• Discover the technique for selecting the imported range whatever its length.
• Record relative movements.
• Debug a macro by running one instruction at a time.
• Program the flow with If..Then..Else statements.
• Prompt the user for input and display messages.
• Create a loop to delete certain rows of a range.

Apply protection

• Stop users from making unwanted changes.

Recap range names

• Understand why range names are essential for macros, file-links and much more.
• Create, modify and delete range names, and learn the benefits of AbleOwl naming conventions.

Create reliable, easy-to-follow formula links to other Excel files

• Build your formula-links to other files so that changes to the source file don’t affect the destination.
• Structure your links to make them easy to follow and change.

Ensure valid entries with Data Validation

• Discover how to allow only number or date entries.
• Provide an in-cell drop-down of valid entries.

Follow best-practice rules for reliability

• Following on from previous courses, pick up more conventions for reliable, better-structured workbooks.

Break, change and locate file-links (optional topics)

• Discover how to remove or repoint links to a different file.
• Know how to locate where the links are.

Prerequisites

Excel users who are already competent with formulas and formatting, and who use Excel constantly. Those who have tasks that could be automated or who want to make spreadsheets easy and robust for others to use. Please note that the macros topic takes up over half of the course. Prior attendance at the Best practice essentials course is not mandatory, though an understanding of the range names topic it covers would be particularly useful.

• Be assured the advice you get is well-proven and utilised.
• Feel free to ask questions.
• Get time away from the office and constant interruptions.
• Enjoy a friendly environment where you don’t feel you’re holding others up.
• Instantly apply what you’ve learned on return to work.