MODEL CITIZN

View Original

VBA might be dying but it ain’t dead

Guest User

4 August | by Benjamin Stoter, Marcus Small and Lance Rubin

Introduction to the co-authors

Benjamin Stoter is a Chartered Accountant and is a self-taught financial modeller. 

He completed his articles at KPMG whereafter he joined a national Real Estate company and worked in various roles such as Asset manager, Operational and Financial controller; Group Financial Manager and ultimately Group Head of Treasury, FP&A and Systems.

Benjamin joined a private International Group in 2019 as a Group Finance Business Partner focusing on managing and supporting Global Treasury and Structured finance; FP&A and Reporting; Systems and IT and working closely within the group of local and international companies providing strategic support and analysis to all of the group business heads.

Benjamin is also the founder of the “80/20 Finance Business Partner” app that features high value aggregated, distilled and curated content from some of the Top Financial professionals across the world. He has a passion for automation and believes in continuous improvement. He is always looking for ways to do things better, faster and simpler and strives to constantly grow his knowledge and challenge the status quo.

Marcus Small is the XL jockey atop the stallion known as TheSmallman.com. A self-proclaimed weapons grade Excel website designed exclusively for rapid knowledge transfer. The site is famous and fabulous and if you have not dropped by, make the world a better place, join millions of others, fill your riding boots and jump on board the digital pony that is his humble Excel website.

Why did the Co-Authors select this topic?

VBA is a key component of automation and transforms tasks from hours/days into seconds.

Although many tasks previously performed in VBA can now be performed with Power Query, Python and other languages, VBA still has a very important place, generally a lot simpler, for financial modellers who are not hard-core coders.

Both authors wanted to share their thoughts on the often-forgotten topic and skill

Marcus’ thoughts include:

  • Keep things simple and never using VBA unnecessarily - more people will be able to manipulate and re-use whilst unnecessary use of VBA excludes many.

  • Use looping constructs sparingly, they are often not required – use Excel’s native functions for efficiency eg Special cells feature to remove say blank cells rather than looping through each cell, testing for blank and removing the cell.

  • Almost never need to use Select or Activate – this will just slow down the VBA code.

  • Power Query covers many (but not all) automation tasks of VBA, refer first point above.

Benjamin’s thoughts come from utilising VBA extensively in his past and current roles and with other automation tools (including Power Query).

His focus on this article is to provide other finance professionals with real life practical tips on how to utilize VBA to extract the most value in the shortest space of time.

Some of Benjamin’s favourite techniques include:

  • Multi-Company Group Financial Reporting Automation -see the link below.

  • Looping through multiple parameters – a process which relates to a company or product parameter for individual reports

  • Passing parameterized SQL - statements that are used to extract database information through VBA

  • Creating an automated Table of Contents – helps users to see what’s in your workbook.

  • Automate Pivot table creation and refreshing – saving you time having to refresh the data for the pivot table which requires a right click each time.

  • Automated dashboard and project management report creation – especially when these are on a regular basis and many many more.

Topic and context in summary

The objective of this article is to provide finance professionals who have little to no experience with VBA with the key fundamentals to start using VBA immediately by applying the Pareto principle (80/20 rule) giving you the highest value in the shortest space of time.

Firstly, this might take some by surprise, but it is important to note that the best solution is where there is no VBA at all. As a model builder you are the custodian of a particular file (galaxy of knowledge for that process). 

The whole world does not possess the herculean visual basic skills that you do so simple spreadsheet design that follows a sound set of rules will be easier for others to pick up and enhance, long after you have taken your seat as CFO of some Fortune 500 company.

 Avoiding VBA is the best answer as its not everyone’s cup of tea (due to its lack of transparency vs Excel formulas) and it is possible to create wonderful financial models without its aid, but there are exceptions where there simply isn’t another way.

 If you have to use VBA it's best to be forearmed with the tools to use it effectively. 

 When you have a logical, repeatable process that you do over and over, then it’s time to consider automating it.

 However, if you’re just doing a one-off thing that will take you 10 minutes to do and will probably never do it again, it will probably not be worth your time making a macro to get the job done.

 Always consider the potential time savings versus the time to create the macro as well as the end user and their understanding of VBA.

If you had to teach this topic in a class to school kids, what key tips would you give them to focus on?

Imagine having a friend who does all your chores for you for no compensation and never complains about it either!

 You can give your friend a specific set of instructions in English and they perform your chores for you.

 As you work with Excel, you will want to perform certain tasks.

 Just like you told your friend to do your chores, you can also tell Excel to perform certain tasks for you.

