Excel VBA for finance professionals - part 2

Date:
12 June 2019
Time:
9.30am-4.30pm
Location:
Manchester, London, Birmingham

Member price: £530 + VAT

Non-member price: £624 + VAT


Manchester, 12 June 2019
London, 9 July 2019
London, 22 October 2019
Birmingham, 12 November 2019
London, 19 December 2019

Email BPP to book


What you will learn

This one day course is designed as a follow on from Excel VBA for finance professionals - part 1, and by the end of the course the delegate will be able to demonstrate their knowledge in the following areas of Excel VBA:

  • Understand how to write more complex conditional blocks of code, including areas where VBA responds to users being asked questions, and to process data.
  • Get code to auto-execute, and be able to apply more creativity with their programs.
  • Improve their VBA routines with smoother techniques and smarter referencing.

This one day course provides functional code to take away for further use, more in-depth programming techniques, and practical exercises in VBA compilation. This also covers interfacing with User Forms; declaring variables; and creating more bespoke functions and routines.

Who should attend

Those looking to further their skills in Excel VBA from Excel VBA for finance professionals - part 1, and turn up the degree of complexity in automation a notch or two.

Spreadsheet authors, Excel developers and finance professionals looking to experiment with VBA programming in more depth and to move up from core foundation skills.

Having attended Part 1 or similar foundational knowledge will help participants gain the maximum benefit from this session.

View full event details

Content

Brief recap of fundamentals

  • Recording macros and interpreting the code
  • Absolute vs relative referencing
  • Working with the Visual Basic Editor and Developer tab

Programming in VBA

  • Complex IF blocks
  • “Option Explicit” and declaring variables
  • Identifying problems and limitations
  • Programming etiquette
  • Debugging and stepping techniques

Repeating code with increased power

  • The need for and benefits of loops
  • Implementing the LOOP structure
  • Understanding the FOR... NEXT structure

Event handler tricks and techniques

  • Toggle updates and alerts
  • Prevent screen flicker
  • Decision making on user questions and internal errors
  • Security and lock-down techniques

Further powerful programming techniques

  • Auto execute code on startup
  • Prevent code collapse with “Resume Next”
  • Program a bespoke Pivot Chart drilldown

Introduction to User Forms – design and implementation

  • Design simple forms for data entry
  • Form properties, controllers and commands
  • Create a bespoke security form for changing passwords

Getting creative with bespoke routines and functions

  • Further in-depth User Defined Functions (UDFs)
  • Search function with instance argument
  • Create a “Splash Screen” for a bespoke program
  • Hands-on time with a case study

All exercises and project files used on the course will be available to take home. Laptops with Excel 2013 will be provided on the course. The knowledge and skills gained can be used across all modern versions of Excel.

Location

Manchester, London, Birmingham

Contacts

BPP
Tel: 0330 060 3303
Email: ldicas@bpp.com

Topics

  • Business and Financial management
  • England and Wales
  • Training courses
  • London and Home Counties
  • Manchester and the North
  • Birmingham and the Midlands
  • Technology

Previous Page