A primer in VBA for Excel (1)

Part 1: setup

For whom is this course written?

VBA is not the most efficient scripting language, but it’s force lies in its very intuitive grammar. Trust me, the learning curve is not steep, and you’ll be writing your first macro sooner that you’d think. Excel is the industry standard, and VBA will allow you to heighten your abilities to a new level.

This course is for those who have no prior knowledge of VBA or any other coding language. It will be a very useful primer for application in your junior roles in investment banks and consulting agencies.

Unlike other VBA courses found in bookstores and on online tutorials, this course is aimed at getting to grips with the way VBA should be thought about. Sure, you can record your macro, but once you really know how to code properly, your horizon will appear limitless.

What do I need to know?

This is an introductory class on using VBA for Excel. You are familiar with Excel as a spreadsheet editor, and are at least familiar with the following concepts:

  • Sheets, columns, rows, cells
  • Functions such as SUM, IF, VLOOKUP…

I assume no other knowledge of coding or VBA.

What exactly is VBA?

VBA, or Visual Basic Applications is a scripting language which allows the use of code in some applications, like Word, Excel, Powerpoint… The focus of this course is on VBA for Excel.

“Macros” is another word for VBA code.

Saving VBA code

When you write code, you need to save your file in one of the following formats:

  • filename.xlsm
  • filename.xlsb

While VBA code can be written in other formats, the code will not be saved when you close your file.

PC or Mac?

VBA code can be executed on both PC and Apple computers. However, it is easiest to write code on PC. As of writing, you cannot write VBA on Office 2016 for Mac.

Visual Basic Editor

The VBE refers to the window in which your code is written. To open the VBE, you first need the developers’ tab in Excel:

  1. Open a new or existing Excel file
  2. Go to File > Options > Customise Ribbon
  3. Activate Developer
Make sur the Developer box is checked

You should now see a new tab, the Developer tab, in your ribbon.

The new Developer tab is now visible in your ribbon.

For this course, we will only really focus on two of the above buttons:

  1. Visual Basic: this will open the editor, and will allow you to edit, test, debug code
  2. Macros: this will opens a popup with a list of macros, which you can run.

Customising your VBE

If you opened a new Excel file, it is likely that your editor — the VBE — will look like this. To make writing VBA code more pleasant, we will make a few customisations:

  1. Menu > Insert > Module — this creates a new blank VBA document
  2. Menu > View > Immediate Window — this window is also known as the console, and will be very useful for debugging purposes.
  3. Menu > Tools > Options > disable Auto Syntax Check — this will make it a lot less annoying to make mistakes, trust me. Think of it as a pop-up blocker.
  4. Menu > Tools > Options > Editor Format > set font to Consolas — this is 2016, let’s stop pretending we’re on a typewriter.

Some Vocabulary

There is a lot of vocabulary to get used to when beginning to code in VBA.

  • Macros are little programs written in VBA. These can be either functions or procedures.
  • Modules are the files which contain VBA code, just as a Word document is the file which contains the text which you write.
  • Functions are blocks of code which return a value, i.e. compute something and give you that something in return. They can be reused as many times as you want but always within another code of block.
  • Procedures, or subroutines as they are known in VBA, are blocks of code which are executed when they are called.

It may be unclear what the difference is between a function and a procedure. For now, it will suffice to think that functions compute stuff while procedures do stuff.

Example: computing someone’s age is a function. Changing the font size of a few selected cells would be a procedure.

Next step

Ready to go the next stage? Click here