Excel Automation with VBA

This course is designed for finance professionals who want to learn how to automate repetitive tasks to make their job easier.  The course will take users through the basics of the VBA language using the macro recorder and will move quickly to editing VBA.   The course provides participants with a thorough understanding of how write Excel VBA code from scratch.   This course will help people who are looking for better automation and control over important financial information in an increasingly complex environment.

Students will be able to:
  • Record macros and adapt the code to be more streamlined and readable.
  • Create macros from scratch using as little code as possible to achieve automation.
  • Understand VBA concepts and features, create procedures and sub routines.
  • Create common Excel VBA variables and Excel objects and concepts to ensure your code is consistent and easily read.
  • Use the autofilter in VBA to efficiently isolate and manipulate data.
  • Copy data efficiently inside a single sheet or to multiple sheets.
  • Use VBA to effectively sort data in ascending or descending order on the fly.
  • Create and Excel looping routine to perform repetitive tasks, saving time in the process.
  • A solid grounding into the vernacular of the VBA language so they can read and understand other people’s code.
  • Use breakpoints and the locals window to help to debug code.
  • Add comments to code to help with readability and handover of procedures.
  • Create variable declarations and the use of explicit variable declaration to help trap errors before they have the opportunity to manifest.

VBA Component

The course is a masterclass in coding technique, simplifying the language into the most commonly used macros for people in finance positions.  The delegate is taken beyond the chaotic creation of macros by using the recorder and copying code from the web to instead, giving them the knowledge and skills needed to break free and create their own procedures from scratch.  The course demonstrates well-worn techniques to reduce development time and create better managed and robust Excel VBA applications.

Practical Component

The course builds programming skills starting with the fundamentals and progressing to more sophisticated programming techniques which have practical applications to the finance professional. Along with the VBA tools and techniques mentioned above the course is designed to follow the following key objectives;

  • Follow a logical, structures and disciplined approach towards designing and building Excel VBA procedures.
  • Provide a stronger understanding of the VBA language with repeatable techniques to assist participants with automation of routine tasks.
  • Help with automating the importing of data, cleaning that data, archiving existing information and similar activities.
  • Adding simple VBA code to a model to automate common Excel tasks which will aid with model calculations.
  • Learn techniques to reduce common coding errors making your models more robust.

Course Materials

The course materials include a 40+ page booklet which will act as a reference guide during the course while acting as a valuable reference following the course should participants wish to refresh their knowledge.In addition, a set of files will be provided which includes the sample versions and worked versions of the course files, as well as a number of reference documents, and a set of links to other useful resources including relevant parts of thesmallman.com.

Benefits

This course provides users with valuable knowledge and design principals to assist in the creation of sound Excel VBA applications.  The course will instil in participants the confidence to be able to prepare their own fit for purpose VBA code.  Understanding of important issues such as the appropriate use of code of Excel inbuilt controls and how to avoid common errors.  The techniques and exercises are a result of years of application and research and as such are best practice.  The result is code that is repeatable once users return to work.

 

It would be most helpful if participants bring a USB to class so they can take the file with them upon completion.