Visual Basic for Applications, better known as VBA, is the programming language in Microsoft Excel. VBA provides developers with endless possibilities for running complex calculations and automating repetitive tasks. When used properly, VBA can create application-class spreadsheets that mimic the form and function of desktop software. Whether you’re an absolute beginner looking to write your first line of code, or an experienced developer looking for a reference, this guide provides a solid foundation to build and improve your skillset.
Did you know that VBA can make Excel interact with other programs, access your computer’s file system, or even play music? Some skilled developers have even used VBA to create video games. When used properly, VBA and Excel can be a reliable alternative to expensive 3rd party software. One of the great things about VBA is its accessibility. As of 2016, Microsoft estimates that there are 1.2 billion Microsoft Office users (Windows Central). This means that you can send your spreadsheets to almost anyone, and they will have the means to access them.
Once the developer tab is active in Excel, it only takes two clicks to open the VBA editor after a spreadsheet is opened. This permits you to start coding (and debugging) immediately. Additionally, code does not need to be compiled before running it, so it’s nearly effortless to get started on projects.
The following series is broken into 10 parts containing tutorials and reference materials. This series is meant to give readers who have never previously programmed the foundation to begin writing VBA scripts. Most tutorials contain a downloadable example file containing all procedures discussed. A recommended approach is to attempt to modify the procedures, or copy them into your own projects. By modifying procedures on your own, you will truly begin to understand how VBA works. If you follow this series from start to finish, by the end, you should have a strong foundation to begin coding in VBA independently.
Getting Started – General Overview
We’ve all encountered a spreadsheet with a “broken” macro. When the macro runs, a “Run-Time Error” pops us asking if we want to End the script or Debug. Most of us hit debug with the hopes of salvaging what we were trying to do, but a window opens with a mess of code and no explanation at all. This part of the series will help demystify the components of the VBA editor, preparing you to get comfortable viewing, and working in the editor.
The Range Object
The Range object is the primary VBA object for interacting with Excel. While many beginner VBA script writers are familiar with how to use the Range object to select, copy, and paste data, there are a number of other methods and properties that can be used to expand its functionality.
- The Range Object – Let VBA Talk to the Spreadsheet
- Assigning Scope to a Range Object
- Overview of Methods and Properties of the Range Object
- Speed Test: VBA Range Object
Variables in VBA
Variables are the gateway to accessing robust and meaningful functionality in VBA. By using variables, Excel users can step away from the constraints of the spreadsheet, become Excel developers, and write procedures that go beyond Excel’s core functionality.
Logic in VBA
Logic builds the foundation for creating dynamic and responsive programs. With logic, inputs provided by the user through the spreadsheet or dialog boxes can be tested against values to run various scripts. With this, the user (or the program itself) can respond to new information or visual cues.
- Using Conditional Statements in VBA
- Logical Operators for Conditional Statements in VBA
- Using Select Case Statements in VBA
Loops in VBA
Loops are control flow statements that allow VBA to repeat scripts while various conditions are met. While they may seem unnecessary at first, they are extremely valuable in spreadsheets. Loops are essential for everything from drop-down menus to complex data imports.
- Interact with Spreadsheets Using a VBA For Loop
- Cycling Through Collections Using a VBA For Each Loop
- Using a Do Loop in VBA to Iterate Through Spreadsheet Data
- Nested Loops in VBA – Looping Within Loops
Arrays in VBA
Arrays permit developers to store multiple values in a single variable-like structure. Through the use of arrays, developers can create and store dynamic lists used for displays or logical operations.
Other VBA Data Structures
While arrays are the most commonly known and used data structure for storing large amounts of data, a few lesser known data structures exist. The features of these structures vary based on ease of use, functionality, and limitations.
- Creating and Using Collections in VBA
- Creating and Using Constants in VBA
- Creating and Using Enumerations in VBA
- Organize Code with User-Defined Types in VBA
- Leveraging Dictionaries in VBA
Functions and Subroutines in VBA
Functions and subroutines provide an effective mechanism for storing blocks of code for future use. Instead of rewriting the same script multiple times for each use, a function or subroutine can be called at any point within a module to run the same operation. Variables can be passed through a function or subroutine through parameters, allowing for variation in behavior.
- Getting the Most Out of Subroutines in VBA
- Calling Subroutines with Worksheet and Workbook Events
- Building User Defined Functions in VBA
- Techniques to Organize a VBA Project
Classes in VBA
One of the more advanced topics in VBA, classes permit the creation of custom objects to store data and complete complex tasks. Classes make up the foundation of Object Oriented Programming (OOP) languages. While VBA is not a true OOP, it contains many features that make it behave like one. By using classes effectively, developers can streamline code and reuse classes across multiple VBA projects.
- Introduction to VBA’s Object Oriented Programming Features
- Building a Custom Class: Part 1
- Building a Custom Class: Part 2
- Building a Collection Class