Macro Recorder in Excel VBA

VBA Macro Reader Use-Cases in Finance

For the typical user, VBA can be used to automate routine tasks and remove the need to manually perform repetitive tasks via the use of macros – but its usage extends to the financial services industry.

Several popular third-party add-ins commonly used in finance were all written in VBA:

• Analysis ToolPak
• Solver Add-In
• Bloomberg’s API
• Capital IQ Excel Plug-In

Let’s say you work in sales & trading and get a file containing your desk’s trade positions each week.

To complete the task, you’d have to regularly parse and clean the data, then perform some VLOOKUPs and calculations on the data, before finally creating a pivot table and sending it off to your manager.

It may take several hours to perform this same set of tasks that you must do every week.

This is where VBA comes in: VBA can be used to create a subroutine (macro) that quickly and automatically performs these actions on any file you pull up.

Once the code is written, you simply run the macro (which can even be assigned to a keyboard shortcut), and it will take the computer merely a few seconds to perform that series of tasks from start to finish, which once took you several hours.

Similarly, VBA is used in investment banking, equity research, portfolio management, and other finance roles to automate processes, test trading strategies, create tools, and perform analysis.

Example of VBA in Project Finance

VBA Macro Reader Capabilities

One easy way to get started with VBA is the “Macro Recorder” built into Excel.

The macro recorder allows you to record your actions (selecting a cell, inputting data, writing a formula, printing, saving, opening files, etc.) and then, like magic, it automatically converts those actions to VBA code for you!

While limited (and often resulting in code that tends to be a bit dirty), the macro recorder is a great tool for building simple macros, as well as for learning syntax.

The macro recorder offers two ways to record a macro.

1. The first is the “out of the box” method, which converts to code that contains hard-coded cell addresses. This is useful if you plan to use the macro on worksheets or files structured identically (like data downloads).
2. The second involves turning on the “Use Relative References” feature before you record your macro. With this feature turned on, your code will contain relative cell positioning rather than hard-coded cell addresses. This is useful if you plan to use the macro in various places within the same worksheet.

Download the Price Data Example Worksheet

Use the form below to download the related data and follow along with the video walk-through:

First Name *

Email *

Submit

By submitting this form, you consent to receive email from Wall Street Prep and agree to our terms of use and privacy policy.

Email provided

Your Download is Ready

Submitting...

Excel VBA Macro Recorder Video Tutorial

Once you’ve got the file open, let’s watch how the macro recorder functions in the video linked below:

Beyond Basics: Writing VBA Code for Advanced Functionality

In VBA, code is written inside an Integrated Developer Environment (IDE) called the Visual Basic Editor (VBE), which resides inside Microsoft Excel and is essentially a text editor that understands certain keywords associated with the programming language.

The Visual Basic Editor uses “IntelliSense” to help with syntax and often makes suggestions for revisions or additions to code.  It also has debugging tools that can be very helpful.

Regardless of the specific programming language you intend to use, several core concepts must be understood to start coding. These are the Excel VBA fundamentals that, once grasped, can allow you to move from one language to another relatively easily.

VBA Macro Reader Fundamental Concepts

As technology evolves and new computer languages are developed, you must learn new syntax, but in general the basic concepts remain the same.

One fundamental concept is the ability to define variables and set variable types (e.g. strings of text, numeric values, integers, charts, pivot tables).

In short, variables store information and are useful for taking in inputs, manipulating them, and later outputting data.

Another important concept is logic. Logic is routinely used not only to determine an output but also to build workarounds to help prevent errors that can crash your program.

Lastly, there is the looping function, which is perhaps the most powerful concept.

Looping is used to repeat your code multiple times. Imagine that you need to perform the same analysis on numerous spreadsheets structured identically. These tasks could be performed far more quickly by looping through the worksheets within the workbook.

Taking it further, you can even write code to loop through all files in a particular folder and perform that same analysis on all files.

Clearly, with the use of looping, VBA can be used to work with large datasets and to perform an enormous amount of analysis more efficiently.

VBA Excel Macro Reader Customization

VBA can be helpful not only for automating procedures, but also for writing your own User-Defined Functions (UDFs).

If an Excel function doesn’t exist for something you want to do, you can use VBA to create your own function.

Additionally, it is possible to create your own interface to interact with a user. This is known as a “user form”, and it enables you to gather several inputs from the user at once.

The user form’s controls can be linked to different sub-procedures so that from a user form interface, the user can choose which actions to take.

Plus, once you have built a complete tool in VBA, you can save your file as an Excel Add-In and share it with colleagues!