Overview of VBA & Macros
What is VBA?
VBA stands for Visual Basic for Applications, the programming language Microsoft Office is built on. Each application on the MS office suite – Excel, Word, PowerPoint etc. – has its own version of VBA. VBA is used to write macros that automate tasks and accelerate workflow.
What is a Macro?
A snippet of code that is going to perform or automate a set of tasks for a user.
In Excel, a macro can be recorded using the macro recorder in the Developer tab, or coded by hand in the Visual Basic Editor. Users create macros to perform a complicated series of tasks that the default Excel program doesn’t support.
When can or should you use VBA?
If you’re doing something in Excel that’s redundant, slow, and tedious, that requires multiple steps, VBA can automate that process.
VBA can enhance the capability of Excel; you can write code to expand the basic functionality of Excel beyond the standard defaults.
What are the Differences Between Macros and VBA?
Advanced Excel users may already use macros and wonder if it’s necessary to learn VBA if you can already record macros without coding language. The truth is VBA and macros are very different and learning VBA will improve your macros.
You can record keystrokes and tasks using the Record Macros feature in the Developer tab (Note: this tab is usually not shown by default and will need to be enabled using File>Options>Customize Ribbon and adding the Developer tab to the “Popular Commands” column).
Let’s say you do a weekly report for your company that summarizes new products in various product categories. You have 10 product categories, and each week you spend several hours formatting all of the ten tables in the same way. You can record a macro to automatically apply this formatting to tables, which will save a couple of hours out of your workday.
However, if you learn VBA, you’ll be able to extend automation even further:
- Edit individual records - With custom VBA code, you can edit individual records one-at-a-time. Unfortunately, when you record and implement a macro, changes are applied all at once.
- Update to accommodate changes - Let’s say you need to change how the table is formatted, or you need to add a new column. Instead of re-recording the macro, you can use your knowledge of VBA to edit part of the recorded macro manually.
- Write more efficient code - Using VBA allows you to write more efficient code snippets in the VBA editor, and exert more control over the details since you can manually edit single cells and records at a time with VBA code.
Learn How to Write VBA Code - Automate Tasks in Excel
NYIM’s VBA classes are hands-on and project based. Students work on a large project throughout the course by completing VBA tasks in a sequential pattern. Larger projects are broken down into smaller pieces to facilitate learning and retention.