Excel Data Interview Series: VBAs in Excel

Hey there everyone 😄. Welcome to Day 6 of the Excel Data Analyst Interview Questions series. You can read up yesterday’s topic — Macros in Excel — here. Today, we’ll be looking at VBA in Excel.

VBA stands for Visual Basic for Applications. It’s basically the programming language that sits behind the application and automates tasks. It is used for programming, editing and running application code, so when you record a macro, you’re essentially creating VBA code in the background.

To go to your VBA editor (where you can see your macro code), press Alt + F11, and this opens up the editor window. When viewing your macro in the editor, you’ll see ‘Sub “Your Macro Name”’ e.g. Sub Highlight_Top_Employees. This ‘Sub’ stands for subroutine, and your macro is a subroutine which is all packaged together as a unit. At the left-hand side of your code, you’ll see single quotes and some green text. These are comments that explain what the code does. With this, you can write out/explain what different sections of your code do, kind of like commenting on your SQL or Python code.

In the VBA editor, you can make changes to your code, which in turn modify the macro. Also, you can create a toggle where you can write a code or merge two (or more) different macros e.g. a macro that highlights cells that are ‘unhighlighted’ and clears the formatting from highlighted cells, using an IF statement. You can simply record independent pieces and pull them all together. You can also use a for loop to run through codes multiple times.

The difference between a VBA and a macro is that the VBA is a programming language for developing automated tasks for Excel and other Microsoft Office programs like Word and PowerPoint, while macros, on the other hand, are a collection of commands that is used to replace a repetitive series of keyboard and mouse actions in an application such as MS Excel. VBA is the language you use to program macros and functions. Technically, the difference between VBA and macros is that VBA code can contain both functions and macros, whereas macro code is only the macro part of the code.

Hope you enjoyed this, and appreciate it with your many claps and comments. Be here tomorrow to officially round off our Excel Series. 🤗