In this tutorial, you’ll learn how to use VBA in Excel for investment banking, and how to get quick results and wins from automating your work without reading multiple books or doing anything complicated.

Resources:

https://youtube-breakingintowallstreet-com.s3.amazonaws.com/Excel-Investment-Banking-VBA-Slides.pdf

https://youtube-breakingintowallstreet-com.s3.amazonaws.com/Excel-Investment-Banking-VBA.xlsm

Table of Contents:

2:12 How to Learn VBA Syntax and Your First Macro

10:44 How to Improve and Optimize the Macro

17:40 Recap and Summary

Lesson Outline:

We get a lot of questions about VBA, macros, and other programming languages like R and Python for investment banking and corporate finance roles.

The short answer is that yes, it helps to be familiar with VBA and macros in Excel because they’ll save you significant time on the job, but you don’t need to know them for interviews.

Other programming languages like R and Python are more useful for roles like trading, portfolio management, or quant research or quant funds where you do real statistical work over huge data sets.

It’s worth spending 10-20% of your total Excel training time to learn some VBA basics.

The best way to learn is to record macros in Excel, review and edit the code in the VBA Editor, and then tweak it to optimize and enhance your macros.

Most Excel constructs, like IF statements, AND, OR, and NOT, and INDEX and MATCH, also carry over to VBA, but the syntax is sometimes different.

You can also do a lot more in VBA, such as looping through a range of cells and performing operations on each one automatically based on its contents or formatting.

Your First VBA Macro

One great use case for VBA in investment banking is to create a macro for an “Input Box” in Excel – it’s simple, and it doesn’t require a lot logic or error checking.

Without a macro, it’s quite cumbersome to do because you have to change the borders, alignment, fill color, and font color, and each one is a separate key stroke or mouse click.

Using Copy and Paste Formats doesn’t work, either, because we don’t want to copy the Number Formats – just specific elements of the formatting in each cell.

So, to set this up, we can record a macro with these actions in Excel (Developer and “Record Macro” and assign the shortcut key Ctrl + Shift + I to it).

We can then edit the code by going to the VBA Editor and fixing any issues, such as the incorrect colors being used.

This basic macro works, but there are some issues with it:

Issue #1: There’s way too much code because there are separate “With” statements for the individual borders, interior, and other parts, and we can group these all together.

Issue #2: It’s not very readable because there are too many useless commands, and it’s hard to tell what the colors are.

Issue #3: We should skip empty cells to make it more efficient, and ideally, we should treat cells with formulas and constants differently so we can apply blue or black font colors to them.

We can fix most of these issues by reorganizing the code and using RGB values for the colors.

For the issue with skipping empty cells and formatting the input boxes differently, we can use “Intersect” commands in VBA along with the “Special Cells” property to select the formulas and constants and skip the empty cells.

We can then set the font colors for the formulas and constants separately, and set the “nonEmptyRange” to all the cells containing a formula or constant in the range of cells the user has selected.

Finally, we apply the border, fill, and alignment formatting at the end, assuming that there’s at least one non-blank cell in this range.

source