Microsoft Excel Macros Course

This example-based, hands-on course introduces you to the fundamentals of writing Excel macros. You will learn how to create procedures to automate your work, remove the risk of inconsistencies and errors in repetitive tasks and above all, to save time.

You will write and edit macros for Excel, using Visual Basic for Applications and the Integrated Development Environment.

Upon successful completion of this course, students will be able to:

• Create new macros and edit existing ones
• Assign those macros to toolbar buttons and to get them to run automatically
• Create sub procedures to interact with the user, asking for information and returning information
• Format a worksheet, including text, cell borders and shading and use custom number formats through VBA
• Enter data in cells and retrieve data from cells programmatically
• Assign headers and footers programmatically and work with workbook properties
• Create code loops to iterate through sheets in a workbook or cells in a range, in order to process each item in them in similar ways
• Create programmatic tests and have the code respond in different ways depending on the outcomes of those tests

Delivery Method: Instructor led, group-paced, classroom-delivery learning model with structured hands-on activities.

This course assumes that you have a sound grasp of working with excel, including writing worksheet formulas (such as SUM, MAX, MIN, AVERAGE etc) and formatting data. Any experience of recording and stepping through macros will be beneficial, but is not a pre-requisite for attendance.

To effectively understand this course, we recommend that you take the Microsoft Excel Level 1, Level 2 and Level 3 courses, or have equivalent experience.

This course is rated as an Advanced Course, suitable for accomplished computer users.

Topics covered on the 2 day Microsoft Excel Macros course

Part 1: Basics of writing VBA

Part 2: Selecting objects and setting values

• Overview
• Properties and methods
• Named vs Positional arguments
• Examples of Properties and Methods
• Exercise – selecting cells and inserting data

Part 3: Setting format properties

• Exercise – adding formatting to a worksheet

Part 4: Using variables to store information

• Data types for variables

Part 5: Interacting with the user

• Using an InputBox to get information
• Some examples of using the InputBox function
• Exercise 3 – Using InputBoxes to automate headers and footers

Code for Exercise 3

• Using a MsgBox
• Using a MsgBox to give information to the user
• Using a MsgBox to get information from the user
• Exercise 4 – Using MsgBox functions
• Exercise 5 – Using InputBoxes, MsgBoxes and functions

Part 6: Branching and looping

• The IF block
• Exercise 6 – Using IF…ELSE…END IF
• Select case
• Using SELECT CASE to test a single conditional against multiple outcomes
• Exercise 7 – Using Loops
• Exercise 8 – Using DO…LOOP

Part 7: Object Variables and FOR EACH… NEXT loops

• Exercise 9 – Using object variables
• Using the UNION function and multiple object variables
• Exercise 10 – Putting it all together