Introduction to Custom VBA Classes and Objects

The ability to build classes and custom objects is one of the most valuable, yet underappreciated, features in VBA. While complex and fully functional VBA programs can be created without the use of this feature, utilizing custom objects will permit a VBA programmer to significantly scale-up a project, make it …

VBA Productivity Tip: Procedure List

Today’s VBA Productivity Tip introduces the editor window’s procedure list. This list serves two purposes: help find existing subroutines, or access built-in subroutines.

Getting the Most Out of Subroutines in VBA

Up until this point of the series, the Sub statement–short for subroutine–has been one of the most frequently used statements in tutorials and exercises. In their plain vanilla form, a subroutines serve one purpose — to contain procedural code. However, having a comprehensive understanding of how subroutines work and various …

Leveraging Dictionaries in VBA

Likely one of the most under-utilized data structures in VBA, Dictionaries include features not found in Collections or Arrays. With their ease of coding and readability, many developers rely heavily on this data structure.

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 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.

Using a Do Loop to Iterate Through Spreadsheet Data

Do Loops are an effective means of iterating through a set of data where the number of iterations is not known. Given the versatility and easy for adding and removing data on an Excel spreadsheet, a Do Loop is an extremely valuable tool when used effectively in VBA.

Cycling Through Collections Using a VBA For Each Loop

As a VBA developer, you will work with collections of objects and arrays of data. There will frequently be a need to either list all items within the group, or extract a specific element from the group. One of the most effective ways of doing this is with a For …