Course description
Excel VBA for Finance Professionals - Part 2
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.
Do you work at this organisation and want to update this page?
Is there out-of-date information about your organisation or courses published here? Fill out this form to get in touch with us.
Suitability - Who should attend?
Those looking to further their skills in Excel VBA from Part 1 of our Excel VBA for Finance Professionals series, 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.
Outcome / Qualification etc.
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.