Microsoft Excel VBA Training – Level 2 (2 days)

    Scheduled courses in Peterborough or £1,190, plus VAT, on-site.

    Our Excel VBA Level 2 course is designed for experienced Excel users who have already begun to create macros and know their way around Excel’s Visual Basic Editor. This course delves deeper into the Excel object model and provides delegates with practical experience of developing their own VBA solutions.

    Excel object model

    About classes and objects, Objects and collections, The hierarchy of objects, Properties, methods and events, The Locals Window, The Immediate Window

    Function procedures

    Sub versus function, User-defined functions, Declaring a function, Calling a function, Private and public scope, Defining arguments, Optional arguments

    The Application object

    When to use, ScreenUpdating property, DisplayAlerts property, Setting CutCopyMode to false

    Dialogue boxes

    MsgBox function, InputBox function, Application.Dialogs

    UserForms

    Creating a userform, Displaying a userform, Adding Label controls, Adding a TextBox control, The CheckBox control, The OptionBox control, ComboBox and ListBox, SpinButton controls, Adding CommandButtons, Setting control properties

    Workbooks and worksheets

    The Workbooks collection, The Sheets collections, The Window objects, Workbook events

    Ranges and names

    Column and Row properties, CurrentRegion property, The Union method, The Intersect method, Using the Name property, Working with named ranges

    Pivot tables

    Pivot table code overview, Creating a pivot table, PivotField object, PivotItems collection, Modifying a pivot table, Using external data

    Charts and drawings

    About programming charts, Working with embedded charts, Working with independent charts, Controlling chart formatting, Assigning a range to a chart, Positioning a chart, Making a chart active/inactive, Editing data series, Controlling chart labels, Drawing shapes, Setting shape attributes