Complete tasks faster by automating them. Learn how to make it easier for others to use your spreadsheets. Save time by not having to remember a long sequence of steps, and reduce errors through automation.

Create a simple macro

• Use the Visual Basic toolbar.
• Record a macro to format cells.
• Assign a shortcut key to run a macro.
• View and edit a macro.
• Tidy up what the recorder records.
• Create a toolbar button to run a macro.
• Create a macro to print reports.
• Create an on-sheet button to run a macro.
• Add a custom button to Excel's Quick Access Toolbar.

Create an import application

• Create an application to import a text file, fill in another column, and apply an AutoFilter.
• Run a macro one line at a time to debug.
• Break long statements into several lines.
• Generalise a recorded macro.
• Make use of Intellisense to reduce typing.
• Have a macro enter a formula into a cell.
• Understand how to create formulas with R1C1 references.
• Prompt the user for information.
• Prompt to select the appropriate file.
• Discover how to record a relative movement.
• Prevent the display of unwanted Excel prompts.
• Create a macro to run other macros.
• Have a macro select all the spreadsheet data.
• Create breakpoints to debug.
• Change execution point.

Work with range objects

• Select a range of cells.
• Work with ranges of cells without selecting them.
• Test code in the Immediate window.
• Discover the Cells, Columns, Rows and Offset properties.
• Process to the end of a range.
• Use the SpecialCells method.

Program the flow

• Use If..then..else.
• Check whether user cancels at a prompt.
• Display messages.
• Prompt with a Yes/No question.
• Create For, For Each and Do Until loops.
• Display the progress of a loop.
• Create a macro to highlight cells of a certain value.
• Create a macro to protect or unprotect all sheets.

Understand objects & variables

• Understand the difference between properties and methods.
• Store values into variables.
• Represent objects as variables.
• Discover Visual Basic functions and statements.
• Understand default properties and objects.
• Work with collection objects.
• Learn the hierarchy of objects.

Create custom functions

• Develop your own functions for shorter spreadsheet formulas.
• Use Excel functions in Visual Basic code.
• Make your functions volatile if they need to be.
• Distribute your functions to other people.

Handle errors

• Prevent Excel displaying error messages for errors that you anticipate.
• Take constructive action as a result of the error.

Run macros automatically with events

• Have Excel automatically run macros as you open, print, or save a file.
• Work with Project Explorer.

Prerequisites

Excel users new to writing Visual Basic macros and those who have only limited experience of Visual Basic macros.

• Be assured the advice you get is well-proven and utilised.
• Feel free to ask questions.
• Get time away from the office and constant interruptions.
• Enjoy a friendly environment where you don’t feel you’re holding others up.
• Instantly apply what you’ve learned on return to work.