Working with Microsoft Excel Macros

About the Course

Designed for
Aimed at frequent users who have an excellent knowledge of Microsoft Excel’s intermediate/advanced principles, this course provides candidates with practical experience of the topics listed in the course outline below. On completion candidates should be equipped with the necessary skills to produce and edit interactive Microsoft Excel Macros, create and customise the Excel menu and toolbar options, and understand VBA Modules.

Teaching Approach
The teaching approach contains a blend of lectures, practical, and one-to-one work and varies according to the people attending. For the most part, candidates are able to work at a pace which best suits them, and emphasis is placed upon quality of learning, rather than quantity.

Requirements
Those attending should have an understanding of Microsoft Excel to at least an intermediate level, be competent Microsoft Windows users, and be familiar with the basics of file and disk management. An understanding of Microsoft Excel Macros capabilities is useful although not a pre-requirement for this course.

Course Material
The majority of this course is spent with a set of prepared exercises and projects so that those attending gain a firm foundation of Microsoft Excel Macro capabilities. Professionally presented course notes are provided and these may be retained for reference afterwards.

Duration
This training program is two days in duration.

Course Outline

1. Creating and Running a Macro
Introducing Macros
Preparing to Record a Macro
Recording a Macro
Stopping Recording a Macro
Running a Macro from the Macro Dialog Box
Running a Macro with a Shortcut Key
Editing Macros
Editing the Macro Options
Recording Relative and Absolute References

2. Assigning Macros to Toolbars
Recording a New Macro – (Revision)
Specifying a Shortcut Key – (Revision)
Adding a Custom Button to a Toolbar
Changing the Name of a Toolbar Button
Changing the Button Image
Assigning a Macro to the Button
Running a Macro Using a Toolbar Button
Changing the Options for an Existing Macro – (Revision)
Running a Macro with a Shortcut Key – (Revision)
Creating a Toolbar
Moving a Toolbar button
Deleting a Toolbar
Resetting a Toolbar

3. Assigning Macros to Graphic Objects
Drawing a Graphic Object on the Worksheet
Assigning a Macro to a Graphic Object
Selecting an Object to Which a Macro is Assigned
Adding Text to a Graphic Object
Using a Graphic Object to Run a Macro
Changing the Macro Assigned to a Button
Removing a Graphic Object from the Worksheet

4. Working with Modules
About Modules
Moving Around the Module
Recording a Macro in the Personal Macro Workbook
Deleting a Macro from the Personal Macro Workbook

5. Editing a Macro
Editing a Macro
Statements and Comments
Adding Commands to a Macro
Adding Comments to a Macro
Running a Macro from the Microsoft Visual Basic Editor

6. Further Macro Editing
Removing Lines from a Macro
Using Comments to Remove Lines
Finding and Replacing Text in a Macro
Copying and Pasting Commands

7. Using Worksheet Data in a Macro
Introducing Objects, Properties and Methods
Getting Information from the Active Cell
Using ActiveCell in a Macro
Dealing with Errors
Getting Information from Other Cells
Using Range in a Macro
Introducing Variables

8. Controlling the Flow of a Macro
Inserting a New Module
Running Statements When a Condition is Satisfied
Running Statements When a Condition is Not Satisfied
Using ElseIf to Check Several Conditions
Using Multiple Conditions in a Single Statement
Using Multiple If… Then Statements

9. Interacting with Macros
Displaying a Simple Message Box
The MsgBox Function
Customising a Message Box
Using Message Box Buttons
Using Worksheet Data in a Message Box
The InputBox Function
Using an Input Box in a Macro
Getting User Input

10. Running Macros Automatically
Running a Macro When You Open a Workbook
Running a Macro When You Close a Workbook

11. Editing Menus
Creating a New Menu Item
Creating a Submenu
Removing Items from a Menu
Creating a New Menu
Restoring the Default Menus

12. Creating a Custom Menu Bar
Creating a Custom Menu Bar
Displaying a Custom Menu Bar
Displaying the Default Menu Bar
Switching Between the Custom and Default Menu Bars

13. Working with Macros – (Project)
Course Revision Exercise