International experts in Excel

Your power-packed agenda  

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.

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 formulae 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.
• Create For, For Each and Do Until loops.
• Display progress of a loop.
• Create macro to highlight cells of a certain value.
• Create 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 formulae.
• 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.

Put controls on the worksheet

• Program Forms and ActiveX command buttons.
• Add check boxes, option buttons and other controls to a worksheet form.
• Calculate with the values of the controls.

Who should attend:

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