Introduction 1 Recording and running macros Record a macro Run a macro Edit a macro Delete a macro Record a macro using relative references Assign a macro to a keyboard shortcut Run a macro by clicking a shape Add a macro to the Quick Access Toolbar Customise a Quick Access Toolbar button Save a macro-enabled workbook Managing Excel 2010 security settings Change Protected View settings Change message bar settings Change data connection security settings Add a digital signature to a workbook 2 Starting with the Visual Basic Editor Introduce object-orientated programming Display the Developer ribbon tab Display the Visual Basic Editor Set project properties Create a code module Create a subroutine Create a function Add a comment to your code Run a VBA routine Rename a code module Delete a code module Export a code module to a text file 3 Working with data and variables Understand data types in Excel VBA Declare a variable Require variable declaration before use Manage variable scope Perform calculations using mathematical operators Define a constant Define a static variable Define an array Define a multidimensional array Redefine an array Define a dynamic array Display an object type Define an object variable 4 Managing workbooks and files Open a workbook Open a text file as a workbook Open a file the user selects Save a workbook Save a workbook in a different format Detect if a workbook is open Close a workbook Create a new workbook Delete a file 5 Managing worksheets Add a worksheet Delete a worksheet Move a worksheet Copy a worksheet Hide or unhide a worksheet Rename a worksheet Protect a worksheet Print a worksheet 6 Managing ranges Activate a cell range Select a cell range Select the active region Refer to cells using Offset Insert a cell range Delete a cell range Hide worksheet columns or rows Create a named range Resize a selectedrange Set the column width Set the row height 7 Managing cells Cut and paste a cell range Copy and paste a cell range Copy and paste values in cells using PasteSpecial Transpose a column into a row Create a cell comment Display a cell''s comment Hide a cell''s comment Delete a cell''s comment Fill a range of cells automatically Copy a range to multiple sheets Add a cell border Find a cell value Replace a cell value 8 Formatting worksheets and worksheet elements Apply bold, italic and underline formatting Change a cell''s font Change a cell''s font size Change a cell''s font colour Change a cell''s fill colour Change a cell''s alignment Apply a cell style Apply a number format to a cell Clear a cell''s format 9 Sorting and filtering data Sort cell data using a single criterion Create a multilevel sort Sort using a customised list of values Turn on filter arrows using VBA code Apply a filter using a single criterion Remove a filter Display a list of unique values Filter data to display two values in a column Filter data to display three or more values in a column Filter data based on values in multiple columns 10 Managing charts Create a chart Move a chart to a chart sheet Add a new data series to a chart Format a chart''s legend text Format a chart''s axis text Export a chart as an image Create a Line sparkline Create a Column sparkline Create a Win/Loss sparkline Delete a sparkline 11 Using built-in functions and statements Use the built-in Open dialog box Prevent screen flicker when running VBA code Suppress and restore alerts Calculate data using Excel worksheet functions Display a message box Get data from an InputBox Display the current date and time Format a date Remove spaces from a string Locate a portion of a string Concatenate strings 12 Managing code ssing logical constructs Create a For.Next loop Create a For.Each loop Create an If.Then.ElseIf statement Create a Case statement Create a Do loop Create a Do.While loop Create a Do.Until loop Call another macro from within your code Refer to objects using a With.End With statement 13 Debugging your VBA code Execute code in the Immediate window Set a breakpoint in your VBA code Watch a value in a routine Step through your code one line at a time Skip over a subroutine Step out of a subroutine Create an On Error GoTo statement Manage errors using an On Error Resume Next statement Manage errors using an On Error GoTo 0 statement 14 Using Excel events in your VBA code Display the available events Run a procedure when you open a workbook Run a procedure when you close a workbook Run a procedure when you save a workbook Run a procedure when a cell range changes 15 Gathering data with UserForms Create a UserForm Add a TextBox to a UserForm Add a ListBox to a UserForm Add a ComboBox to a UserForm Add an option button to a UserForm Add graphics to a UserForm Add a SpinButton to a UserForm Create a multipage or multitab UserForm Write UserForm data to a worksheet Display, load and hide a UserForm Jargonbuster Troubleshooting guide.