The process of telling Excel what you want it to do for you is called computer programming.

 Just as you used English to tell your friend what to do, you can use a language called VBA(Visual Basic for Applications) to tell Excel what to do without having to perform the tasks on Excel yourself. 

VBA is a programming language built into Microsoft Excel that allows you to “record” specific actions like keystrokes, cell formatting changes, formulas, etc.

 Excel is then able to repeat those actions, automatically, for as long as you need them.

 VBA can also be used in the entire Microsoft Office suite however Excel is where it has the most utility.

A Macro is a name we give the script/group of instructions we write using the VBA language. Macros can automate just about any task—like generating customised charts and reports, performing word and data processing functions.

For example, you can write a macro that, with a single click, will make Excel create an entire balance sheet, income statement and cash flow from a series of accounting entries in a spreadsheet.

VBA can even be used to:

  • Create an entire accounting system and generate financial ratios,

  • Analyze and adjust huge amounts of data quickly,

  • Create and maintain complex trading, pricing, and risk-management models,

  • Forecast sales and earnings,

  • Create lists of customers’ names or any other content,

  • Create invoices, forms, and charts,

  • Formatting downloaded data or updating the pivot tables.

 Taking hours of your work into minutes or seconds of automation so you can add value in other areas through conversations, story-telling and decision making and what-if analysis .

What practical steps can people take to learn more

The key to getting started with VBA and extracting the most value in the shortest space of time is to learn how to record Macros as well as copying existing VBA code and modifying it to suit your needs. These principles are summarised below: 

Record Macros using the Macro Recorder

The Macro Recorder is one of the easiest ways to get started with VBA and doesn’t require any coding knowledge. It records user actions for playback at a later time.

The main advantage of using a macro recorder is that it allows a user to easily perform complex operations much faster and with less effort without requiring custom computer programming or scripting.

It’s only the start, so some coding inefficiencies may exist, but Rome wasn’t built in a day. The following is a link on how to record an Excel macro.

Copying code from the internet and using it straight away

Step 1: Open the Visual Basic Editor

Open up the Visual Basic Editor by using the keyboard shortcut Alt + F11

Step 2: Create A New Code Module

Select Insert >> Module.

You should see a new module appear in the Project Explorer (most likely named Module 1).

Step 3: Paste in Your Code

First, make sure you have Module 1 selected (it’s name will appear at the top of your window in the Title Bar).

Paste in your code!

Note: Most Macro’s from the internet generally already have a name with it, but if not, follow the steps below to create a name:

Type the word “Sub” and then give your macro a name. Type “()” after your macro name. After you have done this, you can hit your Enter key and the phrase “End Sub” should automatically appear. You can then paste your code after the name and before the “End Sub”.

 Step 4: Create a Button to link the Macro

We will want to make an easy way to trigger the macro. You can create a button and assign a Macro to it by selecting “Insert Button” under the developer tab.

Select the relevant Macro from the pop-up and label the button by right-clicking and select “edit text.”

You now have a working Macro congratulations!

Note: Ensure that you also save the workbook as .xlsm format. (Macro enabled, otherwise you will lose the Macro in the workbook.) You can also save it as .xlsb at the macro will still work and file will be smaller and run quicker.

Step 5: Advanced techniques - Editing VBA Code:

If the Macro does not perform exactly as you require it, then we may need to edit the code.

The quickest way to do this is to open up the VBE editor again.

Most lines start off with an action (e.g. “Selection”), followed by a period (.), followed by another action (e.g. “Copy” or “CutCopyMode”). Each of these items represent either an Object(Cells, sheets, workbooks etc) that Excel recognises, a Property of the object, or a Method (or action) that can be undertaken.

Identify where the code refers to certain objects i.e. perhaps it is a range of cells. Try to figure out what is happening to this specific range of cells when the Macro runs and alter this range to suit your specific worksheet. (A very simplistic explanation! If editing is required, steps above may not always work depending on your knowledge of VBA but it is a good place to start and try reverse engineer what a Macro is doing. You will also learn a lot while doing it!)

 Step 6: Guidance/ rules when using VBA Code:

These are not hard and fast rules but a solid set of principles to follow whenever you are using VBA including the following:

  • Think before you use VBA. Only use it when it is completely necessary.

  • Use Option Explicit – it will force you to declare all your Variables.

  • Break projects into small chunks and test, test, TEST.

  • Use the recorder – but after you have used it make a copy and try to take all the noise out.

  • Use the Locals window and break points to test your code.

  • Use Filters and Special cells liberally – reduce your reliance on Loops.

  • Challenge yourself. Try problems online and compare your solution to the vb masters

Useful links/places and VBA code you can try yourself:

Here is a treasure chest of some of the most useful VBA code from a financial professional’s perspective:

