Most Popular Posts of 2017

2017, this site’s first full year, is about to come to a close. In addition to wrapping up the Getting Started with VBA series early this fall, the Tips and Tricks with VBA Classes series is well under way and will conclude at the end of January. Below, you can …

Series Highlights and VBA Cheat Sheet

As we approach the close of the Getting Started with VBA series, it’s important to take a broad look at all topics discussed. This post will act as a reference for general syntax, concepts, and other topics.

VBA Productivity Tip: Naming Modules

This VBA Productivity Tip introduces a very simple feature that can be used to organize modules in complex projects — naming individual modules. By providing modules with specific and informative names, developers can easily group and compartmentalize VBA subroutines.

VBA Productivity Tip: Intellisense

This post introduces our first VBA Productivity Tip, a summary of the VBA Editor’s intellisense feature. Intellisense can help reduce the occurrence of common syntactical and semantic errors by providing useful tooltips and lists to help you code more efficiently.

Organize Code with User-Defined Types in VBA

VBA data structures such as arrays, collections, and enumerations can help organize large and complex sets of data. When drilling down to specifics within data, it can be somewhat difficult to work with these structures. VBA offers another data structure, User-Defined Types, which provide a more intuitive and user friendly …

Creating and Using Enumerations in VBA

Enumerations are data structures useful for grouping a elements which can be (or need to be) be associated with numeric values. Examples of useful enumerations are products with product IDs, or working with date values, such as months 1 through 12. Many developers also use enumerations to manage events, such …

Creating and Using Constants in VBA

A constant is a data structure used to store a specific static value that must be retained through the entire execution of a script. While constants may seem somewhat restrictive, they provide an advantage with memory usage and script readability.

Creating and Using Collections in VBA

Similar to arrays, Collections are data structures which can store many values. While each have their own benefits, Collections do not need to be manually resized and are very useful when working with dynamic lists and other data.

Creating and Using Multi-Dimensional Arrays in VBA

Building upon the one-dimensional array tutorial, multi-dimensional arrays provide an opportunity to easily create or transfer large tables of data. In many instances, thousands of data points can be created with a few simple lines of code. Note: multi-dimensional arrays can be inefficient and difficult to manage for complex sets …

Nested Loops – Looping Within Loops

While stand-alone loops will accomplish many tasks that require basic iteration, loops can be nested within other loops. In Excel, nested loops provide robust functionality to complete a number of tasks, such as filling out spreadsheet data, creating multidimensional arrays, or formatting cells.