VBA for Finance Professionals

Description

This hands-on course focuses on the skills needed to harness the power of macros to help automate worksheet processes. Participants will learn to design, build and error check macros that are robust and easy to use. Participants will be introduced to best practices using VBA (Visual Basic – the programming language for macros) and the construction of powerful, error-free macros.

Prerequisites

Participants should have some computer programming experience and a strong working knowledge of Excel

Time

8 - 16 hours, depending on the amount of material to be covered.

Learning Topics

Using Macros in a Model

Explore macros and Excel’s development tools
Identify activities and processes that are suitable for macros
Learn to use the VBA Editor (ie. the Code, Project, Properties and Immediate Windows)
Develop subroutines and the ability to organize and execute actions within Excel with well-designed VBA code
Learn best practices for recording macros and critical edits to ensure that macros operate for subsequent users
Build an understanding of the key elements of Excel worksheet objects used in macros such as workbooks, worksheets and ranges

Write Code for Macros

Design sample macros and reinforce best practices for programming robust and error-free VBA code
Build and audit looping techniques to perform repetitive actions and automated decision choices
Customize Excel using VBA in order to create highly tailored solutions such as custom functions and to control the user experience with features such as user input forms, special controls and macro buttons
Test, audit and correct macros using the debugging and error detection tools within the VBA editor

Macro Creation

Build and test sample macros that automate common capital markets activities and give the user a chance to explore various capabilities
Create functional macros including a data scrubber, dynamic range creator, table formatting, graphing macros and automated data updates