Microsoft Excel: Macros (2 day)

Scheduled Dates Locations filtered. Show all UK course locations.

Course Overview

This example-based, two day 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.

Audience

Anybody that is looking to advance their Excel skills and understand the fundamentals of writing Excel Macros. This course is rated as an Advanced Course, suitable for accomplished computer users. It is assumed that candidates will already have a very good understanding of Excel.

Skills Gained

• 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

Prerequisites

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.

Course Outline

Basics of writing VBA

Selecting objects and setting values

• Overview
• Properties and methods
• Named vs Positional arguments
• Examples of Properties and Methods
• Exercise - selecting cells and inserting data
• Exercise - adding formatting to a worksheet

Using variables to store information

Data types for variables

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

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

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

Microsoft Excel: Macros (2 day) / MEX-MA2