Excel: Macros (1 day)

Course Overview

A one day course for established users of Microsoft Excel who need to learn how to create macros to automate their spreadsheets, and to create customised functions. This course is ideal for those with very little or no previous programming experience.

Audience

People who want to acquire a fundamental understanding of programming and have a need to use automation in Excel.

Skills Gained

This course is designed to provide a solid foundation upon which the delegate can plan and develop Excel macros. Good macro programming practices are taught and encouraged throughout the course.

By The End Of This Course You Will Have Learned

• About the types of macros
• recording and playing a macro
• absolute/relative recording
• Visual Basic objects, methods and functions
• range select method
• ActiveCell formula method
• how to write a macro - message boxes, If.Then.Else, the "compress" macro, loop structures, For.Next, Do.Loop, the Offset method
• how to troubleshoot macros - debug, watch and locals windows
• how to use the debug print statement
• Visual Basic command reference
• custom forms
• creating function macros.

Prerequisites

Delegates should have a good understanding of the Windows environment and ideally have attended the Excel Primary and Intermediate courses.

Course Outline

• Recording macros
• Playing a macro
• From the keyboard, the tools menu, or a macro button
• Ab$olute/relative recording
• The differences between ab$olute and relative recording
• Introduction to Visual Basic
• The macro sheet; selecting a range
• Writing a macro
• Writing more complex macros
• Macro objects, methods and functions
• Message boxes
• Input boxes - getting info from the user
• Decision structures (If...Then...Else)
• Loop structures (For...Next, Do...Loop)
• Troubleshooting macros
• Error trapping; the debug window; the watch window; the immediate window; statements
• Custom dialog boxes
• Working with controls; displaying a dialog box
• Function macros
• Creating function macros; writing more complex functions