Excel VBA training Portsmouth – Provisional Course Outline

Where training face-to-face may still not be viable, we are also offering training by videoconferencing means, usually via Microsoft Teams or Zoom. Do get in contact if we can help you in this way. Click for further details of upcoming sessions. We are also happy to do customised online training to meet your requirements. Contact us for further details.

We have now also resumed face-to-face training at client site.

Excel VBA training Portsmouth

Our Excel VBA training Portsmouth course outlines can be tailored to meet the needs of the individuals or organisation. We train at your site in Portsmouth and in other towns in Hampshire, Wiltshire, Somerset and Dorset.

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.

Number on coursePrice per day
1 - 1 training£345 + VAT
2 - 4 people£525 + VAT
5 - 8 people£745 + VAT

Excel VBA training Portsmouth – Course Objectives

On completion of the Excel VBA training course delegates will have gained confidence in using Excel VBA. As a result, they will be able to:
• Record macros using the Macro Recorder
• Create and edit macros using VBA
• 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. In addition, there is plenty of opportunity for delegates to practise the new skills they learn at the computer. As a result, learners quickly gain confidence in using VBA.

Pre-requisites – Familiarity with the Windows environment, and a good knowledge of Excel. For instance, 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
  • The VB Editor
  • 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

  • Expressions and statements
  • Declaring variables
  • Understanding data types
  • Working with variable scope
  • Using intrinsic functions
  • Constants
  • Intrinsic constants
  • Message boxes
  • 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
  • For… To…Next structure
  • The For Each…Next
  • 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
  • Label control
  • Text box control
  • Command button control
  • 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

  • Creating a pivot table using worksheet data
  • Pivot table objects
  • Working with the pivotfield collection
  • Assigning a macro to a toolbar or the Quick Access Toolbar

Code debugging

  • Looking at errors
  • Debugging tools
  • Setting breakpoints
  • Stepping through code
  • Using Break mode
  • Working out the value of expressions

Error handling

  • VBA error tracking options
  • 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 07477 578417 or by sending an email to info@archwayct.co.uk or filling in our contact form.