Comprehensive Macro Vaults:

For the Rocket Scientist

Structured Courses

Websites and Videos

Books

  • Excel 2019 Power Programming with VBA is a great book to pick up. It’s filled with amazing examples and easy/clear to read chapters. Useful to not only learn but to use as reference material.

How important is this skill in the context of learning financial modelling?

If you want to pursue a career in financial modelling, it a valuable skill to know how to use VBA to automate repetitive day to day activities and develop and maintain complex financial models.

They allow you to increase efficiency and accuracy as well as providing more flexibility in building models.

Learning how to use VBA is a very quick way to differentiate yourself from the millions of other Excel users throughout the world.

Excel users instantaneously associate folks who utilize VBA as an advanced user and will look to them for help. 

VBA should however not be the only automation language one learns for financial modelling. Evaluating the context of what you would like to do with VBA is key.

There are various other automation tools that can also be used in conjunction with VBA that will be covered in a later article. Learning VBA alone is unfortunately not sufficient to keep up with today's times.

How does all this disruption, AI and automation talk impact this topic and will VBA die?

VBA is essentially an automation tool in itself, many data sanitization processes that had to be done previously in VBA can however now be done in Power Query and other programming languages but there are still many things that VBA is required for and will continue to be required for, perhaps for the next 10-15 years, there are too many companies still using it. 

VBA is often the quickest way to automate tasks and reports within Office as you do not need to install any additional applications and can start using it straight away.

 It is a coding language used by millions of people across the world and has been around for decades, despite the hype of RPA and other automation tools.

The context of your role is key in determining whether or not VBA is required now as well as in the future.

 If you work in an environment where a significant portion of your job is working in spreadsheets and most tasks involve repetitive actions, this is where VBA shines. Microsoft is in the process of replacing VBA with JavaScript(Office Scripts).

 The fundamental difference is that VBA macros are developed for desktop solutions and Office Scripts are designed with cross-platform support and security as the guiding principles.

 They can work on virtually any device that is running an Office App. Currently, there is no way to run VBA on Excel mobile or Excel Online. JavaScript is extremely fast at pulling data from outside sources. 

The JavaScript replacement plan is going very slow though and the API still cannot fully replace all the functionality that VBA currently gives us. The adoption by the Excel community has been very slow.

VBA has been in decline for professional developers (real coders) for about a decade now as tools such as Python, C#, R or even just Power Query are commonly used to replace VBA or regular Excel.

There are however still many areas where VBA can be applied such as small to medium sized entities who cannot afford full time developers and have to use off the shelf software solutions; banking and finance which have extensive investment in VBA applications and very experienced individuals who are more finance focused as opposed to programmers; super users at large companies who help automate other peoples repetitive tasks.

The truth is that Excel is used by approximately 1 billion people worldwide, especially in finance.

Most people use what they have access to and most people have access to VBA Fortune 500s, Wall street, most large and small companies cannot survive without ad hoc systems built in VBA/Excel.

VBA has been dominated by people outside the IT department. It is a fairly easy language to learn and you do not need anyone’s permission to start using it. The vast majority of VBA developers are self-trained power users who needed to get work done faster and couldn't get the attention of the IT department or large investment to hire external coders to help out timeously. 

Chris Newman, Excel MVP and founder of SpreadsheetGuru sums up this topic very well in his article “Are VBA Macros dead?”, link to full article and key extracts.

VBA is not as difficult to learn as other programming languages and it takes no time to click “record macro”, do some stuff, peak and modify the code or even just search for code on the internet and modify it to suit your needs.

Many companies both large and small still rely heavily on VBA to automate tasks.

There’s just simply too much money/time invested into VBA automation throughout the world for Microsoft to fully get rid of it.

The VBA user-base are not experts in code writing as the majority are Finance, HR, Marketing, or Project Management professionals.

 These are professionals who were able to teach themselves how to write and implement automating scripts to make their lives easier. They are not full-time coders who are used to (or dare I say enjoy) learning new languages all the time.

 The majority of VBA coders were able to teach themselves because the language made sense to them and are not going to be very motivated to learn a whole new language in their spare time, especially with so little information/tutorials available to them currently.

 Hence, this population (the majority) of VBA users are more than likely going to stay within the bounds of what they know throughout their careers because (1) too many companies rely on VBA to automate processes and (2) too many Excel users have invested time in learning VBA.

It will happen one day, but dropping it overnight and learn something completely different is not realistic.

If you want to find out more and follow the rest of the article series be sure to download the Financial Modelling App

If you want to find more information on financial modelling and content visit the Model Citizn website. We have some exciting new announcements due out soon so stay in touch.