Excel 2016/2019/365 - Macro - NL

Want to save time on routine tasks in Microsoft Excel? Macros are the answer. In this course, the instructor will explain how macros can be used to automate tasks in Excel, and how you can create simple macros of your own. Learn when and 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 mailing list data into a database-friendly format.

Understanding what macros can do for you

• Introduction
• Definitions
• Limits of the macro recorder

Differences between general (personal macro workbook) and local (workbook) macros

• Specificities of general macros
• Specificities of local macros
• How to choose the macro type

Displaying the Developer tab

• To have access to all macro tools and options

Recording macros

• How to correctly name the macro
• Choosing where to store the macro
• Macro description
• How to correctly record the macro
• Stop recording

Testing macros

• To make sure they work before creating a running support

Assigning macros

• Different types of running supports (icon, button or shortcut key)

Macros and file formats

• Macros in old format files (.xls)
• Macros in new format files (not possible in .xlsx, possible in .xlsm)
• Converting a file that contains macros from old format to new format

Using relative references in macros

• To create a local (workbook) macro that behaves like a general macro within the file

Accessing VBA code

Without knowing VBA there are simple modifications that can be made :

• Renaming a macro
• Merging macros together
• Splitting a macro in several macros
• Deleting commands
• Modifying commands
• Re-ordering commands

Deleting macros

• Deleting a local macro
• Unhiding the personal macro workbook to delete a general macro

Examples of useful macros

• Macro to use a template
• Formatting macros
• Printing macros
• Macros to refresh data
• Macros to sort and filter
• …

For people who want to save time on routine tasks in Microsoft Excel.

Prerequisites: Good knowledge of MS Excel (equivalent to Level 2 course).