We are now back to full face-to-face training at client site. We are also offering training by videoconferencing means, usually via Microsoft Teams or Zoom, if this is your preference. Click for more information about remote training.
Excel VBA training – Provisional Course Outline
Our Excel VBA training course outlines can be tailored to meet the needs of the individuals or organisation. We train at your site in Dorset, Wiltshire, Somerset and Hampshire.
| Number on course | Price per day |
|---|---|
| 1 - 1 training | £345 + VAT |
| 2 - 4 people | £525 + VAT |
| 5 - 8 people | £745 + VAT |
Aims of the course
This course covers automation and customising of Excel using macros and screen items (such as buttons and menus). Delegates learn how to write macros using VB code and assign macros to screen items.
Course Objectives
On completion of the Excel VBA training course delegates will have gained confidence in using Excel VBA. They will be able to:
• Use the Macro Recorder to record macros
• Use VBA to create and edit macros
• Use objects and dialog boxes
• Work with VBA controls
• Use variables, arrays and functions
• Work with Active Data Objects
• Create add-ins
The course is of two days’ duration.
How the course is run – This instructor-led Excel VBA training course has a hands-on approach, with plenty of opportunity for delegates to practise the new skills they learn at the computer.
Pre-requisites – Familiarity with the Windows environment, and a good knowledge of Excel. Prior attendance of the Advanced Excel course would be an advantage.
Provisional Course Outline
Introduction and the Macro Recorder
- Recording a macro using the Macro Recorder
- Recording a macro using absolute cell references
- The Personal Macro Workbook
- Assigning shortcut keys
- Deleting macros
- Recording macros using relative cell references
Using VBA to Create and Edit Macros
- The concepts of storing macros
- Using the VB Editor
- Using Visual Basic Help
- Closing the Visual Basic Editor
- Understanding Macro Security
Working with Procedures and Functions
- Understanding modules
- Creating a standard module
- Copying and pasting procedure
- Creating a sub procedure
- Calling procedures
- Linking procedures
- Creating a function procedure
- Naming procedures
- Working with the Code Editor
Objects and Dialog Boxes
- Introduction to objects
- Navigating the Excel object hierarchy
- Understanding collections
- Introduction to the object browser
- Working with properties
- Using the With statement
- Working with methods
- Creating an event procedure
Using Expressions, Variables and Intrinsic Functions
- Understanding expressions and statements
- Declaring variables
- Understanding data types
- Working with variable scope
- Using intrinsic functions
- Understanding constants
- Understanding intrinsic constants
- Using message boxes<
- Using input boxes
- Declaring object variables
- Using object variables
Controlling Program Execution
- Understanding control-of-flow structures
- Boolean expressions
- If, Else and Select Case
- Do Loops
- The For… To…Next structure
- The For Each…Next
- Using control-of-flow structures
VBA Forms and Controls
- Creating userforms
- Using the Toolbox
- Working with userform properties, events and methods
- Setting control properties in the Properties window
- The label control
- The text box control
- The command button control
- The combo box control
- The frame control
- Option button controls
- Appearance of controls
- Setting tab order
- Populating a control
- Adding code to controls
- Launching a form in code
Working with the PivotTable Object
- Using worksheet data to create a pivottable
- Working with pivottable objects
- Working with the pivotfield collection
- Assigning a macro to a toolbar or the Quick Access Toolbar
Code debugging
- Looking at errors
- Using the debugging tools
- Setting breakpoints
- Stepping through code
- Using Break mode
- Working out the value of expressions
Error handling
- The VBA error tracking options
- Using the On Error statement to trap errors
- The Err object
- Writing a routine to handle errors
- Inline error items
To request further details of any of our courses, or to make a booking, please contact us by ringing 01747 820396 or 07477 578417 or by sending an email to info@archwayct.co.uk or filling in our contact form.
Excel VBA training Dorset locations include:
| Bournemouth | Poole | Sherborne | Shaftesbury |
| Gillingham | Christchurch | Ferndown | Wimborne |
| Blandford Forum | Dorchester | Weymouth | Verwood |
| Merley | Wareham | Bridport | Lulworth |
| St Leonards |
Excel VBA training Wiltshire locations include:
| Salisbury | Swindon | Trowbridge | Chippenham |
| Devizes | Marlborough | Warminster | Tisbury |
| Melksham | Westbury | Corsham | Amesbury |
| Tidworth | Bradford-on-Avon | Bulford Camp | Porton |
| Calne | Highworth | Wootton Bassett | Wroughton |
| Malmesbury |
Excel VBA training Hampshire locations include:
| Southampton | Winchester | Portsmouth | Basingstoke |
| Andover | Petersfield | Ringwood | Havant |
| Gosport | Farnborough | Aldershot | Romsey |
| Eastleigh | Fleet | Alton | Fareham |
| Totton | Hedge End | Stubbington | Bishop’s Waltham |
Excel VBA training Somerset locations include:
| Bath | Yeovil | Wincanton | Frome |
| Taunton | Weston-Super-Mare | Midsomer Norton | Shepton Mallet |
| Radstock | Paulton | Bridgwater | Burnham-on-Sea |
| Chard | Cheddar | Clevedon | Crewkerne |
| Glastonbury |