Excel VBA Programming: How to Record a Macro in Excel [Tutorial Video] (19:16)

We get a lot of questions about Excel VBA programming, macros, and other programming languages such as 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 method is to learn how to record a macro in Excel, review and edit the code in the VBA Editor, and then tweak and optimize it.

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.

Excel VBA Programming: 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 much logic or error-checking.

An “input box” is used for assumptions in financial models, such as the highlighted areas below:

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 keystroke 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.

The Format Painter does not work because it incorrectly copies the Number Formats, and it can’t detect the difference between filled cells and empty cells.

And finally, the Styles feature in Excel is not the best solution because then you’ll have to select all the cells manually and apply the Style first.

Updates or changes are easier to handle with Styles, but we rarely change the formatting of Input Boxes anyway, so this point does not make a big difference here.

How to Record a Macro in Excel: Part 1

To record this macro, we can start by making sure the Developer Toolbar is available within Excel.

To do this in PC/Windows Excel, go to Alt, T, O for Options, then Customize Ribbon, and make sure Developer Toolbar is checked:

In Mac Excel, you can press ⌘ + , to access the same Options or Preferences menu.

Then, go to the Developer tab in the ribbon menu and click “Record Macro”:

You can call this macro something like “Color_Code” and you can assign the shortcut key Ctrl + Shift + I to it:

Once you’ve done this, go to any cell that might be considered an “Input Box” and do the following:

1) Press Ctrl + 1 or ⌘ + 1 on the Mac, move to the Alignment tab, and select “Center” for Horizontal.

2) Go to the Font tab and change the Color to Blue (optional – we don’t change this in the video).

3) Go to the Border tab and select a Dark Grey color and “Outline” for the Border Style under Presets.

4) Go to the Fill tab and select a light yellow color.

5) Now, exit out of the Ctrl + 1 or ⌘ + 1 dialog, go back to the Developer Toolbar in the ribbon menu, and press “Stop Recording.”

When you’re done recording, go back to the Visual Basic Editor with Alt, L, V or Alt + F11 on the PC (no shortcut in Mac Excel, so go to the Developer Toolbar and navigate to Visual Basic manually).

You’ll see that it has automatically generated code that looks something like this:

Excel VBA Programming: How to Optimize It

While this code “works,” 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.

The third issue is more difficult to fix and requires additional training in Excel VBA programming, but we can fix the first two points with simple code reorganization and RGB values for the colors.

A “fixed” version might look like this:

More Advanced Tips and Tricks

To skip empty cells and format the input boxes differently based on whether each cell contains a formula or constant, 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.

We cover these more advanced commands in our Excel & Fundamentals course, but here’s a quick summary: